Evolution of the Script Parameter Redux

For my previous blog post, I outlined the three different methods I have used over the years to pass multiple values in a single FileMaker script parameter. Since ‘Evolution of the Script Parameter’ was more on the technical side of FileMaker development, and since I like to mix things up, I expected this article to be less so. However, when I sat down to write it, I realized that I had more to say on my previous topic. With apologies to Francis Ford Coppola and Al Pacino, I feel a bit like Don Michael Corleone in The Godfather, Part III. “Just when I thought I was out… they (i.e. script parameters) pull me back in.”

Last month, I detailed a method for using the JSONSetElement ( ) function introduced in FileMaker 16 to pass multiple values in a single parameter as name/value pairs. Then, with a custom function I defined as fnExtractParameter ( json ; index ), I was able to extract all values and declare the corresponding local script variables using a single script step. If you have not read my previous blog post, you may want to do that before you read any further.

Once again, I thought I had arrived at the method for passing multiple values via a script parameter I would be using for the foreseeable future. Then, my co-worker Bobby Hritz offered a brilliant suggestion to eliminate a parameter from the custom function.

Naturally, the first parameter in the custom function was the JSON object containing the name / value pairs. The second parameter was what I called the ‘index’. The function relied on recursion to parse all of the name / value pairs, and I had to give the custom function its starting point. This ‘index’ was always set to ‘1’ in the initial function call. Then, each recursive call of the function would increment the ‘index’ while the JSON object was passed unchanged. This technique certainly worked well, but if the index was set improperly during that initial function call, the custom function would not yield the expected results.

Another function FileMaker added to our toolbox with FileMaker 16 is JSONDeleteElement ( ). In this use case, Bobby determined that we could use this function to delete the current JSON data element – the name / value pair we had just extracted into its corresponding variable name and value – from JSON object. Then, this smaller JSON object would become the parameter passed to the recursive call of the function. With this change, the custom function could always parse the first name / value pair without having to explicitly declare it with the second parameter. Our updated function fnExtractParameter ( json ) becomes:

Let ( [
   keyList = JSONListKeys ( json ; "" ) ;
   keyCount = ValueCount ( keyList ) ;
   key = GetValue ( keyList ; 1 ) ;
   value = JSONGetElement ( json ; key )
] ;

Evaluate ( "Let ( [ $" & key & " = " & Quote ( value ) & " ] ; 1 )" )
+
If ( keyCount > 1 ; fnExtractParameter ( JSONDeleteElement ( json ; key ) ) ; "" )

)

Now separate from the change detailed above, we began to notice some unexpected results when the values being passed to the script happened to be a date, time, or timestamp. While these values match up with field types in our FileMaker database tables, they are not Parameter Types which can be explicitly declared when defining our JSONSetElement ( ) function to include a date, time, or timestamp in the JSON Object passed to the script. Since FileMaker dates, times, and timestamps are stored as numbers, using “JSONNumber” as the parameter type in the function seemed like a natural choice. But then we were left wondering what does the number mean. Does it represent the number of days or the number of seconds?

To solve this conundrum, we decided to employ another custom function which would allow us to explicitly declare the data type. We also found it easier to handle the value as a string instead of as a number in the JSON Object. We convert the value to text using the GetAsText ( ) function and append a prefix to denote the data type. Our new custom function, which we call fnWrapDateTime ( value ; type ), looks like this:

Let ( [
   prefix = Case (
               type = "Date" ; "DT" ;
               type = "Time" ; "TM" ;
               type = "TM" ; "TM" ;
               type = "Timestamp" ; "TS" ;
               type = "TS" ; "TS" ;
              "DT"
            )
] ;

"<|" & prefix & "|>" & GetAsText ( value )

) 

As you can see, the custom function defaults to a data type of “Date”. Also, the special characters we include in the prefix should ensure the uniqueness of our prefixes since they should not occur naturally in a regular text string. They also help to highlight the appended text during any troubleshooting that becomes necessary. The script parameter calculation now looks like this, assuming we have a FileMaker file with a table called ‘Movies’ and several fields named ‘Title’ and ‘ReleaseDate’:

Let ( [
   json = JSONSetElement ( json ; “MovieTitle” ; Movies::Title ; JSONString ) ;
   json = JSONSetElement ( json ; “ReleaseDate" ; fnWrapDateTime ( Movies::ReleaseDate ; “Date” ) ; JSONString )
] ;

JSONFormatElements ( json )

)

The parameter received by the script looks like this:

{
   “MovieTitle” ; “Star Wars”,
   “ReleaseDate” ; “<|DT|>5/25/1977”
}

(Yes: I do know the official title is now “Star Wars: Episode IV – A New Hope”. If you’re a member of my generation, it will always simply be “Star Wars”. We know what we’re talking about, but I digress…)

So, what does this change do to the custom function that we defined for extracting each value and declaring the corresponding local variable? We add some logic to look for the prefix. Then, we use the Substitute ( ) function to remove the prefix and the appropriate GetAs… function to convert the resulting string back into the desired data type. The calculation for fnExtractParameter ( json ) now looks like this:

Let ( [
   keyList = JSONListKeys ( json ; "" ) ;
   keyCount = ValueCount ( keyList ) ;
   key = GetValue ( keyList ; 1 ) ;
   rawValue = JSONGetElement ( json ; key ) ;
   value = Case (
              Left ( rawValue ; 6 ) = "<|DT|>" ; GetAsDate ( Substitute ( rawValue ; "<|DT|>" ; "" ) ) ;
              Left ( rawValue ; 6 ) = "<|TM|>" ; GetAsTime ( Substitute ( rawValue ; "<|TM|>" ; "" ) ) ;
              Left ( rawValue ; 6 ) = "<|TS|>" ; GetAsTimestamp ( Substitute ( rawValue ; "<|TS|>" ; "" ) ) ;
              rawValue
            )
] ;

Evaluate ( "Let ( [ $" & key & " = " & Quote ( value ) & " ] ; 1 )" )
+
If ( keyCount > 1 ; fnExtractParameter ( JSONDeleteElement ( json ; key ) ) ; "" )

)

Once we reached this point, we thought we were done making changes for a while. Then, we realized that the same technique can be applied to data being returned to the parent script as a Script Result. To accomplish this, all we did is change the name of the custom function: fnExtractParameter ( json ) has become fnExtractVariable ( json ). With this minor tweak, we reduce any risk of confusion when the function is used to extract a script result instead of a script parameter. (In the end, I think it’s a better name since we are declaring local variables in our script. It gives a better picture of what the function is really going to do.)

We trust you will find the information presented here a helpful addition to the previous article. Have a great day.

Until next time,

TW and Bobby