FileMaker 16 JSON Script Parameters

FileMaker™ 16 introduces native JSON parsing which may finally give us a common language for passing multiple script parameters. This should make sharing scripts a lot easier. And while unpacking (reading) JSON script parameters is pretty simple, there are a few tricks to passing (writing) JSON using FileMaker’s new functions.

Overview: better script parameters

The new release of FileMaker 16 includes built-in functions for reading and writing JSON: a lightweight data-interchange format widely used to pass data around between web apps. This is why it was included in FileMaker 16: to complement the enhanced cURL support, but we can use it outside of a web context to make script parameters much more reliable.

While I’m relatively new to FileMaker, I have quite a bit of experience with JSON. And, honestly, I found FileMaker’s JSON implementation to be a little quirky/unique. I would venture to say that I even disagree with how the very important JSONGetElement function is implemented. This brings me to the first, and most surprising, thing about FileMaker’s JSON implementation:

No matter what data type you pass into your JSON parameter, the result will always be returned as text by FileMaker’s JSONGetElement function. Note. As of 16.0.2, JSONGetElement now correctly returns Numbers and therre Booleans. It no longer always returns Text =)

Weird, but we can work with it. More on this later =)

We’re going to be exploring whether it can be beneficial to pass multiple script parameters between FileMaker scripts using the JSON as the parameter format. To review, passing single parameters between scripts is built-in to FileMaker, but once you need to pass more than one parameter, you need to come up with some kind of grammar in order to tell one parameter from another. And it would be good if your grammar understood blank parameters and didn’t freak out when your parameter contained something unexpected like a carriage return.

Currently, in order to pass multiple parameters from one FileMaker script to another, you can:

Create global variables, which is messy as they need to be cleaned up each time the script runs. Plus, they don’s span files.

Use Let() notation like(name = value ; name2 = value2 ;)

Pass a single delimited text string with each of your parameters in order.

Is formatting script parameters as JSON an improvement?

(Spolier: Yes) But there are a few things to keep in mind and they’re mostly around writing JSON (as opposed to reading it). I’ve put together an example file showing how I like to write and read JSON in this context. You don’t need the file to follow along with this article, but there are some scripts you may want to copy from it when you’re parsing your own JSON. Download the example file here: JSONScriptParameters.fmp12

Writing JSON in FileMaker 16

First, let’s look at the method for writing a JSON string with multiple parameters. The following statement will create a JSON object with 3 name/value pairs (three script parameters) in it:

1

2

3

4

5

JSONSetElement("{}";

["Animal";SomeTable::Animal;JSONString];

["Age";SomeTable::Age;JSONNumber];

["Vaccinated";SomeTable::Vaccinated;JSONBoolean]

)

That would create a string which looks like this:

1

{"Age":12,"Animal":"Dog","Vaccinated":true}

And when that’s used as a script parameter, we can get the individual parameter we’re looking for like this:

1

JSONGetElement(Get(ScriptParameter);"Animal")

Which returns: “Dog”

If you want to play with JSON like this, I encourage you to put it in the data viewer so that you can change it more easily than you can in scripts:

There are a few things to note about this simple example:

Despite the order in which you pass your named elements, they will be in alphabetical order in your JSON.

and

The names are case sensitive, so JSONGetElement ( Get ( ScriptParameter ) ; “animal” ) wouldn’t work in this example.

Notice the 3rd parameter of each object specifying the data type (JSONString, JSONNumber, etc.). You either need to specify the type of each value that you’re passing, or let the JSON parser do that for you by leaving the third parameter blank (using “”) instead of something like JSONString. At this time there’s no auto-complete for these values, so you’ll need remember them.

You can find the 7 types of JSON values in the documentation, but here they are for reference (click for details):

JSONNumber

The value included in the JSON is included without quotes. This is the same as applying FileMaker’s GetAsNumber() function to the value before passing it in and will transform dates to numbers as shown in the third example.

Example input: 12345.67
Added to the JSON as: 12345.67
JSONGetElement result: 12345.67 (looks like a number, but remember: JSONGetElement always returns text, as shown in the second example)

Note that blank values passed in using JSONNumber will be sent as 0: use JSONNull if you want to pass a blank value.

Simple number parsing

Notice how JSONGetElement returns 10 as text instead of the number 10

JSONNumber performs the same transformation on a date as GetAsNumber()

JSONArray

