FileMaker’s internal SQL and System Formats

/1 Comment/in , , /by

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.