JSON has now become a FileMaker developer’s standard method to store and manipulate data in memory. In other words, when you want to create a variable to store anything more complex than a simple string, number, or return-separated list, JSON is the way to do it. If you’re not using it already, you must learn how!
Starting out it can be a bit daunting, so I’m going to break it down for you. It can really shine when used to store structured data in a way that makes it easily accessible. I’m going to show you how to store FileMaker records as JSON and then extract the data you want using a script.
You can test all of these steps using the Data Viewer in FileMaker’s advanced toolset, so follow along!
Step 1 – Create a basic object
The basic function we use to build JSON in FileMaker is the JSONSetElement function. The data viewer will autocomplete the following for you (just start typing “JSONSetElement”):
JSONSetElement ( json ; keyOrIndexOrPath ; value ; type )You’ll need to replace all the arguments with actual stuff, so:
- Change ‘json’ to just two double quotes: “” – this simply makes an empty JSON object
- Change ‘keyOrIndexOrPath’ to firstName in quotes: “firstName” – this is called the ‘key’
- Change ‘value’ to your actual name in quotes: “David” – this is the value of the key
- Change ‘type’ to “JSONString”. You can also just put another set of double quotes “”, and let FileMaker figure out what kind of data the value is.
Congratulations, you just made a simple JSON object! It should evaluate to something like this:
{"firstName":"David"}This just means that for this JSON object (all the stuff inside the curly braces), the key “firstName” is equal to the value “David”. In FileMaker terms, the ‘object’ is a record, the ‘key’ is a field name, and the ‘value’ is the contents of the field.
Since this object represents a person, we should put it inside an object called “person”. We can adjust the code slightly like this:
JSONSetElement ( "" ; "person.firstName" ; "David" ; JSONString )Which results in a JSON object that looks like this:
{"person":{"firstName":"David"}}Step 2 – Add fields
Now we can start to construct a “record” in JSON. Take the JSON function you just wrote, and put some brackets around the 2nd, 3rd, and 4th arguments, like this:
JSONSetElement ( “” ; [ “person.firstName” ; “David” ; JSONString ] )Let’s add another field — this time the key will be “lastName”, and the value will be your last name. The way to add it as another “field” in this JSON object it to put a semicolon after the second bracket, and then replicate the structure of the first field, like so (I’ve added carriage returns and tabs to make the code more readable):
JSONSetElement ( “” ;
[ “person.firstName” ; “David” ; JSONString ] ;
[ “person.lastName” ; “Weiner” ; JSONString ]
)You can continue to add as many keys (or fields) as you want:
JSONSetElement ( “” ;
[ “person.firstName” ; “David” ; JSONString ] ;
[ “person.lastName” ; “Weiner” ; JSONString ] ;
[ “person.age” ; 49 ; JSONNumber ] ;
[ “person.phone” ; “(503)616-9422” ; JSONString ] ;
[ “person.extension” ; 3 ; JSONNumber ] ;
[ “person.hasPets” ; False ; JSONBoolean ]
)This chunk of code results in the following JSON:
{"person":{"age":49,"extension":3,"firstName":"David","hasPets":false,"lastName":"Weiner","phone":"(503)616-9422"}}If we take the entire chunk of code and surround it with the JSONFormatElements function, we can make it look better. This doesn’t fundamentally change it at all, though. It just changes the way it displays:
JSONFormatElements ( your JSONSetElement code here ) results in:
{
"person" :
{
"age" : 49,
"extension" : 3,
"firstName" : "David",
"hasPets" : false,
"lastName" : "Weiner",
"phone" : "(503)616-9422"
}
}Notice that the keys inside the person object are alphabetized. This can look a little confusing, but that’s just the way it works. Also, notice that this is an object inside an object. The {curly braces} enclose a JSON object. This leads us to our next step…
Step 3 – Add records
Let’s say you want to add another person record to this JSON. The usual way to start collecting an array of records in JSON would be to hold it in a variable, so let’s assume you started with a variable, called $json, and it contained the data from the previous step. We actually need to put an array (which is simply a list) of records inside the person object. The array is a list of JSON objects, which each contain a set of fields. We need to adjust our last bit of code to create an array:
JSONSetElement ( “” ;
[ “person[0].firstName” ; “David” ; JSONString ] ;
[ “person[0].lastName” ; “Weiner” ; JSONString ] ;
[ “person[0].age” ; 49 ; JSONNumber ] ;
[ “person[0].phone” ; “(503)616-9422” ; JSONString ] ;
[ “person[0].extension” ; 3 ; JSONNumber ] ;
[ “person[0].hasPets” ; False ; JSONBoolean ]
)This just tells the function to put all the information inside the first slot of an array inside the person object. JSON is 0 indexed, so the first slot is index [0], then [1], [2], etc. The result is this (note the [square braces], which indicate the array):
{
"person" :
[
{
"age" : 49,
"extension" : 3,
"firstName" : "David",
"hasPets" : false,
"lastName" : "Weiner",
"phone" : "(503)616-9422"
}
]
}Now, you can inject a new item into the $json variable, inside the person array by referencing the next index number:
JSONSetElement ( $json ;
[ “person[1].firstName” ; “Kimberly” ; JSONString ] ;
[ “person[1].lastName” ; “Carlson” ; JSONString ] ;
[ “person[1].phone” ; “(503)616-9422” ; JSONString ] ;
[ “person[1].extension” ; 2 ; JSONNumber ] ;
[ “person[1].hasPets” ; True ; JSONBoolean ]
)Which results in the following:
{
"person" :
[
{
"age" : 49,
"extension" : 3,
"firstName" : "David",
"hasPets" : false,
"lastName" : "Weiner",
"phone" : "(503)616-9422"
},
{
"extension" : 2,
"firstName" : "Kimberly",
"hasPets" : true,
"lastName" : "Carlson",
"phone" : "(503)616-9422"
}
]
}Now we have two records in the JSON array! That’s basically it. You can continue to add as many records as you want. You can even make arrays inside of arrays. Then you can pull out individual items with the JSONGetElement function like this:
JSONGetElement ( $json ; "person[1].firstName" ) I use this technique regularly to loop through records, pulling out bits of data from each one and storing them in an array. Then I can move to another context and parse items from the array and deposit them elsewhere, or even export them! There are so many ways you can use this. What do you think you could do with it?
If you have question regarding this method, feel free to contact us!
*This article was originally written for AppWorks, which has since joined Direct Impact Solutions. This article is intended for informative purposes only. To the best of our knowledge, this information is accurate as of the date of publication.