A collection of delimited values tied to one name within your JSON. Some people might pass multiple values like this as return delimited text using JSONString, but using JSONArray lets you address an individual member of the array by position instead of using GetValue()

Example input: an array of objects. The objects can be any one of the 7 JSON objects. This can be used for a list of numbers “[1,2,3,4,5]” or text“[\“string1\”,\”string2\”,\”string3\”,\”string4\”]” or even a list of nested JSON strings.
Added to the JSON as: “[1,2,3,4,5]”
JSONGetElement result: a text value containing an array of objects that must be extracted using JSONGetElement

Example use: First, set the array to a local variable from the parameters: Set Variable [ $array : JSONGetElement ( Get(ScriptParameter) ; “array”] ]

Next, retrieve the first value from the array and set as another local variable: Set Variable [ $array1 : JSONGetElement ( $array ; 0)]

Keep in mind that the way FileMaker decided to implement this, with JSON arrays and objects, the first item is at the index 0, not 1. So the first item will be item 0, then 1, 2, 3 and so on. This can be confusing for FileMaker users since with functions such as GetValue, the first item in the array is 1.

JSONBoolean

True, true, “true”, 1, and any text containing a 1 are all equivalent to true. False, “False”, “True”, and 0 are equivalent to false. In reality, anything that does not match the 55 true options, such as “someText”, will be evaluated as false.

Example input: True
Added to the JSON as: true (note the lack of quotes)
JSONGetElement result: 1 Interestingly, no matter how you entered the boolean, the returned value will always be 1 or 0. If you pass the parameter “Vaccinated” as “true” and use the following if statement to read it:

1

JSONGetElement(Get(ScriptParameter);"Vaccinated")="true"

It would evaluate to false, since the value is actually 1, not “true”.

JSONNull

JSONRaw

The same as leaving the third parameter blank (using “”). The value you pass in is run by the JSON parser and it does its best guess as to data type. This actually works pretty well for text and numbers and returns blank for blank fields. It even treats arrays like arrays if they look like arrays =). But as you’l seen in our third, fourth, and fifth example below, it can make some strange assumptions when you mix text and numbers.

JSONRaw can make some strange assumptions about values that mix text and numbers.

JSONRaw treats this as text

JSONRaw looks for the opening and closing brackets and treats this as an array

Doesn’t quite understand dates

If numbers and text are present it seems to choose the first non-blank character to make its determination

Again, it guesses this is an array and discards the rest

As you can see, those not familiar with JSON may find this structure a bit difficult to write and may dismiss JSON as an option at this point.

One could make this easier to write by creating FileMaker custom function to help with JSON creation. Custom functions can be used to make common tasks easier and, in this case, they could obviate the need for remembering the third parameter by having it in the name, like “JSONSetNumber”. I find the third parameters pretty easy to remember, but regardless of how you write your JSON, the biggest benefit to passing parameters in JSON is that no matter which method you use to construct it, the string that is passed to the receiving script is always going to adhere to a standardized structure.

Reading JSON – unpacking your script parameters

Once we’ve passed our JSON array to our script in a parameter, this is where the benefits come in.
First, you’ll want your script to verify the received parameter is valid JSON:

Next, you’ll want to retrieve your values from the JSON string. You have a few options with this:

1. Explode each of the values in the array to a local variable using a loop or custom function.

2. Set each parameter needed to a local variable in its own JSONGetElement line at the top of your script.

3. Extract only the parameters you need from the script parameter as you use them.

With option two, possibly the most useful, you can set a single parameter from the input to a local variable using the following line:

1

Set Variable[$sc_Animal;JSONGetElement(Get(ScriptParameter);"Animal")]

This can be repeated for each parameter your script requires. The advantage to this method is that it lets another developer know which variables they have to work with just by reading the top of your script. Here’s just such a script from our example file:

Notice how lines 19-24 make it very clear which names I should use for the parameters I pass into this script. Given the parameter names, and knowing that the script expects JSON, I have everything I need in order to pass parameters to this script. That’s the big benefit here: I no longer have to examine the script to see how it wants to receive its parameters.

I really like writing one line for each of the requires script parameters as shown above. If, however, you want to loop through the elements and create a variable for any keys you happen to find, you’ll need to consider the catch with JSONListValues.

The Catch with JSONListValues – Return Characters

Let’s say you pass in the values of a drop-down list field containing multiple values. This will result in a JSON formatted string like this: {“Animal”:”Cat\rBird\rIguana”}

The \r in the string represents a return character. When we use the JSONListKeys method, we get the total number of keys/names passed in (1): Animal

