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
Dear Perplexed,
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.
Internationally,
FileMaker Hacks
That sir, is awesome :)
Thanks Daniel… wait till you see part 2 :-)
Very useful tip for international oganizations~
Another simple way around this issue is to create a calculated field cBirthdayMonth = Month(date_of_birth). Then just search in that field for Month(Get(CurrentDate)). This also works independent of location or date preferences. The field would also be useful to print a report of all the birthdays in a particular month where you want to sort by the month number order and not the name of the month.
Thanks Tim. I agree that yours is a wonderfully simple solution, and it can be extended to accomodate the challenge in part 2, by defining the field as Month(date_of_birth) & “.” & Day ( date_of_birth ). But I have an aversion to “polluting” my tables with helper fields unless absolutely necessary.
Tim: create a calculated field cBirthdayMonth = Month(date_of_birth). Then just search in that field
Kevin: yours is a wonderfully simple solution … but I have an aversion to “polluting” my tables with helper fields unless absolutely necessary.
Come off it Kevin! You are recommending that we all start jumping through hoops to avoid “pollution”. What a nonsense. The only reason this problem will occur is because you have an application which has to work in a multi-national context. Isn’t that a good enough reason to add a calc field to a solution?
I’m with Tim, a calc field is a simple, effective solution to the problem. It immediately resolves a difficult issue and provides advanced functionality.
Malcolm you are certainly entitled to your opinion. I don’t happen to share it, but that’s one of the nice things about FileMaker… there are almost always multiple ways to get the job done.
I might add that I support a separation model solution with 40 separate installations; and go to great lengths to solve problems at the UI level.
You are recommending that we all start jumping through hoops to avoid “pollution”. What a nonsense.
Not at all. I’m sharing an outside-the-box method to accomplish a goal. If it doesn’t work for you, do it your way. You’ll note that I said “I have an aversion”, not that “everyone should have an aversion”.
Malcolm – can’t really see why you’re going all “What a nonsense”?
Here’s a technique that suits some situations, but like many areas of FileMaker, does not suit all situations.
Kevin – thanks for taking the time to do the blog.
More nonsensicalness: I also favor avoiding adding fields if a reasonably elegant alternative is available. Adding fields, over time, makes for more visual clutter and additional time for the developer to select a desired field, and, more importantly, each indexed field (such as a calculation of the month that will be used in searches) adds to the time that it takes for a new record to be saved after the dependent field has been modified (whether by manual data entry or during an import). I have an old solution, with quite a few auto-enter values and many calculated utility fields such as this, which takes about two seconds for a new record to be created – even with a fairly fast server/network/client.