How to pack up an entire FileMaker record into a JSON object using a simple modular script
In FileMaker development, as in any development environment, we have lots of tricks and techniques we use to get things done – from the simple (like automatically removing text formatting in a field) to the advanced (using “magic key” style transactions). In my development work, I often have a need to move data from one place to another, and I’ve found that JSON has become my go-to format for accomplishing this, specifically because it’s well suited to this exact use case: storing and moving structured data. If you’re not familiar with using JSON in FileMaker, then it’s high time you learned. Go read my step-by-step guide to building JSON in Filemaker.
A few years ago, a colleague of mine built a quick and easy script to pack up an entire FileMaker record into a JSON object (which I’ve since adapted and optimized), and I’ve found myself using her technique over and over again. It’s particularly useful when parsing data from a flat data source into multiple tables, but I’ve also used it to pack up an entire record, add metadata, encode the entire thing in Base64 format, and send it across the internet to another server using the Data API.
The basic premise is this: using a list of the field names in your table, you pull a record’s value from each field in the list, and add it as an element to a JSON object. By the end of this process, you have a list of all fields in the table and their values, neatly packed up in a single JSON object!
Here is the entirety of the script:
This technique leverages the ExecuteSQL function and FileMaker System Tables to get a list of the fields in the table you’re sitting on. We start by getting the table name and the list of fields in that table:
This results in two variables, called $$table and $$fieldList, which will look something like this:
We then count the number of fields in our table, and prepare to loop through the list of fields:
In the next step, we loop through each of the field names in $$fieldlist, use the GetField() function to get the contents of this field in this table, and add it as an element to a JSON object called $record:
At the end of this loop, we end up with a variable called $record that looks something like this:
{"BirthCountry":"USA","DOB":"8/11/1954","Email":"marcus@odiorne.com","First":"Marcus","Language":"","Last":"Aurelius","Middle":"K","PersonID":"109992","Race":"White","SexAtBirth":"M","cAge":"66"}We can reformat it using the JSONFormatElements() function so that it looks better:
{
"BirthCountry" : "United States",
"DOB" : "8/11/1954",
"Email" : "marcus@odiorne.com",
"First" : "Marcus",
"Language" : "",
"Last" : "Odiorne",
"Middle" : "K",
"PersonID" : "109992",
"Race" : "White",
"SexAtBirth" : "M",
"cAge" : "66"
}At the end of the script, we can add metadata to this JSON block, if we want, but this is totally optional. You might consider things like this:
{
"Record" :
{
"BirthCountry" : "United States",
"DOB" : "8/11/1954",
"Email" : "marcus@odiorne.com",
"First" : "Marcus",
"Language" : "",
"Last" : "Odiorne",
"Middle" : "K",
"PersonID" : "109992",
"Race" : "White",
"SexAtBirth" : "M",
"cAge" : "66"
},
"metaData" :
{
"Account Name" : "Admin",
"DeviceID" : "C56C3D9278D302299180B26B584EEE",
"Host IP" : "",
"PrivSet" : "[Full Access]",
"ScreenSize" : "2560x1440",
"System IP" : "192.168.0.7",
"SystemVer" : "14.4.1",
"Time" : "4/12/2024 1:58:27 PM"
}
}In the last step of the script, there is an exit step, which returns the JSON result:
Exit Script [ Text Result: $result ]This hands the JSON back to the parent script, which you get by calling the Get ( ScriptResult ) function:
Set Variable [ $record ; Value: Get ( ScriptResult ) ]That’s it!
Once you have the record packed up in this way, you can pull individual elements out by using FileMaker’s built-in JSON functions. For example, if you wanted to go to a new table, create a record, and set some fields in that new record to some of the elements in the $record example above, you’d do something like this:
It’s important to note here that the data in the JSON variable is all in captured in “string” format, meaning the data types are not recognized as Number, Date, Boolean, etc. While this is advantageous in one respect – FileMaker does not try to reformat anything – when parsing the data, you may be required to convert the elements to the data type that you need, particularly if you’re pushing data into a different system. As an example, you may pull a date out of your record as a string like this: “4/26/01”. When pushing it into another system, you might need to use a FileMaker function like GetAsDate() to recast it as an actual Date data type, or reformat it into ISO format (like ‘2024-04-26’). Similarly, recasting the parsed data type using GetAsBoolean() or GetAsNumber() might be required.
Notice that the beginning of the script has a step that checks to see if a global variable called $$table is the same as the current table name:
If [ $$table ≠ Get ( LayoutTableName ) ]This is simply to save time if you’re calling this script inside a loop. Running the ExecuteSQL function against the FileMaker System Tables can be rather slow; it can take up to a half-second to generate the field list. While this would not typically be an issue with a single script call, when looping through records, it will add a significant slowdown to the process if done repeatedly. Since a looping process would usually be performed in the same context again and again, there’s no need to get the same list of fields for each loop, so we start by setting the table name that we’re sitting on in the global $$table variable. If the context of the script is the same for each loop, we can reuse the existing $$fieldList for the next loop!
This technique has been so useful in my FileMaker development that I return to it again and again. I hope you can find a use case for it in your own work. Happy data packing and unpacking!