By Daniel Wood, 29 November 2021
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.
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 :)
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments
Daniel Wood 06/12/2021 1:18pm (1 year ago)
You should check out a product I developed a while back called elemental_log which does just this. As part of its audit logging functionality it does obtain field listings from tables using these internal FM tables, and allows the user to nominate which fields they want to track for changes.
You can download a demo at www.elemental-fm.com/elemental-log
Daniel Wood 06/12/2021 1:17pm (1 year ago)
Hi Tobias, thanks for the comment. You are right the larger your solution the slower it can be to obtain a listings of fields from a nominated base table using the internal SQL tables because base table name has to be derived from FileMaker_Fields (which is a TO based listing) in order to then get field names from FileMaker_Tables.
Having said that, in FileMaker 19.4 there has been a few changes to these functions to make them run faster, as well as a name table FileMaker_BaseTableFields which can directly return you a field listing based on a base table name rather than via FileMaker_Fields table. This might help improve the calculation further.
Tobias Liebhart 03/12/2021 2:47am (1 year ago)
Thanks. We use a similar approach for our logging function. We noticed that using SQL takes quite some time though.
Since we use this in a calculation function of a field definition we were able to use the FieldNames function - which is lightning fast and the only requirement is that we have no layout with the same name as the table in our data file. So if you don't mind using unstored calculations and custom functions or while loops for this you can gain a bit of performance.
Charlie 02/12/2021 5:27am (1 year ago)
Daniel, this is really neat. I'm wondering if this could be utilized for a change log and how it could be incorporated.