Custom Function: EasyDate

FileMaker Pro 11’s new OnObjectValidate script trigger provides us with the ability to control and massage data in ways we couldn’t before.  While this trigger is often thought of as a way of adding additional limitations and restrictions on data entry beyond the built-in validation options, you can also use it to ease and allow more flexible data entry.

Take, for example, date fields.  When you enter a value into a date field, FileMaker insists that is a full, valid date, with a month, day, and year (or day/month/year, depending on your locale).  Except… not always.  You can generally decide to skip the year: 12/9 results in (as I write this) 12/9/2010.  But you have to enter, at a minimum, a month and a day.

Inputing dates is even more flexible in Find mode.  Entering 12/2007 expands to 12/*/2007, finding all dates in December 2007.  If it’s a month of the current year you’re after, entering the simple number 10 results in10/*/2010.  And FileMaker is smart enough to assume that you’re not searching for dates in the year 9, so 12/9still gives you 12/9/2010.

Using the OnObjectValidate trigger, we can do the same thing in Browse mode, allowing users to be little more “loose” when entering dates.  Why would we do this?

I was recently meeting with one of our new consulting clients, discussing their old FileMaker database and how we could improve it.  One of the issues they had was that many of their “dates” were actually being stored in text fields.  Their old developer had done this because often they were just recording months and years like12/2007, not full dates like 12/9/2007; sometimes, even, they only had the year.  Since a date field requires a full valid date, the developer had relied on the more flexible text field.

This use of text fields, though, made it impossible for them to search properly (no looking for 10/2004…3/2005), or to sort in the right order (1/2009 comes before 2/2002), or to create accurate reports.  The solution, of course, is to use proper date fields.  Our client agreed that dates needed to be actual dates—if all they knew was April 2010, it would go in as 4/1/2010, and they only had a year, it would go in as January 1st of that year.

Converting to actual date fields would mean FileMaker’s validation rules would require a true, valid date.  While it wasn’t a huge burden for our client and her employees to enter that extra day (or month and day), why notmake if simple. OnObjectValidate, a simple four-line script, and the custom function below allow entry of dates as 4/2009 to automatically expand to 4/1/2009, or 4 to 4/1/2010.

The OnObjectValidate trigger runs a script with one Set Variable and then, if that variable is a valid date, uses the Set Field step without a specified target field, thus operating on the current field:

Set Variable [$date; Value:EasyDate ( Get ( ActiveFieldContents ) ; 0 )]

If [IsValid ( $date )]

  Set Field [$date]

End If

The custom function EasyDate has two parameters: the date that is to be expanded (in this case, the active field contents), and an additional flag that, when set to true, uses the last day of the month (or year) instead of the first.  The function mimics pretty accurately the shortcuts you are allowed to enter in Find mode: you can enter a full date (with or without year), a month (assumed to be this year), a single year, or a month/year combination.

EasyDate ( dateInput ; lastOf ) =

Let (

[

   input = GetAsText ( dateInput ) ;  //get the input as text

   sep = Filter ( input ; KanjiNumeral ( input ) ) ;  //get non-numeric character(s) from input

   inputWords = Substitute ( input ; sep ; " " ) ;

   m = LeftWords ( inputWords ; 1 ) ;

   y = RightWords ( inputWords ; 1 ) ;

   thisyear = Year ( Get ( CurrentDate ) )

] ;