When FileMaker released version 16 earlier this year, one of the most anticipated new features was native functions for building and parsing JSON data. Before that, we could deal with JSON using the free Base Elements (BE) plug-in, or using a set of custom functions developed by Todd Geist or Daniel Smith. These work-arounds served us well, but you can’t use the plug-in functions in FileMaker Go or WebDirect. The custom functions either relied on the BE plug-in or used recursion, and hence were slower than native functions. Plus the biggest issue was that without native functions, widespread adoption and standardization was not going to happen throughout the FileMaker community.
This technical guide will help FileMaker developers that are at an intermediate to advanced level.
Wait, what is JSON?
Glad you asked! JSON is short for JavaScript Object Notation. JSON is a data interchange format like XML. It’s a way to pass data from one place to another in a way that is predictable and universal across multiple programming languages and platforms. Best of all, it can be formatted in a way that is easy to parse—not only by computers, but also by humans. JSON has become the de facto standard for passing data to, and getting data out of, most web services.
Why should FileMaker developers care?
Another great question! Increasingly, FileMaker is not an island. At LuminFire, we believe in using the best tool for the job. And quite often that means using a specialized web service dedicated to that task. Most web services that offer a REST API will accept and return data in JSON. So if you are using the new FileMaker cURL functions with Insert from URL, you would be wise to learn how to create and parse JSON data. Even if you aren’t interacting with REST APIs, using JSON is a great way to pass complex data (read: multiple parameters) to your FileMaker scripts.
But I can already do that with List ( ), a combination of Evaluate ( ) and Let ( ), or the # ( ) custom functions.
Yes, passing multiple parameters to FileMaker scripts is not new. Although there is no native way to actually pass “multiple” parameters (FileMaker script parameters are just passed as one block of text), there are several ways to skin the poor, proverbial cat.
One of the most common methods is to simply pass a return-delimited list of items as your parameter. This can be done with the List ( ) function or manually build it with pilcrow (¶) characters between each item. Then in the receiving script, parse them out with the GetValue ( ) function. With List ( ), you have to be aware that blank values will be skipped. This can cause problems, because this method relies on knowing exactly which line/value each item will be found. And what if you are passing a list of related values, and you don’t know exactly how many there will be. If you have only one item like this, you can put it at the end of your list of values, but what if you have more than one item like this? You can use Substitute ( ) (or a custom function) to replace the intra-item returns with some other character or string, then swap them back on the other end. If you are good with your PatternCount ( ) abilities, you’ll have noticed that there are a lot of work-arounds to this work-around.
Enter name-value pairs. Rather than relying on a parameter/value being located at a particular place in the list of values that we are passing, what if instead, each item could be called by name? The “native” way of passing multiple parameters, as mentioned in the FileMaker documentation is to use the Evaluate ( ) and Let ( ) functions. But this method is both fragile for complex strings and cumbersome to implement, so it is not often used. The # ( ) family of custom functions over at FileMaker Standards makes this rather simple. You simply string together as many name-value pairs using # ( ), then in the receiving script, call #Assign ( Get ( ScriptParameter ) ), and all of your values will be assigned to their own variable, with the name as the variable $name. Neat! You can even nest values in a hierarchy of names… which starts to sound a lot like JSON. So why don’t we all just standardize on this? It’s on the “standards” website, after all. The “standards” are not official, and they are constantly evolving, as FileMaker evolves. The biggest hurdle is that these are custom functions, and as we mentioned earlier, this hinders adoption. Custom Functions can only be added to a file or edited with FileMaker Pro Advanced. And some users just don’t like adding custom functions to their apps. There is also the issue that you can’t readily see what variables your script is expecting. This can be overcome with good documentation/comments in your code.
So now we have established that name-value pairs are a good thing, that wide-scale adoption will only happen with native functions, and it doesn’t hurt to use a data interchange format that is the same standard as most of the web. And now in FileMaker 16 we have that.
FileMaker 16 JSON functions
JSONSetElement ( json ; keyOrIndexOrPath ; value ; type )
This is the main function that you will use to “set” JSON, meaning to either create a new JSON object or to modify or add to an existing JSON object. It has 4 parameters:
json: Use "{}" to start a new JSON object. If you are modifying or adding to an existing JSON object, call out the field or variable where the JSON is currently stored.
keyOrIndexOrPath: That’s a mouthful! Most often, this will simply be the “key” or “name” in your name-value pair. If you have a nested hierarchy of keys, that is known as a “path” and is specified using “dot notation” or periods between each level of the hierarchy, like this: LevelOne.LevelTwo.LevelThree. The other option is “index,” where there can be multiple, ordered items that can be called by their index number. Note that JSON indexes are zero-based. More on that later.
value: You guessed it: this is the “value” in your name-value pair. This is the typically the piece (or one of many pieces) of data that we are trying to get from one point to another.
type: JSON supports 7 data types. These are not necessarily the same as FileMaker data types. See the section below for more details.
One call of the JSONSetElement can set any number of elements in one call. Simply place the last three parameters in square brackets, and place a semicolon between each set, much like you can do with the Substitute function.
JSONFormatElements ( json )
This function takes a JSON object and makes it pretty, and more readable by humans. It does not have any effect on the values themselves. The function can take a long string of keys and values from:
{"Address":{"City":"Anytown","State":"MN","Street":"123 Main St.","Zip":"55123"}}
…to something something formatted with indenting and white space like this:
Wrapping your JSON in JSONFormatElements can make checking your code much easier.
JSONDeleteElement ( json ; keyOrIndexOrPath )
As the name ageplies, this function is used to delete an element from a JSON object. Just supply or reference the JSON object in the first parameter and the key, index, or path that you want to delete in the second parameter. The result will be a JSON object with that element removed.
JSONListKeys ( json ; keyOrIndexOrPath )
This function is useful for listing or counting the keys in a JSON object. Passing an empty string "" in the second parameter will return all of the keys in the object. Alternately, you can pass a portion of a path to return all the keys at that level of the hierarchy. The result is a return-delimited list.
JSONListValues ( json ; keyOrIndexOrPath )
The counterpart to JSONListKeys, returning the values instead of the keys. Passing an empty string "" in the second parameter will return all of the values in the object. Alternately, you can pass a portion of a path to return all the values at that level of the hierarchy. The result is a return-delimited list.
JSONGetElement ( json ; keyOrIndexOrPath )
This is the function you will use most often to extract a specific value from a JSON object. You can also return any portion of the JSON by supplying a specific point in a path. This can be useful when you are looping over items in an array within the JSON.
JSON data types.
As mentioned above, the last parameter in the JSONSetElement function is type and there are 7 options. You can give this parameter an empty string "", but we would not recommend it. The data types are passed as plain, unquoted text. They also have corresponding number values, but using the text will keep things readable. Just for reference, we’ll list the number equivalent after the text.
- JSONRaw (0) – This is the same as passing an empty string. With this option, FileMaker will take a guess at what the data type should be. Let’s just say that FileMaker occasionally makes some questionable assumptions, so you would be better off making this determination.
- JSONString (1) – A text string that will be returned in quotes. Note that this can be an empty string.
- JSONNumber (2) – This is the equivalent of wrapping your value in the GetAsNumber ( ) function.
- JSONObject (3) – Yes, you nest one JSON object inside another.
- JSONArray (4) – You can use this option to list an array.
- JSONBoolean (5) – This is similar to wrapping your value in the GetAsBoolean ( ) function, but rather than returning 1 or 0, it returns either
trueorfalse(note that is returned without quotes, so it is the boolean value, not a text string). - JSONNull (6) – This will pass an empty value, no matter what you specify for the value. This will show up in your JSON as unquoted
null, but will be returned the same as an empty string to FileMaker.
Gotchas when using the FileMaker JSON functions.
Because of the first two letters of JSON, some things go by the JavaScript rules and may not behave quite like we are used to in FileMaker land. Here are a few things to look out for…
- Unlike FileMaker $variable and field names, JSON key/path names are case sensitive. So pick a naming convention and stick to it. At LuminFire, we prefer PascalCase.
- JSON keys are always returned in alphabetical order, as you can see in the example above with the address data. In the JSONSetElement function, we set Address.Street before Address.City, but the order in which you set elements doesn’t impact the order that they appear in the resulting JSON object. If you need to rely on something being in a specific order, use an array in your JSON, so you can set and control the order. Note that the order does matter, if you are setting 2 different values to the same key or path. In that case, the last value will override any previous values set.
- Make sure you are on the latest version. In FileMaker 16.0.1, all JSON functions returned text, even if they were set as JSONNumber or JSONBoolean. The 16.0.2 update fixed that, so those data types will actually return a number now.
- JSON doesn’t do well with very large numbers. Decimals are always rounded to 18 digits. Here we show what is returned when you pass in a 19-digit number:
As you can see, the result is returned in scientific notation as 1.23456789012346e+18, and if we wrap that in GetAsNumber ( ), we see that it has been rounded to1234567890123460000. If you need your large numbers to retain their precision, for example if you use a numeric UUID format, force them to be text by using JSONString. - JSON arrays are zero-based, meaning that if you have 3 items in your array, they will be numbered: 0, 1, 2. This is important to remember because in FileMaker, we are very accustomed to looping through records or over a list of values, by starting a counter $i at 1 and having our loop count match the number of items that we are dealing with. For example, you might have a step that says
Exit Loop If [ $i = $ValueCount ]. But if you are using that $i variable to get the item from the array, you will be off by one. So you either have to start your loop counter at 0, and know that your counter will only get to to the total count – 1, or adjust your count in each call to set or get your values in the JSON function calls. It’s a preference, but most will find it easier to keep the $i in sync with the count of loops, and adjust for it in the JSON calls.
The LuminFire JSON Script Passing Standard
After much heated, internal debate, this is the standard that we have settled on for script parameter passing in any new FileMaker scripts. If you have a well established system, you will have to determine if it is worth it to update existing scripts. Because you may have to make updates to several buttons and parent script calls, it may not be worth the time and the risk of breaking something, unless you are adding parameters or otherwise refactoring the script. There are many standards out there… this one just happens to be ours. Like any “standard” you might find, it is still evolving and subject to change.
- Purpose: At the top of every script, explain what the script’s purpose is and any other important notes.
- Script history: Add a new line every time changes are made, with the date, who made the changes, and a brief description of the changes. This is very important when you need to assign blame, later on. 😉
- Example parameters: An example of the parameters that the script expects to receive. Not only is this useful because it is one place to document all of the parameters that are used throughout the script, it also gives the developer a handy place to copy the the example parameter and paste into your the Specify Script dialog. A quick detour into Edit Script lets you double-click into the example parameter, copy it, and paste it into the parameter field. In the past, we have put the example parameter text in a plain script comment line. Using a commented-out Insert Text step has some advantages:
- It only takes up one line in your script. If you have a long or complex list of JSON parameters, putting it all in a comment can take up a lot of vertical space.
- The grayed-out line makes it an easy target when you are opening the script, looking for the sample parameter text to copy.
- Freeform text entry: if you are typing your example into a comment line, tab or return will “commit” that line, taking you out of text entry. To put a tab or return into your example, you have to type option-tab or option-return (option on a Mac, alt on Windows). The text field within this step lets you enter tabs and returns without any modifiers. Enter will close the dialog.
A double-click on the Insert Text step opens the Specify dialog with the full text selected, ready to copy. Note that there is no need to specify a target field or variable when using the step this way.
- Save JSON to a local variable: We do this simply for readability. Because $Params is easier to read than Get ( ScriptParameter ), especially when it is called repeatedly. Setting it into a local variable means that it will show up in the Data Viewer, so at any point when you are debugging the script, you have easy access to the entire set of parameters. Wrapping it in JSONFormatElements makes it easier to read as well.
- Check for valid JSON syntax: Check for problems right away. If the first character is a question mark, we know that something was malformed in the JSON. We throw an error dialog and exit the script, so we don’t do any harm to the data. Note that the end user should never see this error dialog, as they probably won’t know what it means, and they won’t be able to do anything about it. This is only meant for the developer, during testing.
- Declare local variables: This section is optional (and the source of much debate). Alternately, you could pull everything out of $Params on an as-needed basis and avoid some redundancy and extra variables in your data viewer. But ultimately, we decided that we liked seeing specific parameters in the data viewer for quick spot checks, without having to click into the main $Params variable and scan through the JSON. For arrays, it wouldn’t be practical to break out every value to a separate local variable.
- Looping through arrays: Here we show an example of looping through an array; in this case to set multiple fields. Note that our $i counter matches the number of the loop we are on, and we exit the loop when we get to the count of items, that we get by counting the values returned by JSONListValues for our FieldName array. But when we set the field by name with the value, we adjust our $i counter by – 1 to match the zero-based array index number.
After that, everything else in your script can be business as usual. If you need to pass parameters on to a subscript, you can either create a new JSON object for that parameter, or modify/add to the existing one by specifying $Params as the first parameter of the JSONSetElement command. This script can be found in the sample file below.
In addition to passing parameters to a script as JSON, we are also starting to get script results from our scripts as JSON. At a minimum, we are returning the script name, an error code, and a message. But since we are using JSON, it is easy to add additional data as needed. Just as important as making sure that your scripts work, is making sure that when they do fail, they fail properly, and we handle it gracefully. JSON is also opening up opportunities for other things like automated testing, as demonstrated in this video by Geist Interactive.
Extra Tips
This post is already getting long, so for the details on how some of these work, download the example file, and check out the custom functions.
Dates and timestamps can always be a little fussy, due to the differences in localized formats. If you are getting and setting a date in the same app, on the same computer, in FileMaker, you are probably fine passing a date as text. But if you are sharing data with other users or other systems, we find it best to convert your date to a known standard. Most web APIs will use the ISO 8601 standard for dates and times. The sample file includes custom functions to convert to and from this standard.
Container field data cannot be passed directly in a JSON object. If you reference a container field with the type JSONString, the result will be the text of the file name that is stored in the container. To move the actual container data, we first need to convert the binary data to text using the Base64Encode function. The sample file includes custom functions to make this easier.
References and Links
There has already been a great deal of content written about the new JSON functions in FileMaker 16 by other members of the community. We are grateful for the contributions that everyone has made. This article, the demo file, our standards, and our knowledge has been influenced by and consolidated from the following (and probably others we are forgetting):
Working With the JSON Functions – FileMaker help
FileMaker 16 JSON Script Parameters – SeedCode
Creating JSON in FileMaker 16 – Soliant Consulting
FileMaker JSON Functions – DB Services
FileMaker JSON Type Handling – Jeremy Bante
JSON + Virtual List – FileMaker Hacks
Many things by Todd Geist, Geist Interactive
FileMaker 16: New JSON Functions – Skeleton Key
FileMaker 16: Passing Multiple Parameters Natively Using JSON – AppWorks
JSON Transactional Edits in FileMaker 16 – The Scarpetta Group