Update 30 September 2023: Free end point no longer available for this service. See JSON Currency Exchange Rates, v3 for a revised, functioning demo.
Introduction
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.
Disclaimer: This CF is a work-in-progress that I am sharing freely with the developer community. It may contain bugs or not behave the way you think it should. It may not be performant when processing large JSON structures. Use at your own risk, and with a healthy dose of skepticism and common sense.
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.
Closing Thoughts
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.
One thing worth pointing out, is that this will change the presentation of a number from scientific notation to standard notation… but it will not restore any precision lost from the original FM JSON function’s conversion to scientific notation. For example:
The number 1000000000000000900 is rendered by FM’s JSON functions as 1e+18, which this custom function renders as 1000000000000000000.
So if that precision is important, then you have to code against the scientific notation happening in the first place, probably by forcing it to JSONString.
Excellent point, Shawn. There are down sides to encoding numbers as strings in JSON, but in this case it would be the lesser of two evils.