Dear FileMaker Hacks,
In my personnel database, how can I find all birthdays in a given range, for example, between September 8 and November 15?
— Perplexed in Peoria
Dear Perplexed,
You do come up with some interesting questions. As I recall from our previous correspondence, you are in the US, where dates are formatted M/D/Y, so we’ll use that format for our initial examples. At first glance, your challenge seems like it ought to be solvable by simply querying the date_of_birth field for…
…but that’s not a valid date range, and FileMaker treats it exactly if you’d entered */*/* (or even just * for that matter), and all records with data in that field are returned.
To make this work, you will need to issue a separate find request for each month or partial month in the range, like so:
In other words, for any year, locate birthdays between Sep 8 and Sep 30, plus any birthdays in October, plus any birthdays between Nov 1 and Nov 15. And as you can see, this works just great, as long as your database is deployed in a region where M/D/Y is the standard date format.
But as I recall, you will also be deploying a copy of your database in Australia, where dates are formatted D/M/Y, and the above syntax won’t work for reasons I outlined in part 1. So, once again, I recommend that you use the Japanese date format (Y+M+D), and script the above find as…
Request 1: *+9+{8..30}
Request 2: *+10+*
Request 3: *+11+{1..15}
…which will work in both the US and Australia (and any other place that uses M/D/Y or D/M/Y). Incidentally, here’s a demo called birthday-finder that uses the Japanese date format and popup calendars to set the birthday range.
Enjoy,
FileMaker Hacks
Kevin, It’s not the “Japanese date format (Y+M+D)”, it’s the
ISO (International Organization for Standardization) format (ISO 8601).
昭和44年03月16日 in Japanese 16 March 1969 in English.
Thanks Winfried. One of FileMaker’s (former) engineers refers to it as Japanese format, so I am guilty of imitating my betters.
See this thread on FM Forums —
http://fmforums.com/topic/52262-searching-date-range-by-day-month-multiple-years-range/#comment-245196
While it may be based on an ISO standard, I think Japanese format is correct in the context of FileMaker. Enter a Year value of 0 through 25 and FileMaker performs data math from the start of the Heisei emperor’s reign. So “1+9+30” = September 30, 1989. The Year is the only element that accepts a 0, so “0+9+30” = September 30, 1988.
Entering (in a US system) “9/30/1” = September 30, 2001 and “9/30/0” = September 30, 2000.
That difference strengthens, in my mind, the implication that the format is intended for Japanese dates. More evidence: Entering a year value of >= 26 and <= 64 adds that year value to the start of the Showa emperor’s reign, while values and >=65 and <= 99 are interpreted as 1900 + that value. I guess the take-away for Western users of this format is "always use 4 digit years" when a year is relevant to your search.
Thanks for that lucid explanation Geoff.
Thanks for the tip and the demo. May I suggest (since I can’t figure it out myself) changing the sort order so when the date range spans end of year, the end of year values come first. In other words, it would list the December birthdays before the January ones, because typically I would be using this to find upcoming birthdays, and I want the sorted list to show the ones closest to today (or closest to the start date) first.
You’re absolutely right; that was a shameful lapse on my part. But good news, I have updated the sorter calc in the demo and reposted it, so it now behaves as per your request… the new def for the sorter calc (number result, unstored) is…
Month ( date_of_birth ) +
If ( DayOfYear ( date_of_birth ) < DayOfYear ( g_date_begin ) ; 12 ; 0 ) &
"." &
Right ( "0" & Day ( date_of_birth ) ; 2 ) &
Year ( date_of_birth )
Brilliant. Thank you, Kevin.