Entering Time into FileMaker
There's got to be a better way!
Yep, FileMaker is very picky when it comes to entering time. The program requires that you enter hours, a colon, and minutes as a minimum. And, if you want to enter four o’clock in the afternoon, you had better either add ‘PM’ to the end of your time entry, or use military time, ’16:00’.
It may seem like a trivial thing, but it’s something that more than one user has brought to my attention over the years. I knew there had to be a way to make entering time easier, and more forgiving for the user. First I considered what rules I’d like to apply when it comes to entering time. Here they are:
1) Forget the colon. If I want to enter the time of 4:20 (yes, I said it), then all I want to type in is 420.
2) Accept even hours. If I simply enter 3, or 11, the field should accept it as 3:00 or 11:00.
3) Naturally know AM or PM unless I say otherwise. I’m making an assumption here which may not work in every case, but most time entries tend to follow ‘normal’ working hours. The field should assume that when I enter 420, I mean 4:20 in the afternoon, it will automatically append the ‘PM’ on its own. If I meant 4:20 in the morning (which is kind of ridiculous), then I would type 420a. Oh yeah, I should only have to type an ‘a’ for ‘AM or ‘p’ for ‘PM’. I needs to be smart enough to figure out what I mean.
To accomplish this goal, I’m turning to a simple script which will need to be executed, and our input data ‘normalized’, before the data is attempted to be validated by the database. If we don’t modify our input beforehand, FileMaker will invalidate our input, and give us the nasty message we’re trying to avoid. So we will turn to a script trigger, the OnObjectValidate trigger attached to our field should do the trick.
Next, the script -- The script is a super simple 2 step script:
1) Set Variable
2) Insert Calculated Result
Breakdown of Step one:
( It only looks huge because of all the comments )
Set Variable [ $time; Value:
Let ( [
// --- Grab the field contents, and filter out everything but numbers and the letters a and p ---
~IN = Filter ( Get ( ActiveFieldContents ) ; "apAP0123456789" ) ;
// --- From the filtered input, extract just the numbers ---
~NM = Left ( Abs ( ~IN ) ; 4 ) //-- If you need to be more precise, you can change the left function to six characters
// --- Of the filtered numbers, if the length is just 1 or 2 characters, the user entered a whole hour, so append the minutes ---
~NM = If ( Length (~NM) < 3 ; ~NM & "00" ; ~NM ) ;
// --- From the filtered input, just grab the left-most single character which will be either an ‘a’ or ‘p’ ---
~PS = Left ( Filter ( ~IN ; "apAP" ) ; 1 ) ;
// --- Find the hours. If the length of our numbers (~NM) is 4, pull the left 2 characters, else just 1 ---
~HR= GetAsNumber ( Left ( ~NM ; If ( Length ( ~NM ) = 4 ; 2 ; 1 ))) ;
// --- extract the minutes from our numbers ----
~MN = GetAsNumber ( Middle ( ~NM ; Length ( ~HR ) + 1 ; 2 ) ) ;
// --- make sure the minutes are two characters long ----
~MN = Case ( Length ( ~MN ) = 0 ; "00" ; Length ( ~MN ) = 1 ; "0" & ~MN ; ~MN )
] ; //--- end of the Let variables ---
// --- Set the time ---
~HR & ":" & ~MN &
// --- Append AM or PM ---
~PS = "a" ; "AM" ;
~PS = "p" ; "PM" ;
~HR ≥ 7 and ~HR ≤ 11 ; "AM" ;
) // end Let
Insert Calculated Result [ $time ] [ Select ]
Take the ‘normalized’ time contained our variable from step one, and insert it into the field, which FileMaker will then correctly validate.
And, that my friends, is it! We now have a script that we can use on any time field. It’s completely portable, so you can easily copy it to any FileMaker database.
Download the sample file