JSONFromFields ( json ; fieldNameList ; excludeFieldNameList )

Builds a JSON Object {} or Array [] with each field's value as the correct type of JSON element

Average rating: 5.0 (1 vote) Log in to vote

Jonathan Mickelson - Show more from this author

Sample input:

JSONFromFieldList ( "" ; "FirstName¶people:::Age" ; "" ) =
JSONFromFieldList ( "{}" ; "FirstNam ¶people:::Age" ; "" ) =
JSONFromFieldList ( "[]" ; "LastName¶FirstName¶people:::Age" ; "people::FirstName" ) =
JSONFromFieldList ( "[]" ; "people:::Age & ¶ & FirstName" ; "" ) =

Sample output:

= {"FirstName":"Jane","Age":24}
= {"FirstName":"Jane","Age":24}
= ["Smith",24]
= [24,"Jane"]

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*----------------------------------------------------------------------
--------------- CUSTOM FUNCTION DESCRIPTION BELOW ----------
------------------------------------------------------------------------
Function:        JSONFromFields ( json ; fieldNameList ; excludeFieldNameList )

Description:   A Recursive Function that uses the 'json' parameter to determine what 
                        JSON Object or Array should be built.  Then builds JSON Elements in the JSON root 
                        for each fieldName in the return delimited value list parameter 'fieldNameList'.

Use Cases: 
                        1)  Dynamicly build JSON from fields on the Current Layout.
                        2) A Calc Field that aggregates JSON for records from a table, so the data can be accessed from any 
                            context for that record, scraping a system layout, with a Unstored Calculation Field Calc,
                            like the below:
                                                           Let ( 
                         [
                         excludeMe = "z_Record_JSON_ct" ; //static field name so doesn't reference itself
                         layoutForFields = "z_ImportantFields_JSON" ; // Use Get ( LayoutName ) for dynamic
                         fieldList = FieldNames ( Get ( FileName ) ; layoutForFields ) ;
                         json = JSONFromFields ( "{}" ; fieldList ; excludeMe )
                         ] ; 
                         JSONFormatElements ( json )
                         )     

Examples 
                      NOTE: fieldNames should be generated with GetFieldName ( field ) rather than hardcoded as in examples below 
                      - JSONFromFieldList ( "" ; "FirstName¶people:::Age" ; "" ) = {"FirstName":"Jane","Age":24}
                      - JSONFromFieldList ( "{}" ; "FirstNam ¶people:::Age" ; "" ) = {"FirstName":"Jane","Age":24}
                      - JSONFromFieldList ( "[]" ; "LastName¶FirstName¶people:::Age" ; "FirstName" ) = ["Smith",24]
                      - JSONFromFieldList ( "[]" ; "people:::Age & ¶ & FirstName" ; "" ) = [24,"Jane"]

Parameters:

      json -     a JSON Object {}  or Array [], if already exists, or can define a new one:
                        - "" value     = a JSON object {} with name value pairs. Same as "{}"
                        - "{}"  value  = a JSON object {} with name value pairs.
                        - "[]"  value  = a JSON oArray [] with indexed values in the order of the fields provided.

      fieldNameList  -     List of return delimited fieldNames, fully qualified or simple (local table format), 
                                       that you want to generate into a JSON Object or Array.

      excludeFieldNameList  -     List of return delimited fieldNames, fully qualified or simple (local table format), 
                                       that you want excluded from teh results.  This is good if you use layout scraping to 
                                       dynamicly generate field lists, and do not want certain sensitive fields included.

 NOTE:  IF YOU USE THIS FUNCTION TO DISPlAY JSON FROM CURRENT LAYOUT FIELDS
              YOU SHOULD ADD THE FIELD TO THE EXCLUDE LIST TO NOT RUN AFOUL OF
              FIELD/CALC RECURSION, WHERE THE FIELD ATTEMPTS TO DISPLAY ITSELF
              WITH ITSELF AS AN JSON ATTRIBUTE.

Function Result:         a JSON Object {}  or Array [] with each attributs being the fieldName or index ordered 
                                     by the order provided, and the data from the appropriate field.

