Dear FileMaker Hacks,
My personnel database has a scripted routine that locates all employees with birthdays in the current month, like so:
When I sent a copy of the database to our sister organization in Australia, the scripted routine did not function properly for them. Could it have something to do with their computer expecting D/M/Y rather than M/D/Y?
— Perplexed in Peoria
You’re quite correct. Your script is searching the date_of_birth field for…
Month ( Get ( CurrentDate ) ) & “/*/*”
…which during September translates to…
and works perfectly in the US since we use M/D/Y…
…but not in places like Australia where dates are formatted as D/M/Y. When they open the database, and run the routine in Australia, rather than finding all the records where the birth month is 9, they find all records where the day is 9, regardless of month.
Here’s a neat trick posted by “The Shadow” on FM Forums a while back that will handily solve your dilemma: Script your search to use the Japanese style date format, Y+M+D, and be sure to note that the separator is a + instead of a /.
Assuming you’re in find mode, when your Set Field step pushes…
“*+” & Month ( Get ( CurrentDate ) ) & “+*”
into date_of_birth, the query will immediately and automatically be converted into the appropriate local date format, so will work in both the US and Australia.