How to Automatically Create Variables from JSON Data in FileMaker

/ By / Claris FileMaker, FileMaker Custom Functions, JSON

FileMaker 16 introduced a series of functions to work with JSON data. The primary motivation for adding this functionality was to make it easier to work with web services that transfer data in JSON. However, there are plenty of other good uses for JSON outside of interoperability with external systems. Some examples include passing and receiving script parameters and results. You can also assemble data (for example, for a report), which needs to be collected and/or transformed in some way but which does not otherwise need to be stored in fields.

If you have data stored as JSON, presumably you will want to extract it at some point. And as part of doing that, you may end up assigning that data to variables, whether for better script readability or other reasons.

Adding these assignment steps to your script can be tedious, especially when the JSON document contains a lot of data. For instance, given the following JSON document that has key-value pairs for each letter in the alphabet, we would end up having to create 26 “Set Variable” script steps.

JSON example:

  • { “A” : “value1” , “B” : “value2”, … , “Z” : “value26” }

Script steps:

  • Set Variable [$a; Value:”value1″]
  • Set Variable [$b; Value:”value2″]
  • …
  • Set Variable [$z; Value:”value26″]

The following custom function will create these variables for you automatically:

  • JSONCreateVarsFromKeys ( json ; namespace )

It will create local variables for all keys belonging to the JSON document’s root node.

Take Note

  • You can leave the “namespace” parameter blank. If you specify a value, it is used as a prefix for the variables that get created.
  • Typecasting is handled by determining the type for each value and then using functions like Quote, GetAsDate, GetAsTime, etc., to ensure the correct type. I took this portion of the code from the FileMaker Standards # custom function. My understanding is that Daniel Smith and Jeremy Bante wrote this code section, so the credit for this goes to them. (If you know otherwise, please let me know using the comments section below.)
  • The variable names will be based on the keys that exist in the root JSON node, so this will only work if the key names follow the same naming restrictions as for FileMaker variables and fields. For instance, if a key name uses a reserved keyword such as AND or NOT, the custom function will return an error; specifically, error code 1204.
  • Errors will be indicated in the custom function result using standard FileMaker error codes. 0 means no error.
  • This function will return “?” when used in a pre-16 client.

Below is the custom function code.

/**
 * @SIGNATURE:
 * JSONCreateVarsFromKeys ( json ; namespace )
 *
 * @PARAMETERS:
 * json - JSON data
 * namespace - Prefix for the variables that are to be created; can be left blank
 *
 * @HISTORY:
 * CREATED on 2017-12-08 by Mislav Kos <mkos@soliantconsulting.com>
 * MODIFIED on 2018-02-18 by Mislav Kos <mkos@soliantconsulting.com> - corrected typecasting portion of the custom function
 *
 * @PURPOSE:
 * Create local variables for all keys belonging to the JSON document's root node.
 * For example, given the following JSON, this custom function will create $id and $color variables: { "id" : "123", "color" : "blue" }
 *
 * @RESULT:
 * This custom function will return an error code as a result.
 * 0 means success or empty JSON.
 * 5 means invalid JSON.
 * 1204 means one of the JSON root keys did not conform to the FileMaker naming restrictions for variable and fields.
 * Pre-16 clients will return "?".
 *
 * @ERRORS:
 * Errors will be indicated in the custom function result using standard FileMaker error codes: https://fmhelp.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Ferror-codes.html.
 *
 * @NOTES:
 * Keys must be named following the same naming restrictions as FileMaker variables and fields: https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help%2Fnaming-fields.html.
 * 
 * @DEPENDENCIES:
 * Does not require any other custom functions. Requires v16 or later client. Pre-16 clients will return "?".
 */

