Today we are going to look at a way to use JSON for passing multiple parameters to a FileMaker script. Regardless of whether you are an experienced FileMaker developer or if you have only the barest inkling of what JSON is, not to worry, this article is designed for FileMaker developers of all skill levels.
As I mentioned in a related article Jotform to FileMaker, JSON is simply a type of structured text formatting and stands for “JavaScript Object Notation”. Despite your experience (or comfort) level with JavaScript, JSON is nothing more than a great way to store and pass structured data (even related data) as a single text string that is easy for both humans and computers to understand.
Why should all FileMaker developers learn JSON?
- JSON is easy to use and offers a simple structure of organization that helps provide standardization and cleaner code.
- It’s native: JSON specific functions have been included in FileMaker since version 16.
- JSON is THE standard used by many APIs and web services, including the FileMaker API.
- It is a convenient and popular way to store structured data as a simple text string.
- Using JSON to pass multiple parameters is easier and less error prone than previous methods (like return- or pipe-delimited lists) because FileMaker’s built-in JSON functions auto-encode and auto-decode potentially problematical characters (such as quotation marks, hard returns, tabs and backslashes).
Quick JSON Reference for FileMaker Developers
There is a ton of information and material on learning about JSON, but very little on how it pertains to using it as a FileMaker developer. Therefore, I will provide some basic information on JSON syntax here.
Curly { } Braces = JSON Object
JSON objects are always enclosed within a set of curly braces like shown below:
{"first name":"Joe","last name":"Schmoe","phone":5551234444,"email":"joe@blah.com"}
This is a simple JSON object. Notice how within the open and closing curly braces there are a number of name/value pairs separated by a colon? (Note: in JSON “names” are also known as “keys”, so I could just as easily use the term “key/value pairs” – the expressions are interchangeable.)
If we wanted to compare this sample JSON object to a concept within FileMaker, we could say that everything enclosed within this pair of curly braces represents a single record. Thus, each name/value pair could represent a particular attribute for a contact named Joe Schmoe. Now in reality, a JSON name (like “first name”, “last name”, “phone” or “email”) can be whatever you wish, but the idea is to use something descriptive so you can remember what kind of value it will contain.
You may have noticed how all our name/value pairs are wrapped in quotes except for the phone number value. That is because JSON differentiates between regular strings (or text) which require “ ”, and integers (or numbers) that do not.
NOTE: We are not going to dive into the various JSON value types in depth in this article but it’s good to be aware they exist, especially if you ever plan on working with JSON returned by an API. If this concept of using JSON to store values as integers vs. text is confusing, simply wrap all JSON values in quotes to treat them as text. After all, FileMaker itself can easily convert any text input into various value types later.
Name/Value Pairs
All JSON objects are made up of one or more name/value pairs. Most values will be a string or integer, but some values may be another JSON object!
{
"ID" : 501 ,
"Location" : "Denver" ,
"Parent" : { "first name" : "Joe" , "last name" : "Schmoe" }
}
Here the value of “Parent” (i.e., everything after the “:”) is a JSON object containing its own nested name/value pairs.
Square [ ] Brackets = JSON Array
While a single JSON object is great for storing a single unit (or record) of information, what if you have multiple contact records that you want to express via JSON? Well then you could simply list a group of JSON objects within a set of square brackets like this:
[
{
"first name" : "Joe",
"last name" : "Schmoe",
"phone" : 5551234444 ,
"email" : "joe@blah.com"
} ,
{
"first name" : "Amy",
"last name" : "Schmoe",
"phone" : 5551234442 ,
"email" : "amy@blah.com"
} ,
{
"first name" : "Suzie",
"last name" : "Schmoe",
"phone" : 5551234443 ,
"email" : "suzie@blah.com"
}
]
This is what is known as a JSON array. Notice how it is nothing more than a comma-separated list of individual JSON objects enclosed by opening and closing square brackets? Within a JSON array, each object is assigned an index beginning from zero. So Joe Schmoe’s object is index 0, Amy’s is index 1, and Suzie’s is index 2 respectively.
Index, Key or Path
Most of the JSON specific functions within FileMaker accept an array index (i.e., “[0]”, or “[1]”), object key (aka a Value name like “first name”), or path (basically a combination of one or more keys or indexes like “[0].first name”) as arguments. To illustrate this, we use the JSONGetElement function which is structured like this: JSONGetElement ( json ; keyOrIndexOrPath ).
Using JSONGetElement
If we use the above JSON array as our “json” source (i.e., data in a FileMaker text field value or variable) here are some sample outcomes:
JSONGetElement ( json ; “[0]” ) or JSONGetElement ( json ; “[]” )
In this example, we are getting the entire Joe Schmoe { object } by the given [ array ] index of 0 or empty.
{
"first name" : "Joe",
"last name" : "Schmoe",
"phone" : 5551234444 ,
"email" : "joe@blah.com"
}
JSONGetElement ( json ; “[1]” )
This will grab the entire Amy Schmoe { object } by the given [ array ] index of 1.
{
"first name" : "Amy",
"last name" : "Schmoe",
"phone" : 5551234442 ,
"email" : "amy@blah.com"
}
JSONGetElement ( json ; “[1].email” )
This will grab the entire Amy Schmoe { object } by the given [ array ] index of 1.
Here we are using the index of 1 and key of “email” to construct the JSON path of “[1]email” enabling us to extract the email value from the Amy Schmoe { object }."amy@blah.com"
Creating JSON statements in FileMaker
So now that we have reviewed the basics of what JSON is, how it is structured, and how to read and extract particular JSON objects or values, it is now time to create some JSON using FileMaker.
While the JSONGetElement function is great for parsing JSON, to create and update JSON we will use the JSONSetElement function, which is expressed like this: JSONSetElement ( json ; keyOrIndexOrPath ; value ; type ).
Using JSONSetElement
The “json” parameter for the JSONSetElement function can be a JSON { object }, [ array ], or reference to any existing JSON script parameter or field value. This is especially helpful if you wish to update or add to an existing JSON statement. In the interest of this post however, we will only discuss how to use the JSONSetElement function for creating new JSON strings.
When using the JSONSetElement function to create a new json object, the “json” function parameter can be left blank like this:
JSONSetElement ( “” ; “name” ; “Joe Schmoe” ; JSONString )
However, doing so will default to creating a JSON object only (i.e., not an array). Additionally, this minimalist approach is perhaps a bit more confusing to understand and read, so we recommend always explicitly declaring the type of JSON you wish to create by using “{}” for declaring a new JSON object (the default), or “[]” for declaring a new JSON array.
JSONSetElement ( “{}” ; “name” ; “Joe Schmoe” ; JSONString )
This returns a simple JSON object with one name/value pair. No index or path just a simple key of “name”.
{
"name" : "Joe Schmoe"
}
JSONSetElement ( "{}" ;
[ "first name" ; “Joe” ; JSONString ] ;
[ "last name" ; “Schmoe” ; JSONString ] ;
[ "phone" ; 5551234444 ; JSONNumber ] ;
[ "email" ; “joe@blah.com” ; JSONString ]
)
Here we are creating the single Joe Schmoe object that we used earlier. Once more there is no path or index, only keys, values, and types.
NOTE: see how several name/value pairs are being added all at once? As with the Substitute function, you can pass multiple arguments simultaneously if they are enclosed in square brackets like shown. This is a FileMaker trick, so don’t let the use of additional square brackets throw you off!
{
"first name" : "Joe",
"last name" : "Schmoe",
"phone" : 5551234444 ,
"email" : "joe@blah.com"
}
JSONSetElement ( "[]" ;
[ "[0].first name" ; “Joe” ; JSONString ] ;
[ "[0].last name" ; “Schmoe” ; JSONString ] ;
[ "[0].phone" ; 5551234444 ; JSONNumber ] ;
[ "[0].email" ; “joe@blah.com” ; JSONString ] ;
[ "[1].first name" ; “Amy” ; JSONString ] ;
[ "[1].last name" ; “Schmoe” ; JSONString ] ;
[ "[1].phone" ; 5551234442 ; JSONNumber ] ;
[ "[1].email" ; “amy@blah.com” ; JSONString ] ;
[ "[2].first name" ; “Suzie” ; JSONString ] ;
[ "[2].last name" ; “Schmoe” ; JSONString ] ;
[ "[2].phone" ; 5551234443 ; JSONNumber ] ;
[ "[2].email" ; “suzie@blah.com” ; JSONString ]
)
This time we are creating a JSON array “[]” with three nested objects for Joe, Amy, and Suzie like we saw before. Since we are creating an [ array ], we use paths that explicitly declare each person’s index and values (like “[1].first name” for “Amy”) to build our JSON.
[
{
"first name" : "Joe",
"last name" : "Schmoe",
"phone" : 5551234444 ,
"email" : "joe@blah.com"
} ,
{
"first name" : "Amy",
"last name" : "Schmoe",
"phone" : 5551234442 ,
"email" : "amy@blah.com"
} ,
{
"first name" : "Suzie",
"last name" : "Schmoe",
"phone" : 5551234443 ,
"email" : "suzie@blah.com"
}
]
Using JSON to pass multiple parameters
We are now ready to use JSON to pass multiple parameters to a FileMaker script. While there are lots of ways we could illustrate the usefulness of this concept in practice, one common use case for this technique would be using it to allow FileMaker users to send an email via FileMaker server through the PSOS (Perform Script on Server) script step.
As most of us know, when you perform a script on server, it is like the server is opening your FileMaker file from scratch every time the PSOS script is run. Thus, any user context (like the current table occurrence, record, sort order, or found set of records) would either need to be re-created on the server environment or passed to the server as one or more script parameters.
Using what we have already learned, we will now use the JSONSetElement and JSONGetElement functions to do just this.
- Create a new FileMaker file (or use a pre-existing one of your own). For the sake of simplicity, we chose to use the default Invoices starter solution included with all copies of FileMaker Pro, and FileMaker Pro Advanced. (To follow along with our example, simply open your copy of FileMaker and from the Menu go to File > Create New, select the “Invoices” option and choose Create).
- Add a few records to work with so we have something to pass as script parameters. We simply added one or two customers and then a fake invoice or two.
- Create 2 scripts: one for creating our JSON object and passing it as a parameter, and another for receiving our JSON object and breaking out its respective values.
Script #1: “Pass email values as JSON”
This script really only needs two steps: one to create the JSON object using the current user context (line 9), and another to trigger the second script (line 11).
NOTE: for the purpose of testing and initial proof of concept we decided to use a simple “Perform Script” and “Send Mail via Email Client” script steps so readers can easily follow along without needing a running instance of FileMaker server or SMTP email settings. However, once everything is in place it should be very easy to update these two scripts to make this example work seamlessly for PSOS use.
Using what we have already learned, we will now use the JSONSetElement and JSONGetElement functions to do just this.
1. Create a new FileMaker file (or use a pre-existing one of your own). For the sake of simplicity, we chose to use the default Invoices starter solution included with all copies of FileMaker Pro, and FileMaker Pro Advanced. (To follow along with our example, simply open your copy of FileMaker and from the Menu go to File > Create New, select the “Invoices” option and choose Create).
2. Add a few records to work with so you have some data to pass as a script parameter. For the purpose of this article, we simply added one or two customers and then a fake invoice or two.
3. Create two scripts: one for generating the JSON object and passing it as a parameter, and another for receiving the JSON object and breaking out its respective values.
Script #1: “Pass email values as JSON”
This script really only needs two steps: one to create the JSON object using the current user context (line 9), and another to trigger the second script (line 11).
NOTE: for the purpose of testing and initial proof of concept we decided to use the “Perform Script” and “Send Mail via Email Client” script steps so readers can easily follow along without needing a running instance of FileMaker server or SMTP email settings. However, once everything is in place it should be very easy to update these two scripts to make this example work seamlessly for PSOS use.
Looking at the Relationship graph for the Invoices starter solution (you can find this in the FileMaker menu by going to File > Manage > Database then click on the “Relationships” tab), we see that there are two related tables from the context of the Invoices layout: Invoices and Customers.
Looking at the pre-existing fields for these two tables, we might decide to use the following to build our sample JSON object:
JSONSetElement ( "{}" ;
[ "invoiceNumber" ; Invoices::Invoice ID ; JSONString ] ;
[ "firstName" ; Customers::First ; JSONString ] ;
[ "email" ; Customers::Office Email ; JSONString ] ;
[ "status" ; Invoices::Status ; JSONString ] ;
[ "dueDate" ; Invoices::Due Date ; JSONString ] ;
[ "amountDue" ; Invoices::Total ; JSONString ]
)
Thus, when script #1 is run by a user from the Invoices layout the current invoice customer and invoice info will all be set into our custom JSON object and passed to the second script due to the context of the current table relationships.
Script #2: Receive JSON parameter and parse field values.
In this script we simply grab the passed JSON object script parameter (line 8), and then use the JSONGetElement function to extract our desired values (lines 11-16).
Lines 18, 19 and 20 use some simple text concatenation to construct some dynamic text like the email subject and body. The details of each are included here for reference:
$subject:
"Invoice #" & $invoiceNumber & " is " & $status
$daysRemaining:
If ( Get ( CurrentDate ) >= $dueDate ; "was due on " & $dueDate ; "is due on " & $dueDate )
$body:
"Dear " & $firstName & "," & ¶ & ¶ &
"Invoice #" & $invoiceNumber & " for " & $amountDue & " is " & $status & " and " & $daysRemaining
Lastly, we simply use the variables constructed from our JSON parameter to build and send the email.
NOTE: Since we are only using script variables to populate an email, the current layout or record context doesn’t matter as we are not directly referencing any fields when Script #2 is run. However, to avoid unexpected results or send potentially multiple emails at once, make sure you have the create “One email using data from the current record” option chosen.
Conclusion
Hopefully you now have a better understanding of JSON, how to construct and deconstruct JSON objects, and have learned how to use JSON to pass multiple parameters at once to a FileMaker script via the JSONSetElement and JSONGetElement functions. These functions make JSON a NATIVE aspect of FileMaker, so as developers it behooves us to be at least aware of some of its uses. JSON is also easy to use and offers a simple structure of organization that helps provide standardization and cleaner code.
Want to learn more about JSON? Check out our related post, Jotform to FileMaker, where we walk you through how to convert JSON API results into FileMaker records. If you found this post helpful, please spread the word by sharing it with others.
Subscribe to get the latest in your inbox.
Built with you in mind
Speak to one of our expert consultants about making sense of your data today. During
this free consultation, we'll address your questions, learn more about your business, and
make some immediate recommendations.
