Wednesday 3 November 2021

A more modern method of handling parameters in FileMaker scripting

 Back in 2014 I wrote a blog post about working with complex script parameters in FileMaker, here.  I've been meaning to modernise it for quite a while, and I finally got around to writing this essay.  If you are not familiar with that previous article, I suggest going back and reading it, this essay will make a lot more sense if you do.

The basic problem that is solved here is how to pass multiple complex pieces of data in a single parameter to a script in FileMaker.  Where you want to supply multiple pieces of data, you have to structure it in some way.  Most beginner-to-intermediate developers start on this by using multiple return-delimited values as their first solution to this problem, but that has limitations that can be difficult to work around, so a more capable method of structuring the data is desirable.  (e.g. what happens if your data contains a return character?  what happens if you load the values in the wrong order, or a value is missing?)

Previously I solved this by using "Property List" custom functions written by Shawn Flisakowski here, extended by me to handle things like reserved characters, and to turn each property in the parameter into a script local variable with a single custom function call at the beginning of the script.

Since FileMaker 16 introduced JSON, it's been possible to improve this mechanism.  JSON handles things like embedded return characters and other reserved characters, so it's potentially simpler to implement.  The catch has been how to implement the change without breaking any existing functionality.  The principle database solution that I work on has over 90 files and many thousands of script calls, there was no way to edit every single place where a script is called with a parameter.

Given that I don't want to break existing functionality in an already existing solution, the very first thing I need is a custom function to determine if the parameter is valid JSON or not.  If it is not valid JSON, call the old parameter processing functions, if it is valid JSON use the new functions that I'll show below.

isValidJSON ( json )

/* source is https://github.com/geistinteractive/fm-json-additions/blob/master/functions/JSON.IsValid.fmfn */

/*

* Tests to see if the JSON object is valid

* @param {object} json the JSON object to to test

* @module fm-json-additions

* @see https://github.com/geistinteractive/fm-json-additions

*

* @history 2017–11-29 updated doc block for clarity, dave@geistinteractive.com

* @history 2017–11-23 created, todd@geistinteractive.com

*

*/

Left ( JSONGetElement ( json ; "doesnotmatterwhatishere" ) ; 3 ) <> "? *"


So now we know if the parameter is structured JSON or not.  The next thing needed is a function that takes a JSON object and makes a script local variable out of it.

JSON_ObjectToLocalVariable ( aJSONobject )

/*

#==============================================

#.  Function:           JSONPropertyToLocalVariable

#.  Parameters:       a json Property

#.  Notes:              assumes that aJSONobject is a valid single JSON Object

#.  Author:             Peter Gort

#.  Version:            1.0

#.  Created:            Sunday, 5 August 2018 at 11:24:29 am

#.  Modified:           

#.  Modified by:        

#==============================================

*/


Let([

  propertyName = GetValue(JSONListKeys ( aJSONobject ; "" ) ; 1 ) ;

  propertyValue = JSONGetElement ( aJSONobject ; propertyName );

  statementToEvaluate = "let ( $" & propertyName & " = " & Quote (propertyValue) & " ; get(lasterror) )"

];

  Evaluate ( statementToEvaluate )

)


So now we can convert a single JSON object into a script local variable.  The next thing we need is a custom function that receives any number of valid JSON objects, and loops through them calling JSON_ObjectToLocalVariable() on each object.

JSON_ObjectsToLocalVariables ( theListOfJSONObjects )

/*

#==============================================

#.  Function:           JSONObjectsToLocalVariables

#.  Parameters:      theListOfJSONObjects

#.  Notes:              assumes that theListOfJSONObjects is a valid JSON Object containing zero or more valid JSON Objects

#.  Author:             Peter Gort

#.  Version:            1.0

#.  Created:            Sunday, 5 August 2018 at 11:26:12 am

#.  Modified:           

#.  Modified by:        

#==============================================

*/

If(

  IsEmpty( theListOfJSONObjects )

;

  1

;

  Let([

    JSONObjectNames = JSONListKeys ( theListOfJSONObjects ; "" ) ;

    n = ValueCount ( JSONObjectNames );

    theFirstJSONObjectName = If ( n = 0 ; "" ; GetValue ( JSONObjectNames ; 1 ) ) ;

    theFirstJSONObject = If ( n = 0 ; "" ; JSONSetElement ( "" ; theFirstJSONObjectName ; JSONGetElement ( theListOfJSONObjects ; theFirstJSONObjectName ) ; JSONString ) );

    theRemainingJSONObjects = If ( n ≤ 1 ; "" ; JSONDeleteElement ( theListOfJSONObjects ; theFirstJSONObjectName ) )

  ];

    Case(

      n = 0 ; 1 ;

      n = 1 ; JSON_ObjectToLocalVariable ( theFirstJSONObject ) ;

      n > 1 ;  JSON_ObjectToLocalVariable ( theFirstJSONObject ) & JSON_ObjectsToLocalVariables ( theRemainingJSONObjects ) ;

    ) // case

  ) // let

)  // if


Note that this does NOT handle JSON Arrays of objects, though it does handle a JSON Object whose value is an array.  Also, the value of a JSON Object can itself be another JSON Object.... I'm not *trying* to confuse the reader, honest!  

