FileMaker’s internal SQL and System Formats
I’ve been using the ability to execute SQL statements via a FileMaker Pro plug-in more and more lately (see An Approach to FileMaker Server-Side Script Debugging for an example of this) and recently encountered one issue that might arise when working with files created with different system formats to those on your computer. It’s important to note that FileMaker Pro uses your computer’s system formats to determine how dates, times, and numbers display and sort when you first created a new file.
I was working with a file that I didn’t even realise was created with different system formats as the developer of the file had set the File Options to “Always use current system settings” which tells FileMaker to use the current system settings for data entry of numbers, dates, and times instead of those saved with the file when it was first created or cloned:
The startup/OnOpen script for the file was also using the Set Use System Formats script step and setting this to [On] which also instructs FileMaker Pro to use the current system formats. It wasn’t until I started using the BaseElements plug-in to enter the current timestamp along with some additional text into a text field that I noticed something was amiss. Instead of inserting a timestamp for my system settings (Australian) which would appear as:
9/11/2011 3:49:07 PM
I was getting this instead:
09.11.2011 15:49:20 Uhr
I did some tests and whenever I evaluated the Get ( CurrentTimeStamp ) function I would get a correctly formatted timestamp for my region (e.g 21/11/2011 3:51:20 PM ) but as soon as I used this in an SQL statement or converted it to a text data type I would lose the system settings and get the original settings that were saved with the file when it was first created (German in this case). This appears to happen anytime you need to convert dates, times etc into a text data type via the calculation engine (not specifically related to using a plug-in), which is what I’m doing with the internal SQL feature and the BaseElements plug-in. For example my plug-in calculation looked liked this:
BE_FileMakerSQL ("
INSERT INTO ServerLog(Description)
VALUES('Logging Commenced at " & Get ( CurrentTimeStamp ) & "'" & ")")
The only solution that I’m aware of is to create a clone of the file and then open it on a computer with the required system formats. It will then use the local system formats and you will get the same results when converting date and time values to a string.
I prefer to use my own custom function for any date/time conversions to/from text. The reason is that I have 100% control over the conversion result.
I used to use the built-in conversion until I discovered that sometimes, for example when getting result from a plug-in as text, conversion to date does not have to be consistent with the formats stored in the file when it was created.
As an alternative, which we actually used in FM Bench, you can store the timestamp as number. Conversions between number and timestamp do not depend on the system formats (unless you use fractional timestamps).