Aller au contenu
  • billets
    97
  • commentaires
    3
  • vues
    4 589

How to Automatically Create Variables from JSON Data in FileMaker

Soliant Consulting

38 vues

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>
 *
 * @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
				~plusOneText = GetAsText ( ~value + 1 ) ; 
				~isValidDate = not EvaluationError ( GetAsDate ( ~value ) ) ; 
				~isValidTime = not EvaluationError ( GetAsTime ( ~value ) ) ; 
				~number = GetAsNumber ( ~value ) ; 
				~value = Case ( 
					~value = "" or ~value = "?" or ~number = "?" ; Quote ( ~value ) ; 
					~isValidDate and ~isValidTime and GetAsText ( GetAsTimestamp ( ~value ) + 1 ) = ~plusOneText ; "GetAsTimestamp ( " & Quote ( ~value ) & " )" ; 
					~isValidTime and GetAsText ( GetAsTime ( ~value ) + 1 ) = ~plusOneText ; "GetAsTime ( " & Quote ( ~value ) & " )" ; 
					~isValidDate and GetAsText ( GetAsDate ( ~value ) + 1 ) = ~plusOneText ; "GetAsDate ( " & Quote ( ~value ) & " )" ; 
					~value ≠ ~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.

The post How to Automatically Create Variables from JSON Data in FileMaker appeared first on Soliant Consulting.


Voir le billet d'origine



0 Commentaire


Commentaires recommandés

Il n’y a aucun commentaire à afficher.

Créer un compte ou se connecter pour commenter

Vous devez être membre afin de pouvoir déposer un commentaire

Créer un compte

Créez un compte sur notre communauté. C’est facile !

Créer un nouveau compte

Se connecter

Vous avez déjà un compte ? Connectez-vous ici.

Connectez-vous maintenant

×