JSON, Level: Intermediate

A JSON Miscellany, part 1

In this 2-part series, we’re going to take a look at various JSON-related topics, including a bug fix in FM 19, two free web services that return useful information in JSON format, and several ways to parse a found set into a JSON object.

Fixed in 19: JSON validation bug

In FM 18 (but not in 16 or 17) JSONFormatElements and other JSON functions can mask mangled JSON errors, making them harder to detect and correct. I wrote about this in detail last year, but, briefly, a JSON bug that was introduced in FM 18 has been corrected in FM 19.

Steps to replicate:

Create some JSON, and then transform it, but intentionally botch the transformation, for example, attempt to update an array using object notation:

2020-07-05_125525

After step 1 executes, the $$json variable contains this JSON array…

2020-07-26_181815

…but after step 2 runs, the array has been ruined by the attempt to update it as if it were an object. At this point (in FM 16 or later) if you view $$json in the Data Viewer you will see that your JSON is well and truly mangled.

2020-07-26_180039

Now we come to the bug: in FM 18 if you wrap $$json in JSONFormatElements, your original $$json from step 1 is displayed nicely formatted as if there were no problem!

2020-07-26_183012

Good news: this has been remedied in FM 19:

2020-07-26_183648

Note: the bug fix is not limited to JSONFormatElements… these functions were also affected by the bug in 18, and now work properly in 19…

…which is to say that they now correctly return an error message when pointed at mangled JSON transformations.

Package Tracking

2020-07-31_15-13-08

Demo file: json-package-tracking.zip

Thank you Tim Ballering for mentioning Package Place recently on the FMP Experts list. If you’ve ever wished you could easily pull package tracking info into FMP, your wish has come true: submit your tracking number, Package Place figures out which carrier it corresponds to and returns tracking info as JSON. And the cost is extremely reasonable: free.

Supported carriers:

  • Canada Post
  • DHL
  • FedEx
  • SkyNet Worldwide
  • USPS
  • UPS

The demo file includes a number of different valid tracking numbers… and of course you can enter your own as well. Click the Update button to run this script:

2020-07-31_14-21-24

Once the field is populated, you can click Latest Info to see the current status of the package in question.

2020-07-31_15-16-56

Finally, here’s the underlying calculation syntax for Latest Info…

Let ( [ 
   json = JSON::json ; 
   shipper = JSONListKeys ( json ; "" ) ; 
   indices = JSONListKeys ( json ; shipper ) ; 
   current = RightWords ( indices ; 1 ) ; 
   status = JSONGetElement ( json ; shipper & "[" & current & "]" ) 
] ; 

JSONFormatElements ( status )

)

…and early versions of the demo file used an unstored calculated text field inside the popover to display the info, but as regular readers of this blog will know, I’m not a big fan of polluting table schema for display purposes, and I realized a field, calculated or otherwise, was overkill in this modern FM era.

So what to do instead? Well, one obvious alternative would be to use a merge $$variable, but… yawn… been there, done that. As an alternative I decided to go with a trick I (along with many other developers) have been using more and more since button bars were introduced in FM 14: in place of a calculated field, I used a single-segment, do-nothing button bar and embedded the calculation logic into the label definition, like so:

2020-07-31_16-11-05

This trick effectively provides something many of us have long been asking / wishing / begging for: calculations definable at the layout level vs. at the table schema level. And, having digressed a bit from JSON itself, I think that’s enough for today.

But Wait, There’s More…

Next time we’ll look at a free service from the European Central Bank to perform currency conversions with the results coming back in JSON format, and a number of different ways to render a found set as a JSON object.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.