-----------------------------------------------------------------------
Created By: Jonathan Mickelson
Created On: 2019-04-15
-----------------------------------------------------------------------
Modified: 2019-04-17 - Jonathan Mickelson - Integrated the JSONtype and TrimMagic Functions for no other CF dependency. 
Modified: 2019-05-06 - Jonathan Mickelson - Added ExcludeFieldNameList, so that calc fields can exclude themselves more easily. 
------------------------------------------------------------------------
--------------- CUSTOM FUNCTION CALCULATION BELOW --------
----------------------------------------------------------------------*/
Let ( 
[
// INPUT PARAMETERS
json = Case ( not IsEmpty ( json ) ; json ; "{}" ) ; 
fieldNameList = fieldNameList ; 
excludeList = excludeFieldNameList ; 

// SANITIZE INCOMING fieldNameList, BLANK VALUES ARE BAD
fieldNameList = Case ( Left ( fieldNameList ; 1 ) = "¶" ; Middle ( fieldNameList ; 2 ; 10000 ) ; fieldNameList ) ; 
fieldNameList = Case ( Right ( fieldNameList ; 1 ) = "¶" ; Left ( fieldNameList ; Length ( fieldNameList ) - 1 ) ; fieldNameList ) ; 
fieldNameList = Substitute (  fieldNameList ; [ "¶¶¶"; "¶" ] ; [ "¶¶"; "¶" ] ) ; 

fieldNameCount = ValueCount ( fieldNameList ) ; 
field = GetValue ( fieldNameList ; 1 )  ; 

fieldName = Case ( WordCount ( field ) > 0 and PatternCount ( field ; "::") = 1 ; GetValue (  Substitute ( field ; "::"; "¶" ) ; 2 )  ; field ) ; 
fieldNameFullyQualified = Case ( WordCount ( field ) > 0 and PatternCount ( field ; "::") = 0 ; 
                                                          Get ( LayoutTableName )  & "::" & field ; 
                                                          field
                                                          ) ; 

field = Case (  IsEmpty ( excludeFieldNameList )  or 
                       ( 
                        PatternCount ( ¶ & excludeFieldNameList & ¶ ; ¶ & fieldName & ¶ ) = 0   
                          and 
                       PatternCount ( ¶ & excludeFieldNameList & ¶ ; ¶ & fieldNameFullyQualified & ¶ ) = 0 
                        )
                       ; field ) ; 


fieldFMType = MiddleWords ( FieldType ( Get ( FileName ) ; fieldNameFullyQualified ) ; 2 ; 1 ) ; 
value = Case ( WordCount ( field ) > 0 ; GetField ( fieldNameFullyQualified ) ) ; 
type = 
         Case ( WordCount ( field ) > 0 ; 
              Case ( 
             // JSONBoolean - THIS IS TRICKY AND PROBABLY SOLUTION SPECIFIC,
             //                            DEFINE YOUR CRITERIA FOR A FIELD CONTAINING BOOLEAN DATA
             /*------------- DISABLED --------------
                  fieldFMType= "Text" 
                  and not IsEmpty ( value ) 
                   and 
                   ( 
                     Exact ( value ; 0 ) 
                     or 
                     Exact ( value ; 1 ) 
                     // IF you want to be looser and allow text True and False values as boolean
                     or 
                     ( Length ( value ) = 4  and Lower ( value ) = "true" ) 
                     or
                    ( Length ( value ) = 5 and Lower ( value ) = "false" ) 
                  ) 
               ; JSONBoolean ;
               --------------- DISABLED ------------*/

             // JSONNumber
                 fieldFMType = "Number" 
                ; JSONNumber ; 

             // JSONObject { }
                 fieldFMType = "Text"
                   and not IsEmpty ( value ) 
                   and Left ( value ; 1 ) = "{" 
                   and Right ( value ; 1 ) = "}" 
                   ; JSONObject ; 

             // JSONArray [ ] 
                 fieldFMType = "Text"
                   and not IsEmpty ( value  ) 
                   and Left ( value ; 1 ) = "["
                   and Right ( value ; 1 ) = "]" 
                   ; JSONArray ; 

             // JSONString (DEFAULT) 
                 JSONString
               )
          ) ; 


NEWjson = Case ( WordCount ( field ) = 0  ;  json ;
                         Left ( json ; 1 ) = "{" ;   JSONSetElement ( json ; fieldName ; value ; type ) ; 
                         Left ( json ; 1 ) = "[" ;   JSONSetElement ( json ; ValueCount ( JSONListKeys ( json  ; "" ) ) ; value ; type ) ; 
                          ) ; 
NEWFieldNameList = RightValues ( fieldNameList ; fieldNameCount - 1 ) 
] ; 

Case ( fieldNameCount <= 1 ; 
            // RETURN FINAL JSON, NO MORE FIELDS TO PROCESS
            NEWjson ; 
           // RECURSIVE CALL - MORE FIELDS TO PROCESS
           JSONFromFields ( NEWjson ; NEWfieldNameList ; excludeList ) 
          )

) // END LET

