How do I create a data entry filter for a time field so that a user can enter "0845" and the field will automatically format it as "08:45"?
IF you are using FileMaker 11 or newer, set up the OnObjectValidate trigger that performs a script. Have the script check for the lack of a colon and insert it for the user before the data is validated.
The same script can detect and remove any entered seconds or you can set up an auto-entered calculation that removes any seconds value.
Use an Auto-enter calculation as simple as this: ( just in case ALWAYS use a 4 number entry) Left ( Self; 2 ) & ":" & Right( Self; 2 ) Use the format hhmm to display the entry without seconds.
The auto-enter method sounds nice, but when it's a time field, the lack of a colon will trigger an error dialog before the auto-enter calculation can correct it to be a valid entry.
Managed to work this out myself -- not easily, mind you -- but my solution actually applies elements of both of your solutions. The OnObjectValidate trigger calls the following script:
Insert Calculated Result(Select; Left ( Parameters::Time ; 2)&":"& Right (Parameters::Time;2)
Insert Calculated Result(Select; Left ( Parameters::Time ; 1)&":"& Right (Parameters::Time;2)
In other words, hours can be either one or two digits, but minutes must always be two digits. Whether the user enters "940" or "0940", the script inserts the colon in the proper place. And no seconds value. As I said, it works. So far. Any suggests for improvement, however, will be gratefully accepted.
…and if the user enters 9:48 including the colon but without the leading zero?
Maybe you should Filter(Parameters::Time ; "0123456789") first using the Let() function or a $variable.
Insert Calculated Result [Select: Let( t = RIght( "0000" & Filter( Parameters::Time ; "0123456789") ; 4) ; Left( t ; 2 ) & ":" & Right( t ; 2 ) ) ]
P.S. By padding the user's entry with 4 leading zeros, you eliminate the need for the If/then/else statement. However, a blank entry will be converted to midnight (00:00) which may not meet your needs.
Good point. I'll add a filter for the colon. Thanks!