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.

(If you’re just looking for the example file, here it is: JSONScriptParameters.fmp12)

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.

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.
  • Or use a custom function, such as fmpstandards’ #Name-Value.

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:

JSONSetElement ( "{}";
 ["Animal"; SomeTable::Animal ; JSONString];
 ["Age"; SomeTable::Age ; JSONNumber];
 ["Vaccinated"; SomeTable::Vaccinated ; JSONBoolean]
)

That would create a string which looks like this:

{"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:

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:

FileMaker 16 JSON Script Parameters

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):

JSONString

The value included in the JSON is always included in quotes, even if you passed in number. This is the same as applying FileMaker’s GetAsText() function to the value before passing it in.

Example input: “text1”
Added to the JSON as: “text1”
JSONGetElement result: “text1”

FileMaker 16 JSONString

Passing a simple string

Notice how returns are encoded with \r and \n but decoded in JSONGetElement and (unfortunately) in JSONListValues

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.

FileMaker 16 JSONNumber

Simple number parsing

FileMaker 16 JSONNumber Script Parameters

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

JSONNumber Function

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

FMP 16 JSON Script Parameters

JSONNumber turns blank values in to zero

JSONObject

Nest one JSON object as a member in another object.

Example input: “{ \”a\” : 11 }”
Added to the JSON as: “{“a”:11}”
JSONGetElement result: “{“a”:11}”

FileMaker 16 JSONObject Script Parameters

A JSON object can, itself, be a value in a JSON object

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.

FileMaker 16 JSONArray Script Parameters

Addressing the second position in an array by nesting JSONGetElement

Retrieving the second position of the array directly.

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:

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

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

FileMaker 16 JSONBoolean Script Parameters

A simple boolean

FM 16 JSONBoolean

Note that “True” in quotes is case sensitive and false in this case

FileMaker 16 JSON Boolean

True is true in this case

JSONNull

This creates a named, empty member, regardless of what you pass in.

Example input: “text1”
Added to the JSON as: “”
JSONGetElement result: “”

FileMaker 16 JSONNull Script Parameters

There is nothing to see here

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.

Example input: “text1”
Added to the JSON as: “text1”
JSONGetElement result: “text1”

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

FileMaker 16 JSON Null

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:

FileMaker 16 JSON Parsing Example

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:

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:

FileMaker 16 JSON Script Parameters Example

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.

Here’s how we accomplish this in our demo file (see lines 14-17):

JSON Script Parameter Escape Returns

Then, when retrieving your value, you can substitute back in the original characters with:
Substitute( Substitute ( GetValue ( $values ; $i ) ; $char13replacement ; Char(13) ) ; $char10replacement ; Char(10) )

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 always returns text, 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 the result will always be a text value. Let’s say you’ve created the following JSON parameter:

$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:

JSONGetElement ( $n ; "num" ) > 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:

GetAsNumber( JSONGetElement ( $n ; "num" ) ) > 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:

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:

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. This can be frustrating if you’ve passed in a number value, specified the type JSONNumber, yet still have to use the GetAsNumber() function to retrieve the value as a number.
  • 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

Tagged with →  

23 Responses to FileMaker 16 JSON Script Parameters

  1. john renfrew says:

    Great article, thanks for the work to get in first and set the bar…

  2. Tony White says:

    Nice blog post! Some thoughts…

    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:

    jsonSetElement_string
    jsonSetElement_number
    jsonSetElement_object
    jsonSetElement_array
    jsonSetElement_boolean
    jsonSetElement_null
    jsonSetElement_raw

    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.”

    Thanks.

    • KC Embrey says:

      Hi Tony,

      Custom functions could definitely be a help for those with FileMaker Pro Advanced. They could surely prevent a lot of duplicate code when getting variables in a required data type.

      I tried out a few different ideas for getting the variables, and tend to like the 2nd option too. It makes it very clear which parameters the script is looking for.

      Regards,
      KC

  3. Paul Jansen says:

    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…

    • KC Embrey says:

      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.

  4. Jordan Watson says:

    Apologies if this is just the standard in JSON and I’m pointing out the obvious but the JSONGetElement function appears to be Case Sensitive which had me stuck for an hour :(

  5. Martti Tumanto says:

    Thank you for your clear blog post.

    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)

    So, it is always better to set elements to JSONString and convert it in FileMaker :
    Let ( [
    json = JSONSetElement (“”; “Key”; 2436621663156788962; JSONString)
    ];
    getasnumber( (JSONGetElement ( json ; “Key” )))
    ) ==> 2436621663156788962

  6. Benedick Miller says:

    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.

    • KC Embrey says:

      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.

  7. Paul Jansen says:

    Great article; useful information AND thought provoking.

    Short take; JSON is Huge!

    I am considering having 2 parts to the JSON parameter; one for the control parameters and one for the data. The same approach could be taken with script results as well.

    I like the robustness of the date as number approach but don’t like the fact I cant “read” it. I guess it’s better to be safe.

    I also think I prefer to pass a multi-value field or list as an array.

    “tags” : [ “VIP”, “Active”, “Invoice Contact” ]

    The list can easily be recreated using

    JSONListValues( JSONGetElement ( $json ; “data.tags” ) ; “” )

    Anyway, I agree it will be interesting to see how JSON script parameter use evolves and if any consensus can be reached.

    Sample script parameter:
    {
    “data” :
    {
    “dateLastContact” : 736423,
    “email” : “j.smith@newco.com”,
    “id” : “BBDB49A2-5F42-4A63-878E-392DE4CF0894”,
    “idCompany” : “FB44CF93-39FA-40B0-97E3-A9559C29CEC5”,
    “name” : “John Smith”,
    “tags” : [ “VIP”, “Active”, “Invoice Contact” ]
    },
    “scriptParam” :
    {
    “action” : “create”,
    “ask” : true,
    “layout” : “Contacts”
    “table” : “People”
    }
    }

    • KC Embrey says:

      Thanks, Paul! I was staying away from nested arrays in the article, as that gets a little more into advanced JSON. However, that is likely the better way to go with list values.

  8. I’m moving to this standard for passing parameters.
    It is really simple and easy to understand.
    Thanks for sharing…

  9. Matt Larson says:

    KC, nice article.

    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.

    • KC Embrey says:

      Thanks for pointing that out, Matt.

      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.

  10. Alejandro says:

    Thanks for taking the time to write this post!
    It is super clear and easy to understand!

  11. Michael Twisdale says:

    Hey KC, nice article.

    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.

    To create a named or unnamed array with an arbitrary number of values, I wrote and am sharing a fast, non-recursive, custom function named FMListToJSONArray. You can find open source code for it on
    1) GitHub – https://github.com/twisdale/FMListToJSONArray
    2) Briandunning – http://www.briandunning.com search for FMListToJSONArray
    3) Filemaker – https://community.filemaker.com search for FMListToJSONArray

    Github has complete readme documentation.

    I hope you and your readers will find it useful in passing parameters either within Filemaker or as proper JSON arrays to Javascript.

    • KC Embrey says:

      Thanks Michael!

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Share →
DOWNLOAD
DayBack Calendar
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.
Thank you! Please download: DayBack Calendar
Need More?
SeedCode tips & example files in your inbox
Need More?
SeedCode tips & example files in your inbox
Want More?
Be the first to see articles and tips like these
DOWNLOAD
TimeZync
Download TimeZync and we'll send you a couple short emails with tips syncing your FileMaker Go files.
Thank you! Please download: TimeZync
Want More?
Be the first to see articles and tips like these