Description: A Recursive Function that uses the 'json' parameter to determine what
JSON Object or Array should be built. Then builds JSON Elements in the JSON root
for each fieldName in the return delimited value list parameter 'fieldNameList'.

Use Cases:
1) Dynamically build JSON from fields on the Current Layout.
2) A Calc Field that aggregates JSON for records from a table, so the data can be accessed from any
context for that record, scraping a system layout, with a Unstored Calculation Field Calc,
like the below:
Let (
[
excludeMe = "z_Record_JSON_ct" ; //static field name so doesn't reference itself
layoutForFields = "z_ImportantFields_JSON" ; // Use Get ( LayoutName ) for dynamic
fieldList = FieldNames ( Get ( FileName ) ; layoutForFields ) ;
json = JSONFromFields ( "{}" ; fieldList ; excludeMe )
] ;
JSONFormatElements ( json )
)

Examples:
NOTE: fieldNames should be generated with GetFieldName ( field ) rather than hardcoded as in examples below
- JSONFromFieldList ( "" ; "FirstName¶people:::Age" ; "" ) = {"FirstName":"Jane","Age":24}
- JSONFromFieldList ( "{}" ; "FirstNam ¶people:::Age" ; "" ) = {"FirstName":"Jane","Age":24}
- JSONFromFieldList ( "[]" ; "LastName¶FirstName¶people:::Age" ; "FirstName" ) = ["Smith",24]
- JSONFromFieldList ( "[]" ; "people:::Age & ¶ & FirstName" ; "" ) = [24,"Jane"]

Parameters:

json - a JSON Object {} or Array [], if already exists, or can define a new one:
- "" value = a JSON Object {} with name value pairs. Same as "{}"
- "{}" value = a JSON Object {} with name value pairs.
- "[]" value = a JSON Array [] with indexed values in the order of the fields provided.

fieldNameList - List of return delimited fieldNames, fully qualified or simple (local table format),
that you want to generate into a JSON Object or Array.

excludeFieldNameList - List of return delimited fieldNames, fully qualified or simple (local table format),
that you want excluded from the results. This is good if you use layout scraping to
dynamically generate field lists, and do not want certain sensitive fields included.

NOTE: IF YOU USE THIS FUNCTION TO DISPlAY JSON FROM CURRENT LAYOUT FIELDS
YOU SHOULD ADD THE FIELD TO THE EXCLUDE LIST TO NOT RUN AFOUL OF
FIELD/CALC RECURSION, WHERE THE FIELD ATTEMPTS TO DISPLAY ITSELF
WITH ITSELF AS AN JSON ATTRIBUTE.

Function Result: a JSON Object {} or Array [] with each attributes being the fieldName or index ordered
by the order provided, and the data from the appropriate field.

Comments

		Jonathan Mickelson May 7, 2019
I updated the function and changed it to be called "JSONfromFields"

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

JSONFromFields ( json ; fieldNameList ; excludeFieldNameList )

Comments

Support this website.

Support this website.