However, when we use the JSONListValues method, each one of those return characters (\r) will add a new line to the result, so we’d have a total value count of 3:
Cat
Bird
Iguana

This means there are more values than keys/names, so we can’t match up the key/name index with the value index.

In order to account for this, we can substitute the two possible return characters, \n (char 10) and \r (char 13), before using JSONListValues, then substitute them back when looping through each variable. While you could replace these values with any string, I find it safer and easier to use a UUID so that your chances of that string being in any of your values is extremely unlikely.

This can be seen in our demo file in the script “Parse JSON Parameters – Return Character Substitution”

In this scenario, there would be more benefit had FileMaker left the escape character in the value so it could be handled later. I have a feeling that most developers would expect both the JSONGetKeys and JSONGetValues to return the same number of results at all times. The method, as-is, will probably result in a lot of headache for developers.

Best Practices for Numbers and Dates (wrap in GetAs… functions)

Because the JSONGetElement function returns dates as text (and returned numbers as text before version 16.0.2), you’ll want to be explicit about your data types when retrieving dates and numbers from JSON script parameters.

Numbers

Probably the most important thing to remember when using JSONGetElement is that before version 16.0.2 the result will always be a text value. Let’s say you’ve created the following JSON parameter:

1

$n=JSONSetElement("{}";"num";10;JSONNumber);

Even though you’ve specified the value type JSONNumber, JSONGetElement will return it as text.

This means that the following would evaluate to false:

1

JSONGetElement($n;"num")&gt;2

In order to account for this, you need to tell FileMaker to cast the result as a number with the GetAsNumber() function. The following would then evaluate to true:

1

GetAsNumber(JSONGetElement($n;"num"))&gt;2

Dates

Passing dates as text can be very tricky in FileMaker, JSON aside. Different locales use different date formats and when FileMaker converts a date to text it does so using the format in place when the file was created. This means that passing the date “11/12/2016” as text might work just fine for users in your locale, but as soon as the script is pasted into a file created in another country, the text could be written as “12/11/2016”.

For this reason, the best way to pass a date as a parameter would be to first convert it into a number. Here’s an example of how this can be done:

1

JSONSetElement("{}";"date";ExampleField::Date;JSONNumber)

Since the result when using JSONGetElement is always returned as text, you’ll also need to convert the parameter back to a number again before using the GetAsDate function:

1

Set Field[Table::Field;Value:GetAsDate(GetAsNumber(JSONGetElement($n;"date")))]

Conclusions

With all this said, do I think JSON is an effective method of passing and parsing variables between scripts in FileMaker?

Absolutely! While it’s not perfect, it would be great to see this become the new standard when developing FileMaker files.

The biggest reason I’d like to see this as the new standard is that, no matter how the JSON string is built, it will always be in a standard JSON format in the receiving script. This makes it really easy to debug, modify, and add on to existing scripts.

While FileMaker’s JSON implementation was originally intended for integrating with 3rd party APIs, it very well could become the new standard for passing multiple script parameters between FileMaker scripts.

Benefits

No matter how the JSON string parameter is constructed, it will always be in a standard JSON format in the receiving script.

JSON is hardened against content, so you don’t have to worry about including a carriage return in one of your parameters as you would if you were using returns to separate your parameters.

JSON can also take JSON objects AS parameters. so if you need to pass all your params to a different script (as you might when using PSOS) you can just include them in one new script parameter.

Disadvantages & Oddities

JSONGetElement always returns a text value for dates so you’ll wan to use the dates-to-numbers best practice mentioned above.

JSON functions are not supported in runtime solutions.

The JSONSetElement function can be a bit difficult to type outside of the calc dialog.

You either need to specify an data type or let FileMaker detect which data type you’re using. Letting JSON do it for you may give you some surprising results when mixing text and numbers.

You must remember that the first item in an array is 0, not 1 as in most FileMaker functions.

Passing an empty field as a JSONNumber will result in a JSONGetElement value of 0, not blank.

This blog was really a collaborative effort; I’d like to thank Jason Young and Todd Geist for providing great ideas and feedback

The new JSON function for “Setting” and “Getting” values from within a JSON object are very interesting.

1. Since, as you point out above, “there is no auto-complete for these values” I would suggest that many in the FileMaker community might want to have a suite of simple custom functions that wrap around the native JSONSetElement function, for example:

