The problem
Have you ever noticed that specifying a date for a computer is a rather difficult matter? How often, do you think, people have problems typing "12/5/2001" or something like? I'm a rather experienced user but a need to enter several dates always brings me to my knees.
Let's say I'm thinking about an appointment and want to enter it into my diary. The appointment is going to be on this Friday. I know that if I typed "Friday", the computer wouldn't understand; have I told you that I'm experienced? OK, I'll calculate what date is the Friday. This isn't a big mental effort, sure.
Today is Tuesday, 14th so the Friday is 17th. I type it in the date field and...
Of course, I forget the month number. Now I have to make another mental effort and to remember what month is now and what number it is. Then I must carefully type that "code" in the field. It looks so arbitrary comparing to what I had in mind that I often mistype and is punished again:
I've typed "17/3" instead of "3/17". By the way, it won't release you until you either empty the field or enter a correct date. But stop! What is incorrect in "17"? None of the people I know ever has problems understanding what I mean saying "17" or even "Friday" or "yesterday". This is for human; but computer takes it differently. It doesn't know that people have invented convenient names for the current day and the next or previous one. It doesn't understand what is "Friday". If I enter "15" it asks me which month do I mean. Why it's so extremely difficult to guess, especially if today is 14th?
The truth is that a typical date entry routine makes wrong assumptions of what the entry may be. If I'm entering an appointment, which dates are probable? January 12, 567? October 31, 2514? Definitely not. Of course, if I work with a historical database the possible range of dates is much wider, but a seasoned developer is almost always able to determine what she is designing at a given moment.
And in most cases it is an appointment system, a schedule, an order processing or time tracking widget and all these applications typically work with dates very close to the today's date. Here today and tomorrow are champions, yesterday takes the next place, the current week is miles ahead of the current month and the latter leaves the rest way behind.
So let's write down some maxims:
- It's almost always possible to forecast which dates will be entered in this particular field.
- In most cases these dates will be very close to the today's date.
Fortunately I'm a developer and can build my own date entering solution that would take these thoughts into account.
The solution
The solution is a multi-field calculation, which I call Smart Date Field. The sample file that comes with the manual understands the following entries:
- Any date in a way FileMaker understands it.
- A day of a month, such as 9 or 25. By default it returns the nearest such a day, but it can also be configured to "look" only in the past or in the future.
- A year, such as 1998 or 2005.
- Yesterday, today and tomorrow; names of months and days of weeks; words "week", "quarter", "month" and "year" combined with words "last", "this" and "next". Each such an entry can have unlimited numbers of synonyms, other language translations and abbreviations.
The rest of the article explains the inner mechanics of the system and ways to use it in your solution.
How it works?
The principle is simple. User enters "dates" in a text field that won't ever give any warnings unless you explicitly set its validation options. Another field analyzes what's entered and calculates the date. For the sake of simplicity the second field in the sample file is splitted into two parts: one analyzes the entry and produces its code and another uses the code to calculate a date.
Understanding what's entered
Though analyzing the entry looks difficult, in fact there are only a few possible choices, that are checked one by one using a Case() statement:
- nothing;
- a date FileMaker considers valid;
- a one or two-digit number from 1 to 31
- a four-digit number from 1 to 3000;
- a special word, such as "Sunday", "January", etc;
- none of the above (an error).
There's nothing special about any of the cases, except the case of "special word". Hey, there seems to be a lot of them! How does the thing recognize the words and their meaning?
Let's count how many is "a lot", that is how many possible combinations of words "last", "this", "next", names of days, months and words "week", "month", "quarter" and "year" are there? I have count 88; 91 with "yesterday", "today" and "tomorrow". This isn't a big number, is it? If we write each on a new line it will take about two pages.
So we don't need a full-featured language parser: we're going to make a list of all possible entries and use it as a dictionary.
Such a dictionary makes it as easy as pie. If the input doesn't match the first four cases, the calculation tries to find it in the dictionary. If it's found, the calculation returns it's "definition"; if not, it goes to the sixth case, "an error". The process is same for all the possible words and phrases; only the result differs. So this highly compact calculation can understand almost any number of words and phrases. For example, a full-featured dictionary of the sample file contains just about 4,000 characters, including comments; this is not even close to its full capacity of 64,000 characters.
Now it's time to look at the dictionary. Here's the beginning of that one of the sample file:
1 yesterday: "yesterday", "y"
2 today: "today", "t"
3 tomorrow: "tomorrow", "m"
4 nearest Sunday: "Sunday", "Su", "Sun"
5 nearest Monday: "Monday", "Mo", "Mon"
...
The words and phrases the system understands are in quotes; all the rest serves as comments to help you navigate and modify the dictionary.
But where are the definitions? Since they are used only by calculations, they are numbers, numbers of the lines. The calculation finds the word and counts the number of lines (carriage returns) before it; it's very simple and universal. Of course, changing the order of the lines in the dictionary will ruin all the system, so be careful with it.
The principle also makes it easy to add more synonyms, abbreviations and languages to the dictionary. For instance, the only thing you had to do to have the field understand the name of Sunday in German and French would be adding the words from these languages to the 4th line:
...
4 nearest Sunday: "Sunday", "Su", "Sun", "Sonntag", "dimanche"
...
You can also remove some entries: if a line doesn't have an entry, it is disabled, because there's no input that can activate it and the corresponding calculation. The only requirement is that all the words and phrases must be different or only the first one will work.
Calculating the matching date
The field that analyzes the entry (gEntryType) evaluates to a numerical code of it:
| -1 | nothing; |
| 1 | a date; |
| 2 | a number from 1 to 31; |
| 3 | a number from 1 to 3000; |
| 4...94 | a word or phrase from the dictionary (the result is the number of the line plus 3); |
| 0 | none of the above (an error). |
The code is used by the field that actually calculates a date. Basically the field contains one big Choose() function
Choose(gEntryType+1,
96 calculations for each possible case
)
This calculation is rather big to be put here, so please use the sample file. It guarantees there won't be at least syntax errors and can automatically convert the list separators.
By the way, as soon as you open "Define fields" dialog, you'll see that there are more than just one calculation: there are 12. Why so many?
Variations
In fact all the calculations are almost the same and you won't need at least half of them.
First, some "magic words", or let's call them "smart dates", mean more than a single day: "1998", "January" or "last week" are ranges of dates. A range is defined by its start and end date, so we need two date calculations to get both. (If you're interested in working with ranges of dates and numbers in FileMaker, you can find a good piece of information here.)
Second, some "smart dates" define the result clearly, while others are not so precise. For example, there's only one "this week's Sunday" or "1998"; but what can you say about "Sunday" or "17"? Unless today is Sunday or "17", there are two possible answers: one in the past and another in the future. Which one to get? It depends more on the context the field is used. Sometimes it implies only future or past dates are valid, sometimes it doesn't matter, so the proper responce would be to return the nearest one.
So the two fields we were talking about get three variations each: two of them prefers either the future or the past while the third gives the nearest match.
And the final variations pays attention to the week start day, which can be either Sunday or Monday. Since it's hard to imagine these two types mixed up in one solution, there are two sets of the six fields in the sample file: just leave out the one you don't need.
Here's the full list of the fields:
| | Past | Nearest | Future |
| Sunday 1st | start |
gDatePastStartSu |
gDateNearestStartSu |
gDateFutureStartSu |
| end |
gDatePastEndSu |
gDateNearestEndSu |
gDateFutureEndSu |
| Monday 1st | start |
gDatePastStartMo |
gDateNearestStartMo |
gDateFutureStartMo |
| end |
gDatePastEndMo |
gDateNearestEndMo |
gDateFutureEndMo |
How to use it
If you are going to add smart date fields to your solution, here are some useful tips.
Preparing the dictionary
The default configuration contains typical abbreviations and shortcuts for days of week and months but who knows? They might be of no use in your solution. Shortcuts are good for most frequent entries; but only you can analyze your users' tasks and decide which entries are most frequent. May be your users will be fond of abbreviations like
last month: "-m"
this month: "m"
next month: "+m"
The only thing you should keep in mind that if a user doesn't use a shortcut several times a day, she won't remember it. So in most cases you won't need shortcuts for things that run monthly or quarterly.
Merging intermediate fields
The sample has an intermediate field: gEntryType, because it simplifies the explanation and makes the solution more "modular": if you wanted to make some changes in the field, you would not have to modify all the 12 date fields.
But as soon as you are happy with gEntryType you can safely embed it into the date calculations. There's only one reference to it:
Choose(gEntryType+1,
...
)
so it's not difficult. Such a merged calculation is especially useful if you're going to use the principle in scripts.
Using the principle in scripts
If your application has a script that gets users' input into global fields and then does something with it, note that using the Smart Date Field principle in scripts even easier than in fields. You don't need any intermediate fields: only a text field to get the input and, perhaps, a date field to store the output. All the conversion could be done within a single step:
SetField["gOutputDate", "a merged formula (see above)"]
If you accept a date in a global field in several places, a good idea would be to turn the script into a subroutine: just add two global fields to keep the parameter and the result (say, text gSmartDateEntry and date gSmartDateResult) and two more steps to the script:
SetField["gSmartDateResult", "TextToDate("")"]
SetField["gSmartDateResult", "a merged formula that uses gSmartDateEntry"]
SetField["gSmartDateEntry", """"]
Installing the fields
If gEntryType is merged into date calculations, each smart field will require one entry field and either one or, if you need ranges, two date fields. You just need to change field references in the formulas; the fastest way to do it is to use a text editor with search and replace function.
Telling users the fields are smart
Smart fields are not quite common around here, so nobody expects to meet one. It's absolutely necessary to explicitly tell this to users. A good tease would be to put a part of an instruction near the field and make a hyperlink to a help screen, that explains the rest. Another idea is to make a pop-up list with text entries the field understands and, again, a link to help screen.
Stored smart date fields
Smart date fields are especially convenient when they are unstored, that is when they are used in advanced portal filters or scripts. Using smart date fields for typical date entry is harder and limited.
The problem of global gDict that makes all the rest unstored is easy to solve; you can keep the dictionary in a value list, use the "live lookup" technique or even embed it as a text constant into the calculation (there are only two references to it in gEntryType). But all the methods have one big disadvantage: there's a chance that some actions, such as changing a calculation definition, will reset all the existing dates. Nobody wants all the dates of a whole year that were conveniently entered as "today" to be changed to the date of the unlucky day an error in a formula were spotted. The "live lookup" technique partially solves this problem, but it cannot prevent the dates from being "updated" when they are imported and so on.
The only fully reliable method would be to use a script that automatically runs when the entry field (Entry) is changed, calculates the date, saves it in the plain date field (say, theDate) and updates Entry as a final touch:
SetField["Entry", "DateToText(theDate)"]
But running a script automatically when a field is changed requires a plug-in (or Web-interface), so the method is not universal.
If you consider this article useful
If you like the explained techniques and think the article has improved your FileMaker skills, please support the author with $10.00 and get a bonus: the master password for the sample file. The date calculations in the file are carefully tested and will save a lot of your time.
It's a long article, so if you'd like to have it printed get 
the PDF version with FileMaker samples ( 114k).
