Understanding FileMaker System Formats and Resetting the File Locale

When you create FileMaker files, FileMaker Pro uses your computer’s system formats to determine how dates, times and numbers display and sort. If you open or share a FileMaker file created with different system formats, you can use your computer’s system formats or match the formats used when the file was created

I was recently working on a FileMaker solution (originally created by our customer) and started to notice some strange results when working with dates and numbers. I’m based in Australia so I expect to see the following regional settings for dates, numbers and currency as shown in the Language & Region System Preferences on my Mac:

I was in the process of manipulating a date to change the format used when making an API request – here’s how the date field looks on the FileMaker layout:

I like working with FileMaker lists and often substitute out the date separator character for a FileMaker carriage return ¶ using this syntax:

which didn’t give me the expected result. That was my first clue that there was something different with the date formats for the file I was working with. Shortly after I needed to import some geocodes that were to be used for mapping a customer’s address against a defined delivery zone (see earlier article on this here). Here’s a screenshot of the geocodes in Excel that I was importing into FileMaker:

and here’s how they appeared after being imported info FileMaker:

You can see that the decimal separator has changed from a full stop/period to a comma – once again I knew there was definitely something different with the number formats for this file. This was confirmed again shortly afterwards when I was adding in a test to ensure that the user was using FileMaker Pro 19.1.3 or later due to some changes in how the FileMaker.PerformScript() function operates. GetAsNumber ( Get (ApplicationVersion) ) was returning this:

whereas I was expecting this format:

Viewing the File Locale Settings

If you’ve been developing with the FileMaker Platform for a while you might recall seeing this dialog box when opening a file that was sourced from outside of your normal region (e.g. an Australian developer being sent a file from a developer in North America):

This was FileMaker Pro alerting you to the difference in the settings between the file (e.g. US regional settings) and your local macOS/Windows system settings (e.g. Australian). You could (and still can) manually toggle the use of System formats via the Format menu:

as well as using the File Options>Text tab settings for each FileMaker file:

The default for this setting was changed to “Always use current system settings” with the release of the FileMaker Pro 8.0v2 update back in December 2005 – previous it was set to “Ask whenever settings are different”. This was a welcome change and solved most of the issues as far as displaying and entering dates, times and numbers. Developers could also use the Set Use System Formats script step in their startup script to enable this option.

However you can still encounter issues when the file locale settings are different to the system locale settings as described above, most often when importing or exporting data and not having the correct source format set etc.

Claris have recently provided a programatic way to determine the locale settings for a file so you can be more proactive about identifying if there is a difference between the file’s locale settings and your macOS/Windows system settings. The recently released FileMaker Pro 19.1.2 Update included 2 new Get functions which return information about the operating system’s locale and the current file’s locale:

Get(SystemLocaleElements) – returns a JSON object with information about the client system’s locale.

Get(FileLocaleElements) – returns a JSON object with information about the current file’s locale.

Using these 2 functions you can now compare the system locale settings with the current file locale settings to see if they are different. You can see the JSON object that is returned by using the Data Viewer to add these under the Watch tab. You can also use the JSONGetElement function to target particular JSON keys, including the Misc.Active key which indicates whether the active locale for the current user is the system’s locale (true) or the file’s locale (false):

JSONGetElement ( Get(SystemLocaleElements) ; "Misc.Active" )

If you want to compare the system locale with the file locale to see if they are the same you can use the following test:

JSONGetElement ( Get(SystemLocaleElements) ; "LocaleID.IDNum" ) = JSONGetElement ( Get(FileLocaleElements) ; "LocaleID.IDNum" )

Here’s some other examples that you might find useful:

JSONGetElement ( Get(SystemLocaleElements) ; "LocaleID.Name" ) returns the name of the system locale.

JSONGetElement ( Get(SystemLocaleElements) ; "Date.Sep" ) returns the Date separator character for the system locale.

JSONGetElement ( Get(SystemLocaleElements) ; "Num.Decimal" ) returns the decimal separator character for the system locale.

JSONGetElement ( Get(FileLocaleElements) ; "LocaleID.Name" ) returns the name of the current file locale.

JSONGetElement ( Get(FileLocaleElements) ; "Date.Sep" ) returns the Date separator character for the current file locale.

JSONGetElement ( Get(FileLocaleElements) ; "Num.Decimal" ) returns the decimal separator character for current file system locale.

Using these new functions I was able to determine the exact locale settings for the file I was working on. In this case these revealed that the file had a locale of Germany, the Date separator was the . character and the Number decimal separator was the , character. This explained why I was having issues with importing dates and numbers and evaluating the GetAsNumber ( Get (ApplicationVersion) ) function.

Changing the File Locale Settings

Version 19.5.1 of the Claris FileMaker Data Migration Tool now lets you specify a new locale for a target file. Use the syntax “-target_locale {locale}” to change the locale of the resulting file. See the Claris FileMaker Data Migration Tool Guide for for supported locales and additional details. You can download a copy of the Claris FileMaker Data Migration Tool here.

If you don’t want to use the Claris FileMaker Data Migration Tool you can perform the following actions to reset the file locale settings:

1. save a clone of the file you wish to reset. A cloned copy of a FileMaker Pro file contains all the contents of the original file with the exception of the record data and the default locale information. 
2. open the clone on a system with the required regional formats you wish to use. When FileMaker Pro or FileMaker Pro Advanced opens a clone, the operating system’s current locale information is added to the clone.
3. import your data from the original file to the newly cloned file one table at a time.

We would definitely recommend using the Claris FileMaker Data Migration Tool here as it can migrate data from all tables at once and also reset the locale for the target file.