Using JSON Objects in FM Script Parameters

James Pierson, 3/15/2018

 

What Is JSON?

 Here is what Wikipedia says:

In computing, JavaScript Object Notation or JSON is an open-standard file format that uses human-readable text to transmit data objects consisting of attribute–value pairs and array data types (or any other serializable value). It is a very common data format used for asynchronous browser–server communication, including as a replacement for XML in some AJAX-style systems.

 JSON is a language-independent data format. It was derived from JavaScript, but as of 2017 many programming languages include code to generate and parse JSON-format data. The official Internet media type for JSON is application/json. JSON filenames use the extension .json.

 Douglas Crockford originally specified the JSON format in the early 2000s; two competing standards, RFC 8259 and ECMA-404, defined it in 2017. The ECMA standard describes only the allowed syntax, whereas the RFC covers some security and interoperability considerations.

 From <https://en.wikipedia.org/wiki/JSON>

 I like to think of a single JSON Object with multiple Key/Value pairs as a single record in a database — or at least a few fields from a record. But it doesn’t have to be from the same record. You can include related data, or really anything you want. Including Arrays, and even other JSON Objects.

Working with JSON In FM 16

Prior to FileMaker 16 it was possible to work with JSON using Custom Functions and Plugins. For that matter you could hack together a JSON Object just using native text parsing techniques if you really wanted to. But starting with FM 16 we now have the ability to use native functions to work directly with JSON Objects.

There are 6 JSON Functions and they can be found grouped in with the Text Functions.

They can be easily found by typing “json” in the calculation dialog and FM’s type ahead feature will bring up the list. If you continue to type the first letter of the one you want it will bring you to just the individual function and you can simply hit enter to add to your calculation.

We will be focusing on just two of the JSON Functions.

  • “JSONSetElement” to create our JSON Object and
  • “JSONGetElement” to retrieve data from the Object.

 JSONSetElement has 4 parameters:

JSONSetElement ( json ; keyOrIndexOrPath ; value ; type )

 json: To create a New Object use “{}” empty curly braces. This is likely what you will be doing in a script parameter.

 keyOrIndexOrPath: For an Object the Key side of a Key: Value pair.

Key names for objects must be in quotes and are Case Sensitive. By Case Sensitive I mean if you name your Key “value1” and try to retrieve it as “Value1” it will fail.

Index would be the index number for an Array. 0,1,2,3 etc.

Path is notation used to allow you to drill into nested Objects and Arrays.

 value: This is the value you are storing for later retrieval.

For a string, the value must be in quotes. i.e. “Apple”. Numbers are not in quotes, i.e., 42. Booleans are not in quotes, i.e., lowercase true or false.

Can also be a field reference, Let, $Local, or $$Global variable. The value can also be another Object or Array.

 type: You must specify the type of data that will be stored in the Value side of the Key: Value pair. For script parameters you will mostly use JSONString or JSONNumber and possibly JSONBoolean. There is also JSONObject, JSONArray, JSONNull, and JSONRaw. To be honest I have not used JSONRaw, but you can find out more about that here:

https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help%2Fjsonsetelement.html%23

But you can also use JSONSetElement to add or change the values in an existing JSON Object. Instead of “{}” you would use the name of the Field or Variable that contains the object. If the Key name you specify already exists in the object you will change that value to the new value. If it does not exist it will be added as new Key: Value pair.

 JSONGetElement has 2 parameters:

JSONGetElement ( json ; keyOrIndexOrPath )

 json: A field or Variable that contains a JSON Object or Array.

 keyOrIndexOrPath: Same as outlined above. Don’t forget that Keys are Case Sensitive.

 Here is a simple example of JSON set and get that you can play with in a data viewer in FM Pro Advanced version 16 or later.

EXAMPLE 1:

Let(

$params =

 JSONSetElement ( “{}” ;

 [“value1” ; “Apple” ; JSONString ];

 [“value2” ; “Rock” ; JSONString ];

 [“value3” ; 42 ; JSONNumber ]

 )

;

JSONGetElement ( $params ; “value2” )

)

 

// Result = Rock

The Let is not required to set a JSON Object, but it made it easy to show both setting and getting in one calculation. It is often useful to use a Let to gather some complicated or conditional values into Let variables. Then you just use those in JSONSetElement for the values.

The square bracket syntax is used for multiple values similar to other FM functions such as Substitute ().

 EXAMPLE 2: You can also create Arrays.

You can get really deep with Arrays in Objects, Objects in Arrays, Objects in Objects, etc. Not using Arrays in any other examples but this is how you would do it.

Let(

$array =

JSONSetElement ( “[]” ;

 [0 ; “Apple” ; JSONString ];

 [1 ; “Rock” ; JSONString ];

 [2 ; 42 ; JSONNumber ]

 )

;

JSONGetElement ( $array ; 2 )

)

 