2. Likewise because, as you point out, the JSONGetElement function always returns text, we might want to have custom functions that both get the value and coerce it to the require data type, for example:

jsonGetElement_asDate

3. I like the 2nd option above: “2. Set each parameter needed to a local variable in its own JSONGetElement line at the top of your script.”

Over the years I have used a number of techniques for script parameters the #functions being the latest. Whilst JOSN is a wonderful thing , I wonder if there is really any advantage to using JSON. It would be interesting to assess the pros and cons of both techniques. I can see the advantage of JSON for nested information, but this is not the case with most script parameters . I really like being able to convert the script parameter to local variables with a single script step regardless of how many there are. I also appreciate referring directly to local variables in my scripts as I find this makes my scripts more readable and easier to understand.

A custom function to do this for JSON script parameters would be really useful – especially if it handled nested data.

It will be interesting to see whether JSON script parameters become more popular now that we have a choice…

I too was on the fence regarding whether it’s beneficial to use JSON for this. The thing that sells it for me is that I know that the parameters will always be in a standardized format in the receiving script.

Just a warning about long integers and long decimal numbers . If your input has more than 18 digits, you will get exponential presentation of the value:

Let ( [
json = JSONSetElement (“”; “Key”; 2436621663156788962; JSONNumber)
];
(JSONGetElement ( json ; “Key” ))
) ==>2.43662166315679e+18
( Btw, we here in Finland and many other European countries use “,”, not “.” as decimal separator. So we have to convert this to 2,43662166315679e+18… just like in ExecuteSQL)
Decimals are always rounded to 18 digits.
( btw this is a wrong result for country settings like here in Finland where “,” is used for decimal separator)

nice article, neatly explaining this useful new feature. JSON may be a good way to standardise multiple parameter passing in native FileMaker, but if it requires custom functions to make it work nice, then perhaps the advantages over using the #functions (or other methods) are not so great? In any case I’m looking forward to exploring the new possibilities FM16 has opened up, and articles like this can only help.

While custom functions aren’t necessary, they sure could make things easier. The real benefit, though, is that whether you use custom functions or not, the resulting string is always in the same standardized format.

I noticed a small discrepancy. The Age value in the sample file is being passed as a JSONString, but the article here on the web shows it passing it as a JSONNumber. Any significance to that? Seems like it should be a JSONNumber, but then I wonder, if no age is set (empty field), the final passed value will be “0”, as if the person is still an infant not yet one year old… 🚼

Also, I wonder if anyone has seen Jeremy Bante’s custom functions? I really like how dates are converted to ISO8601 format (added plus is web compatibility and readability) for the JSON object, then a CF is used to convert back to local FileMaker format. Although that approach makes declaring the variables (as text) in the script header less useful, since the CFs would need to reference the entire JSON object and not just a text variable.

That was actually an oversight. In the “Pass Age” button, it’s passed as a string, but the “Pass All Parameters” button passes it as a number, so you can see the difference between the two. Passed as a string, the result will be an empty string, but passed as a number, the result will be “0”.

Converting your dates to and from ISO8601 format is really helpful, and most of the time necessary, for interacting with 3rd party APIs. In the case of passing dates between FileMaker scripts, though, it’s probably a bit complicated, since FileMaker has its own formats that don’t adhere to the ISO standards.

In the section “The Catch with JSONListValues,” you discuss a drop down list and what the functions JSONListKeys and JSONListValues return. You conclude “there are more values than keys/names, so we can’t match up the key/name index with the value index.” However, there is a difference between a Filemaker list and a JSON array, which may be either named or unnamed. Giving the function JSONSetElement a filemaker drop down list as returns a sensible result but this is not a JSON array as you point out.

In order to create a JSON array using JSONSetElement, we need to add Animal[0], Animal[1], … Animal[n-1] for each value of the FileMaker list. An object created in this way can be passed to a Javascript system and be seen as a proper named array without issue.

A nested array is probably the best way to go in this case. I didn’t get into that in the article, as it dives a bit into more advanced JSON, but I’m sure this custom action can make it a lot easier for people to work with nested arrays inside JSON formatted parameters.

Fantastic article – I used the method mentioned (in Assign Local Variables) to create a dynamic script to send an SMS. I love how you can just parse in multiple parameters and have a script run without requiring context as the data is parsed in.

Need Help?

Download

DayBack's 30-day trial is unlocked so you can customize it and integrate it with your files.
Download DayBack and we'll send you a couple short emails with tips on how to modify it and use some of the coolest features.