We now have to modify the old custom functions from 2014 to detect if the parameter is valid JSON and call the new functions if so, and call the old functions if not.  Beginning with PropertyListToLocalVariables().

PropertyListToLocalVariables ( propertyList )

If(

    isValidJSON ( propertyList )

;

   JSON_ObjectsToLocalVariables ( propertyList )

;

    If ( 

      not ( IsEmpty ( propertyList ) ) 

  ;

      Let([

               countProperties = ValueCount ( propertyList )

           ];

              If ( 

                   countProperties > 1 

                 ; 

                   PropertyToLocalVariable ( cleanAndPlainText ( GetValue( propertyList ; 1 ) ) ) & 

                   PropertyListToLocalVariables ( MiddleValues ( propertyList ; 2 ; countProperties-1 ))

                 ; 

                   PropertyToLocalVariable ( cleanAndPlainText ( GetValue( propertyList ; 1 ) ) ) 

                 )  // end if

              ) // end let

  ; 

      "0"

  ) // end if

) // end if


As you can see, if the incoming parameter is a valid JSON object, the new JSON_ObjectsToLocalVariables() function gets called, otherwise the old functionality is called.

A similar modification has to be made to PropertyToLocalVariable()

PropertyToLocalVariable ( property )

//used for converting script parameters into local variables

If(

  isValidJSON ( property )

;

  JSON_ObjectToLocalVariable ( property )

;

  If ( 

      not ( IsEmpty ( property ) ) 

  ;

      Let([

          propWithLeadingDollar = If ( Left ( property ; 1 ) = "$" ; property ; "$" & property );

          pos = Position ( propWithLeadingDollar ; "=" ; 1 ; 1 );

          leftbit = Left ( propWithLeadingDollar ; pos - 1 );

          rightbit = Right ( propWithLeadingDollar ; Length(propWithLeadingDollar) - pos );

          propWithQuotes = leftbit & "=\"" & rightbit & "\"";

          propDecoded = DecodeFromProperty ( propWithQuotes );

          propWrappedInLetStatement = "let(" & propDecoded & ";\"\")";

          propInstantiated = Evaluate (propWrappedInLetStatement)

      ];

          Get ( LastError )

      )

  ; 

      "0"

  ) // end if

) // end if


OK that's the "receiving parameter" mechanism updated to use JSON where it appears and do things the old way if it's not valid JSON.  We haven't broken anything.   Now we have to modify the "construction" side of things.  First creation:

JSON_AddObject ( JSONData ; objectName ; objectValue )

/*

#==============================================

#.  Function:           JSON_AddObject

#.  Parameters:         

#.  Notes:              just a helper function that packages the built in JSON function, for replacing the old AddProperty() custom function

#.  Author:             Peter Gort

#.  Version:            1.0

#.  Created:            Sunday, 5 August 2018 at 12:35:18 pm

#.  Modified:           

#.  Modified by:        

#==============================================

*/

JSONSetElement ( JSONData ; objectName ; objectValue ; JSONString )


Now we modify AddProperty() to call the new function

AddProperty ( propertyList ; propertyName ; propertyValue )

If ( 

      IsEmpty ( propertyName )

    ; 

      propertyList

    ;

      JSON_AddObject ( propertyList ; propertyName ; propertyValue )  

)

OK, only a couple more things to do.  Modify Property() now calls an analogous JSON function

JSON_ModifyObject ( JSONData ; objectName ; newObjectValue )

/*

#==============================================

#.  Function:           JSON_AddObject

#.  Parameters:         

#.  Notes:              just a helper function that packages the built in JSON function, for replacing the old ModifyProperty() custom function

#.  Author:             Peter Gort

#.  Version:            1.0

#.  Created:            Sunday, 5 August 2018 at 12:35:18 pm

#.  Modified:           

#.  Modified by:        

#==============================================

*/

JSONSetElement ( JSONData ; objectName ; newObjectValue ; JSONString )


and the old ModifyProperty() function gets modified to call this worker

ModifyProperty ( PropertyList ; PropertyName ; PropertyValue )

if(

  isValidJSON ( PropertyList )

;

  JSON_ModifyObject( PropertyList ; PropertyName ; PropertyValue )

;

  AddProperty( RemoveProperty( propertyList; propertyName ); propertyName; propertyValue )

)


the old RemoveProperty() function now calls an analogous JSON function

RemoveProperty ( propertyList ; propertyName )

If(

  isValidJSON ( propertyList )

;

  JSONDeleteElement ( propertyList ; propertyName )

;

  //Authored by  Shawn Flisakowski http://www.spf-15.com/fmExamples/

  /* Call a worker function to do the real work */

  RemovePropertyWorker ( ""; propertyList; propertyName )

)



So what has this actually achieved?

I can call a script using a structured parameter

someScriptToExecute ( addProperty ("";"surname"; "smith") )

and inside the script looks like this:

if ( Parameter converted to variables OK )

--- variable $surname will exist containing the value "smith"

end

Now the new custom functions have to be installed in each database file, and the existing custom functions have to be modified with their new definitions in each database file.  I wrote an AppleScript robot to do that for me, but that will be another post.