This is a follow up to a behavior I mentioned last month where FileMaker’s JSON functions can transform a number into scientific notation when you might prefer to have that number remain in standard notation.
Demo file: json-force-standard-notation.zip
Note: demo file uses JSONGetElementType so requires FM 19.5 or later.
The overall functionality of the demo was covered last time. This time we’re going to focus on working around the behavior, followed by some related observations… but first let’s review.
What Problem Are We Trying To Solve?
When you click “Get Rates” with BTC (Bitcoin) as one of the selected currencies, all values, including the BTC rate, come in as standard notation in the raw JSON, for example…
…but as soon as JSONDeleteElement, JSONSetElement, JSONFormatElements, or even — under certain circumstances — JSONGetElement, is invoked, the BTC values will be rendered in scientific notation.
As we saw a couple years ago in Bypassing Scientific Notation, there’s a simple hack to force a conversion from scientific to standard notation.
The tricky part is getting the standard notation to “stick” when you push the value into a JSON object or array, because as soon as you invoke a transformative JSON function, that value will be “helpfully” converted to scientific notation.
I ended last month’s article by wondering, “…given that formatted (a.k.a. prettified) JSON is intended to be read by human beings, and for the sake of consistency, wouldn’t it be nice to find a way to display that number in standard notation?”
Custom Function To The Rescue
Well, as the saying goes, there are many ways to skin a cat, and one way would be to use today’s custom function, JSON.ForceStandardNotation.
Here are some notes on the custom function:
If the input is non-JSON or if the CF cannot identify any scientifically-notated numbers, it will return the input unchanged.
If an otherwise-valid value is surrounded by quotes, the CF will interpret it as text and not transform it.
The CF should be invoked after any FileMaker JSON functions have been applied. If you subsequently apply a JSON function, it will override any CF transformations.
Miscellaneous Demo Observations
1. After changing any of the check boxes you need to click Get Rates to see the results.
2. The “Sort” check box is dependent on the “Format” box being checked. If you uncheck “Format”, “Sort” will auto-uncheck. If you check “Sort”, “Format” will auto-check. Note: “Sort” is a hack to get “start_date” to appear before “end_date”.
Other Miscellaneous Observations
1. Regardless of FileMaker file or system formats, the decimal separator in a JSON number is always a dot.
2. In JSON a number cannot begin with a dot so while FileMaker considers
.5 to be a perfectly valid value, in JSON there must be a leading zero like so:
0.5 — of course you’ll be fine if you generate your JSON via JSONSetElement, because the function will take care of adding that leading zero if necessary, but it is something to be aware of if you build your JSON by hand.
3. The “E” in FileMaker scientific notation is upper case. In JSON the “e” is lower case.
4. For small numbers scientific notation kicks in at 10^-5 but for large numbers you have to reach 10^18 before it kicks in.
5. For some odd reason if you point JSONFormatElements at a hand-coded “e+” value where the power is between 1 and 17, a
.0 will be appended.
6. This superfluous dot zero does not break anything. A single superfluous
.0 is allowed and retained; additional zeros won’t break anything but they will be ignored.
7. Interestingly if you retrieve a number with superfluous zeros via JSONGetElement or JSONListValues, the single superfluous dot zero will be included.
8. I haven’t experimented to see what the limit is, but it turns out that FileMaker is extremely tolerant of superfluous zeros when you express a number in scientific notation.
I purposely did not dig into the code of the CF in this article. If you have questions, or suggestions for improvement, feel free to post them here.