Case ( 
	IsEmpty ( json ) ; 0 ; // If JSON is empty, return 0 ("no error")
	
	Left ( JSONFormatElements ( json ) ; 1 ) = "?" ; 5 ; // If JSON is invalid, return 5 ("command is invalid")
	
	Let ( [ 
		~keys = JSONListKeys ( json ; "." ) ; // Get keys from JSON document's root node
		~key = GetValue ( ~keys ; ValueCount ( ~keys ) ) // Process keys starting with the last one; otherwise JSON arrays won't process correctly
	] ; 
		If ( 
			IsEmpty ( ~key ) ; 0 ; // If the JSON document's root node doesn't contain any keys, return 0 ("no error")
			
			// Create variable based on key, then delete key from JSON, and then recursively call CF again to process remaining keys
			Let ( [ 
				// Get value for key
				~value = JSONGetElement ( json ; ~key ) ; 
				
				// Ensure correct typecasting of value; without this, everything would get typecast as text
				// This next section was taken from the the # custom function and (I think) was written by Daniel Smith (github.com/dansmith65) and Jeremy Bante (github.com/jbante)
				// See https://github.com/filemakerstandards/fmpstandards/blob/master/Functions/%23Name-Value/%23.fmfn
				~text = GetAsText ( ~value ) ; 
				~number = GetAsNumber ( ~value ) ; 
				~validDate = IsValid ( GetAsDate ( ~text ) ) and not IsEmpty ( ~number ) ; 
				~validTime = IsValid ( GetAsTime ( ~text ) ) and Position ( ~text ; ":" ; 1 ; 1 ) > 0 and not IsEmpty ( ~number ) ;
				~value = Case ( 
					~value = "" or ~value = "?" ; Quote ( ~value ) ;
					~validTime and ~validDate ; "GetAsTimestamp ( " & Quote ( ~value ) & " )" ;
					~validTime ; "GetAsTime ( " & Quote ( ~value ) & " )" ;
					~validDate ; "GetAsDate ( " & Quote ( ~value ) & " )" ;
					~text ≠ GetAsText ( ~number ) ; Quote ( ~value ) ; 
					~number
				) ; 

				
				// Create variable based on key and value (and namespace)
				~error = EvaluationError ( Evaluate ( "Let ( $" & namespace & ~key & " = " & ~value & " ; \"\" ) " ) ) 
			] ; 
				If ( 
					~error ≠ 0 ; ~error ; // If we encountered an error, return the error code and don't bother processing the rest of the keys
					Let ( 
						json = JSONDeleteElement ( json ; ~key ) ; // Delete key from JSON
						JSONCreateVarsFromKeys ( json ; namespace ) // Recursively call custom function to process remaining keys
					)
				)
			)
		)
	)
)

Get the Demo File

Download the demo file which provides several examples, including how to use the custom function to receive script parameters and results.

References

Need Help?

If you have any questions on these instructions or the demo file, please let me know in a comment below. If you’re looking for help with customizing your FileMaker solution further, my team and I are happy to provide additional insights. Contact us today.

2/18/2018 update: The typecasting portion of the custom function was updated to correct and error.

9 thoughts on “How to Automatically Create Variables from JSON Data in FileMaker”

  2. Vincent

    Hi, that’s nice but it uses (and there’s no alternative) the Evaluate function that can get very slow if the variable content is a big text.
    This would be resolved if we finally get from FMI a function to create variables on the the fly, specifically made for this, so optimized for it, and hence fast.
    Please vote that idea, so people will be able to avoid Evaluate for this, and get much better speed :
    https://community.filemaker.com/ideas/2253

    Reply

  4. Pingback: JSON Variables, WebDirect in FileMaker 16, Scrolling the Unscrollable - FileMakerProGurus

  5. Charlie

    Hello and thank you for sharing. I’m currently working with JSON and I’m wondering what is wrong with my syntax… the function results in error 5. My example is as follows:

    [

    ┬á { “firstName”:”John”, “lastName”:”Doe” },
    ┬á┬á┬á { “firstName”:”Anna”, “lastName”:”Smith” },
    ┬á┬á┬á { “firstName”:”Peter”, “lastName”:”Jones” }

    ]

    Thank you

    Reply
  7. Axel

    This CF just creates variables from the JSON root node keys. So it’s pretty useless, isnt’t it? Most JSONs out there have a complex structure so this CF would only create one variable for the root node key(s) and ignore 99% of the rest.

    Reply
    1. Wim Decorte

      This technique isn’t meant to be used as a general-purpose JSON parsing approach, it is meant more as a multi-parameter passing technique. When passing in multiple parameters between scripts, it would be uncommon to have more than one level.
      But if needed you could certainly adapt the CF to use the While() function and iterate through each level and continue to spawn new variables.

      Reply
      1. Axel

        That’s true. But also if you just want to pass parameters between scripts, they can have multiple levels. So in order to make this CF work with any XML structure, I changed the line:

        ~keys = JSONListKeys ( json ; “.” ) ;
        to
        ~keys = JSONListAllKeys ( json ) ;

        which uses a CF from filemaker-magazin.de
        You’d then need a variable for the key name, since “[” and “]” from JSON arrays are not allowed in variable names:
        ~key_name = Substitute ( ~key ; [ “[” ; “|” ] ; [ “]” ; “|” ] )

        In the Let statement you’d then need to use ~key_name instead of ~key.

        Reply

Leave a Comment

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

Scroll to Top