// Result is: 42

 Example 3: Create a JSON Object in a Script Parameter

A common FileMaker “New Feature Request” for many years has been a native way to pass multiple Script Parameters. With the new JSON Functions in FM 16 we now have that!

 Here is a JSONSetElement that might be found inside a Script Parameter Calculation Dialog.

 Create JSON Object in the Script Parameter:

Let([

~layout= Get(LayoutName);

~object = Get(ActiveLayoutObjectName);

~task = “Call SAO to find out ASA.”;

~run = Case(

 Cases::ASAknownYN = “No”; 1; 0 )

];

JSONSetElement( “{}”;

 [ “idCase” ; Cases::id ; JSONString ];

 [ “todoAction” ; “autoToDo” ; JSONString ];

 [ “user” ; “Legal Assistant” ; JSONString ];

 [ “task” ; ~task ; JSONString ];

 [ “objectName” ; ~object ; JSONString ];

 [ “layoutName” ; ~layout ; JSONString ];

 [ “interval” ; 0 ; JSONNumber ];

 [ “run” ; ~run ; JSONNumber ]

)

)

 In the above example we are creating a JSON Object inside the OnObjectSave Trigger on a simple Yes/No Radio button. We need to create a task if the answer is No. That is the reason for the “run” Key. The script will look at that and if it sees a value of 1 it will continue otherwise it will exit. We also did an On Object Modify that just does a commit so that our Script Parameter can get the New value of the Radio Button. Would work with just the On Object Save but doing the commit first triggers the Save right away rather than wait for the user to try to do something else then interrupt them with the script.

I always include an “action” Key. I’m sure everyone has done the If Get (ScriptParameter) = “whatever” thing. That is the purpose of “action,” it tells the script which branch to run. “idCase,” “objectName,” and “layoutName” get stored in the task record for later reference. This allows us to provide an easy way for the user to get back to the Layout, Record and Field in question from the ToDo record for further data entry.

 In the script near the top I do a Set Variable [$params; Value: Get(ScriptParameter)]. Now I have access to all of that data that I bundled up into a JSON Object.

Example 4: Using the JSON Object in an IF to see if we are running the script or not:

//IF the following is true run or else we will exit

JSONGetElement ( $params ; “todoAction” ) = “autoToDo” and

JSONGetElement ( $params ; “run” ) = 1

If we are running the script, we open an off-screen window and go to the ToDos layout then create a new record.

We then proceed to pluck the values we need from $params one by one as we set the fields in the new ToDo record.

Example 5: Grab the Foreign Key and Task Description:

JSONGetElement ( $params ; “idCase” )

JSONGetElement ( $params ; “task” )

Rinse and Repeat.

Adding a Value To An Existing JSON Object

Another handy thing you can easily do is to pop another value onto an existing JSON Object.

Let’s say you need to take the user to one or more pick lists, let them choose, and then continue to the final layout. One technique for doing this that I have always disliked was the Pause and Resume script technique. But sometimes it was a necessary evil.

Now you can take your $params and make it $$params instead. Go to your Pick list and exit the script. When the user chooses, a different branch of the same script will continue the process. And we take the id (or whatever we need) from the record the user has chosen

and pop it onto our $$params Object. We can continue on to multiple pick lists as needed.

Example 6: Append a Value to an Existing Object:

JSONSetElement ( $$params ; “idContact” ; Contacts::id ; JSONString )

Assuming the “idContact” Key doesn’t exist, it will be added to the Object. If it does exist it will be updated with the new value which also might be useful. I’ll say it one more time. The Key IS Case Sensitive. So many things in FM are not but this is, and it will bite you if you’re not paying attention.

Pros and Cons of Using This Technique

 Pros

  1. Script is more or less portable. Anything Context Specific is stored in the Script Parameter of the Trigger or Button Object.
  2. You should be able to copy the Button or field to another location and not have to alter the Script. You would just modify the Script Parameter to the new context and possibly grab different values etc. Your script can have every set field it might need for any situation. It won’t hurt anything if a value doesn’t exist in $params. It will just set the field to nothing, which is fine.
  3. The values are in no particular order, you just ask for them by name. Keep in mind, that name is case sensitive.

       Cons

  1. I have only encountered one potential con. It’s really more of a Gotcha. I use this technique to generate some automatic email messages. Another developer created an email logging table and script. I thought I was going to just call his script and use my still live JSON Object to feed into that script. That script is looking for JSON, but he used different naming conventions so my JSON Keys are not the same as his. So I either need to change my names or convince him to change his. Or do what I actually did which is to script my own log entry, so I can use my existing Key names. The moral of that story is if 2 or more developers are using JSON in Script Parameters they should agree on a standard for commonly used Key names.