31 July 2017: As per my exchange with Beverly Voth in the comments section, the date encoding technique used here is strictly intended for situations where FileMaker will both produce AND consume the JSON.
Demo file: json-plus-virtual-list-part-2.zip
This is a short follow-up to part 1, to demonstrate an alternative idea re: JSON date encoding. As you may recall, JSON does not have a “date” type, and one of our goals is to encode and decode JSON dates in a region-agnostic manner (e.g., encode in a region where MM/DD/YYYY is the default, but then decode in a region where DD/MM/YYYY is the default).
To accomplish this goal, in part 1, we wrapped our FileMaker dates inside a GetAsNumber function when pushing them to JSON so they looked like this:
The advantage of the above approach is that it satisfies our requirement for region date agnosticism. FileMaker date fields will correctly interpret the highlighted numeric values regardless of location and/or system date preference.
The disadvantage is that the above dates are not human-friendly when it comes to readability. One possible work around would be to use custom functions, such as Jeremy Bante’s, to encode the date in ISO 8601 format (YYYY-MM-DD), but then you will also need to take extra steps to decode it: FileMaker date fields do not automatically interpret YYYY-MM-DD.
So is there a region-agnostic trick we can use to encode dates in a human-readable manner, without having to jump through any additional hoops when decoding? One possibility would be to…
- Encode them in ISO 8601 format, i.e., as YYYY-MM-DD
- Substitute “+” for “-“, so we end up with YYYY+MM+DD
This will encode the dates in so-called “Japanese” date format…
…which FileMaker date fields will interpret natively. No need to change anything in the virtual list table.
For more on Japanese-style dates see Birthday Challenges part 1 and part 2 (both of which include spirited exchanges in the comments section), or scroll down to Tip #7 in Tips ‘n’ Tricks, part 1.
As the sayings go: this is food for thought and YMMV (“your mileage may vary”).
These are great tips! GetAsNumber(dateField) in FileMaker is the number of days since 1/1/001. Excel (and other systems) may use another ‘range’ for dates as numbers. YMMV indeed, using this method for exchanging JSON “date” data with other systems.
Thank you Beverly. You’re absolutely right. The techniques used to encode dates here, and in part 1, are strictly intended for situations where FileMaker will produce AND consume the JSON.
I have used JSON and XML to communicate with an Oracle system (peoplesoft), and the dates I get from them are formatted using YYYY-MM-DD. Not sure if that is an API “standard”, but it is easily understood: when you start the date with the year, then it is understood that the next two digits are month, followed by the day. It is only when you put the year at the end, that confusion sets in.