It’s About Date & Time!
Example file: DatelTime
First I’d like to point out a few curious facts about our old friends Date and Time.
We take a great deal for granted when we talk about them, they are so much a part of our everyday lives. There are few things to take into consideration when using them in FileMaker, however.
It is really very good about understanding what we mean when we type in a date or time. If we type “5/2,” it supposes that we must mean May 2, 1999 (US date format). You can type just “1” in a time field and it will put 1:00 am.
Yes, I know–Y2K–you should really type in four-digit dates. In a simple calendar it’s overkill; other places it may be a good idea. Go get the latest free updater from http://www.filemaker.com for your version, if you haven’t already done so. It fixes all the remaining little Y2K glitches. It allows you to demand full four-digit validation of date entry, if you need that security.
Dates are Numbers
If you want to use dates more extensively in calculations and scripts, there is more that you should know.
Basically, after checking your typing to see if it is a date, it records that date as a number. That’s what it uses from then on in its calculations.
It’s the number of days from 1/1/0001. You can see this for yourself by creating a mirror of a date field. Make it a calculation field, equal to the date field, with a number result.
Or, vice versa, create a regular number field. Then create a calculation field, equal to the number field, but with a date result.
1 = 1/1/0001 either way.
If you want to alleviate some of your fears about Y2K, type 12/31/3000 into the date field. No problem; it’s 1095727. The funny thing is, it has told us many times. Every time you (and we all do) type an invalid date into a date field, we get a no-no dialog box that says, “The date in this field must be a valid date in the range of years 1 to 3000 and should look like “12/25/1997.” But we were in no mood to read it then. If you try to go further, it doesn’t crash and the world doesn’t come to an end, you just get a question mark in the field.
Time is a Number, Too
Time is just the number of seconds past midnight, when the day begins. It actually begins at 0, and goes to 83,399 (84,000 in all).
Divide by 60 to get minutes, and by 3600 to get hours (you knew that).
It starts over every day (duh).
The fact that Date and Time are internally numbers (integers) means that you can use them directly in calculations where you would use numbers, add, subtract, <>. It’s also why there is no DateToNum or TimeToNum function. That would be redundant.
Date Find Confusion
Confusion arises, however, when you try to use them in Finds. Everyone has problems with this, at least for a while.
It’s partly because of the clever way FileMaker can read and format dates for us. When you type a date, you are not typing numbers, you are typing text. “11/29/1999” is text. FileMaker “parses” and validates it to see if it is a date, then records it as the number of days since 1/1/0001.
This mechanism is bypassed by certain script steps, the foremost being Set Field. It just sets the field. It’s fast, but it requires that you, the scripter, be responsible for only passing it the correct type of data for the field.
This is OK much of the time, but what about if you want to find a range between two dates? You can type an ellipse (…) between two dates, typed into a date field when you’re in Find Mode, to do this. But you can’t set it into the field during a Find script. It’s not a date.
Furthermore, in order to allow this non-date value in Find Mode, it’s really necessary to input it as text. The solution is to include all this information in your scripted Find, using a script step that simulates typing text. This would be one of the Paste steps. In this case we’d need Paste Result (sounds like Paste, but doesn’t use the clipboard).
Date Find Script
I like to use two global fields, option-type date, for the beginning and ending dates, set next to each other on a layout with “to” or “-” between them.
Then I put a button nearby that says “Find Now,” or something similar. You’re still in Browse Mode until you hit the button, then it does the Find in one shot, so you don’t need Pause steps.
The one hangup with this method is how to let people just find one date. One solution is check for that in the calculation, so it doesn’t matter which field they use or which is empty. Here’s the basic Date Find:
Enter Find Mode []
Paste Result [“Date”,
“Case(not IsEmpty(DateBeging) and not IsEmpty(DateEndg),
DateToText(DateBeging) & “…” & DateToText(DateEndg),
Case(not IsEmpty(DateBeging) and IsEmpty(DateEndg), DateToText(DateBeging),
Case(IsEmpty(DateBeging) and not IsEmpty(DateEndg), DateToText(DateEndg), “”)))
Perform Find []
Date Find as Number
As you would imagine from earlier, Date is a number, and can be used in a scripted Find as a number. The calculation is a little different, and can use math operators or the ellipse, whichever you want.
It does require you to create a separate field “Date cn”, calculation, =Date, number result. But it frees you from all the DateToText functions.
Here’s just the Paste Result step:
Paste Result [“Date cn”,
Case(not IsEmpty(DateBeging) and not IsEmpty(DateEndg),
“„” & DateBeging & “¾” & DateEndg,
Case(not IsEmpty(DateBeging) and IsEmpty(DateEndg), DateBeging,
Case(IsEmpty(DateBeging) and not IsEmpty(DateEndg), DateEndg, “”)))
Notice that the operators (“„”) still need to be inside quote marks (they’re text). The extra carriage returns are just to make it easier to see the arguments.
Date Range Plug-in
Some situations require frequent Finds for relatively narrow date ranges. Scheduling solutions need very fast finds, to check for conflicting dates. In cases like this I highly recommend a free plug-in by FileMaker’s own David McKee, “Ranges.”
Its external calculation takes two global dates (or any two numbers), and quickly generates all the numbers between them. The result is a global field with the date range, each separated by carriage returns.
This can be related to the Date field to very quickly find out if there is a match, using the under-documented ability to use a multi-line key field like a join file. It’s faster than a Find, and doesn’t disturb the present found set.
It doesn’t work so great with Time, however, as there’s just too damn many seconds in a day. You’ll quickly hit the ‰32,000 field character limit.
Other fields can also be “concatenated” to the number fields, to “filter” the relationship further. Altogether a great tool, and unbelievable at the price. There’s not a lot of documentation, but there’s a demo file included that shows how to use it.
One problem: if you neglect to put an “end” on the range, on either end, it will happily continue to calculate until it hits the 32,000 character limit, tying up the computer for the time.
Get it, and several other of David’s free plug-ins at http://protolight.home.ml.org
Date + Time
This is actually where I started this article; the preceding was just supposed to be “background.” And there’s tons more beyond all this in the example file. Well, you’ve got a month.
I was using Date & Time to create a unique key for a modification tracking file. I found an interesting phenomenon.
If you try to use Date + Time, a calculation with a number result, you’ll end up with a decidedly non-unique number.
If you add the Date + Time for 12:00:02 today, and the same for 12:00:01 tomorrow–they are the same! And there will be many other matches.
Date & Time
Concatenating the two values, however, is a whole different story. FileMaker will do that, sticking them together, not adding them, but still ending up with a number.
Date is generally going to be 6 digits, (at least from 10/16/0274 until 11/27/2738). It will increment by one each day. Time will be anything from 1 to 5 digits tacked onto to that, changing rapidly all day long.
It’s a weird number, difficult to get a handle on. But, as far as I can tell, it is a reasonably unique number, using the easily obtainable values Status(CurrentDate) & Status(CurrentTime).
Date & Time as Key
The reason that is important, at least to me, is that it can be used to create a key to another Log file (“Logger”) to reliably generate a new record whenever data is written to any field in the file via that relationship.
The matching key in the Log file must be a regular number field, not a calculation field. The second, or target key must always be enterable to allow creation of related records. You don’t have to do anything with it; FileMaker will fill it in when you set a value into any of the related fields.
If the concatenated Date&Time is set into a global field in the first file, the timing of record creation can be controlled by scripts in that file. This is handy if you want to write more than once to the same record. A new record will only be created if you reset the key, to the new current(date) & current(time). Otherwise it will continue to write to the same record, as the keys will still match.
A human-readable time stamp in the Log file can be recreated from the key by breaking the key back down into its component parts. This is easy because, as we saw earlier, the date is the first 6 digits, and the time is the rest. Remember, in the Log file, Date|Time is just a number field.
Date = Left (Date|Time, 6) <– Choose Result is Date
Time = Right (Date|Time, Length (Date|Time) – 6) <–Result is Time
Just format them appropriately on the layout, and they’ll look fine, just like the real thing.
You can alternatively just use the Modification Date and Modification Time, by creating auto-entry fields for those. It’s about the same either way.
This whole area of recording field modifications can get pretty complex, as there are several variations of how it can be done, what will be stored, and why you’d want to do it.
I’ve included 4 fairly simple variations in the example file. Needless to say, it would take too long to explain each here. There is some documentation in that file.
There are also a couple of techniques for showing or hiding global buttons, one using a calculation, the other using the mysterious invisible portal. Check it out.
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.