Best of 2021: Get Record as JSON

Get Record as JSON

This is my personal favourite in this "Best of 2021" series — I guess that makes it the Best Of The Best! This is a really simple and reusable script for obtaining an entire record represented in JSON format.

I built this initially for a debugging tool used in a client solution (see an upcoming article for this). The user could press a button to submit a debug report to the developer. Part of that debug report is a JSON representation of the record they are currently viewing. This was really useful in helping to diagnose issues because I was able to see all field values at the time of the issue. 

As usual I've prepared a sample file to show you how this works which you can download here.

Download Get Record As JSON example

The JSON is built using 2 key features. The first is using FileMaker's underlying internal SQL functions to obtain a list of all fields within a desired table. Using this list of fields it becomes rather easy to then formulate an executeSQL query to return all these field values from a nominated record.

This script needs a single parameter which is a reference to the primary key field of the record you wish to return a JSON representation of. We use the GetFieldName function to achieve this.

From this single reference the script can derive the table occurrence name. Using the underlying FileMaker_Tables table, an executeSQL query can obtain the base table name. Using this, we can then query the FileMaker_Fields table to obtain a list of all the fields within this table.

Finally we can formulate a query to get all fields values from the desired record in that table by using the known primary key name, and value (obtained through the Evaluate function).

This is easiest to be seen just by looking at the script yourself in the example file.

You may wonder why we don't just obtain "*" from the table to get all field values, in favour of explicitly asking for every field by name. The reason for this lies in the fact the FileMaker_Fields internal table allows us to also obtain information such as field type, and storage type (e.g. calculation, stored, global).

This is useful because it allows you in the script to set some criteria as to what types of fields you wish to return for your JSON representation. You may wish for example to omit summary fields, unstored calculations, global fields, containers etc. 

This loop to extract certain fields has been left in the script and is well commented but for the sake for the example I have left every field included.

NOTE: Because the field values are obtained through an executeSQL query this technique does not cater for repeating fields or container fields. A container field will return the file name but not the field contents per se; repeating fields will return the first repetition value only. You can always represent a container by create a calculation for it which is the base64 encoded representation.

Example File

What FileMaker Weetbicks article wouldn't be complete without an example file? In this you'll find everything discussed in the article. I think the best way to learn is to jump in and explore.

Click here to download the example file

We love feedback so please leave a comment if you have anything to say - even if you don't it would be nice to hear from you :)