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.
As the sayings go: this is food for thought and YMMV (“your mileage may vary”).