JSON.FromRecord ( fields ; omitRelated )

Create JSON representation of the current record

Average rating: 5.0 (2 votes) Log in to vote

Michael Wallace - Show more from this author
Empowered Data Solutions
http://empowereddatasolutions.com

Sample input:

JSON.FromRecord ( "" ; True )

Sample output:

{
"Address" : "123 Any St",
"City" : "Lewisville",
"NameFirst" : "Michael",
"NameLast" : "Wallace",
"State" : "TX",
"Zip" : "75067"
}

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

// JSON.FromRecord ( fields ; omitRelated )
/*
Written by
Michael Wallace - Empowered Data Solutions
mwallace@empoweredds.com

Adjusted to handle single field without error
Lazarus Sismanis - Decision Group

Adjusted to fix error with long numbers (i.e. number uuids)
Steve Stearns <stevestearns@alkemelabs.com>

fields - optional - list of fields to return as JSON.  If none provided uses the fields from the current layout
omitRelated - optional - default to false.  If true then only local fields are used.

Handles numbers, text, JSON Arrays in fields, JSON Objects in fields, containers as Base64Encoded data, etc.

Known limitations - The following are ignored
Repeating fields beyond the first repetition
global fields
summary fields

*/

Let ([
  ~omitRelated = If ( IsEmpty ( omitRelated ) ; False ; omitRelated )
; ~fields = If ( IsEmpty ( fields ) ; FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ; fields )
; ~fields = If ( Right ( ~fields ; 1 ) = "¶" ; Left ( ~fields ; Length ( ~fields ) - 1 ) ; ~fields )

; ~n = ValueCount(~fields)

//; ~count = 90
//; ~fields = Left ( MiddleValues ( ~fields ; ~count ; 100 ) ; Length ( MiddleValues ( ~fields ; ~count ; 100 ) ) - 1 )

; ~fileName = Get ( FileName )
; ~table = Get ( LayoutTableName )

; ~jsonSetCalcStart = "Let ([
~field = \""

; ~jsonSetCalcEnd = "\";
~related = GetAsBoolean ( PatternCount ( ~field ; \"::\" ) ); 
~fullField = if ( ~related ; ~field ; " & Quote ( ~table ) & " & \"::\" & ~field ); 
~fieldType = FieldType ( " & Quote ( ~fileName ) & " ; ~fullField ); 
~global = PatternCount ( ~fieldType ; \"Global\" ); 
~summary = PatternCount ( ~fieldType ; \"Summary\" ); 
~repetitions = GetAsNumber ( RightWords ( ~fieldType ; 1 ) ) - 1; 
~repeating = ~repetitions and PatternCount ( ~field ; \"[\" ); 
~dontInclude = ~related and " & ~omitRelated & " or ~repeating or ~global or ~summary
; ~data = if ( ~dontInclude ; \"\" ; GetField ( ~fullField ) )
; ~data = if ( PatternCount ( ~fieldType ; \"Container\" ) ; Base64Encode ( ~data ) ; ~data )
; ~jsonType = Case ( 	
		~dontInclude ;
			\"\" ;
		PatternCount ( ~fieldType ; \"Number\" ) and Length ( ~data ) > 9 ; 
			JSONString ; 
		PatternCount ( ~fieldType ; \"Number\" ) and ~data = 0 ; 
			JSONString ; 
		PatternCount ( ~fieldType ; \"Number\" ) ; 
			JSONNumber ; 
		Left ( ~data ; 1 ) = \"[\" and Left ( JSONGetElement ( ~data ; \"\" ) ; 1 ) <> \"?\" ;
			JSONArray ;
		Left ( ~data ; 1 ) = \"{\" and Left ( JSONGetElement ( ~data ; \"\" ) ; 1 ) <> \"?\" ;
			JSONObject ;
			JSONString 
)
; ~result = \"[\" & Quote ( ~field ) & \" ; \" & Quote ( ~data ) & \" ; \" & ~jsonType & \"];\"
; ~result = if ( ~dontInclude ; \"\" ; ~result )
];
~result
)"
; ~jsonSetCalcFull = ~jsonSetCalcStart & 
Case (
	~n = 1; 
		~fields ;
	Substitute ( ~fields ; "¶" ; ~jsonSetCalcEnd & "¶" & ~jsonSetCalcStart ) 
)
& ~jsonSetCalcEnd
; ~jsonSetCalcFull = 
Case (
~n = 1; ~jsonSetCalcFull ;
"List ( " & Substitute ( ~jsonSetCalcFull ; "¶" ; ";¶" ) & ")"
)
; ~jsonSets = Evaluate ( ~jsonSetCalcFull )
; ~jsonSets = Left ( ~jsonSets ; Length ( ~jsonSets ) - 1 )
; ~jsonCreateCalc = "JSONSetElement ( \"{}\" ; " & ~jsonSets & " ) "
; ~result = Evaluate ( ~jsonCreateCalc )
];
~result
)

Michael Wallace - Empowered Data Solutions
mwallace@empoweredds.com

fields - optional - list of fields to return as JSON. If none provided uses the fields from the current layout
omitRelated - optional - default to false. If true then only local fields are used.

Handles numbers, text, JSON Arrays in fields, JSON Objects in fields, containers as Base64Encoded data, etc.

Known limitations - The following are ignored
Repeating fields beyond the first repetition
global fields
summary fields

10-15-2019 - Bug fixed where field list was adding extra return at the end breaking the calc.

Comments

		Lazarus Sismanis, Decision Group Jun 14, 2019
Good stuff. That being said, single field provided returns error: Thanks.

		Lazarus Sismanis, Decision Group Jun 18, 2019
Modified a bit to account for single field. Thank you for this wonderful custom function! ///////////// // JSON.FromRecord ( fields ; omitRelated ) /* Michael Wallace - Empowered Data Solutions mwallace@empoweredds.com fields - optional - list of fields to return as JSON. If none provided uses the fields from the current layout omitRelated - optional - default to false. If true then only local fields are used. Handles numbers, text, JSON Arrays in fields, JSON Objects in fields, containers as Base64Encoded data, etc. Known limitations - The following are ignored Repeating fields beyond the first repetition global fields summary fields */ Let ([ ~omitRelated = If ( IsEmpty ( omitRelated ) ; False ; omitRelated ); ~fields =If ( IsEmpty ( fields ) ; FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ; fields ); ~n = ValueCount(~fields); //; ~count = 90 //; ~fields = Left ( MiddleValues ( ~fields ; ~count ; 100 ) ; Length ( MiddleValues ( ~fields ; ~count ; 100 ) ) - 1 ) ~fileName = Get ( FileName ); ~table = Get ( LayoutTableName ); ~jsonSetCalcStart = "Let ([ ~field = \""; ~jsonSetCalcEnd = "\"; ~related = GetAsBoolean ( PatternCount ( ~field ; \"::\" ) ); ~fullField = if ( ~related ; ~field ; " & Quote ( ~table ) & " & \"::\" & ~field ); ~fieldType = FieldType ( " & Quote ( ~fileName ) & " ; ~fullField ); ~global = PatternCount ( ~fieldType ; \"Global\" ); ~summary = PatternCount ( ~fieldType ; \"Summary\" ); ~repetitions = GetAsNumber ( RightWords ( ~fieldType ; 1 ) ) - 1; ~repeating = ~repetitions and PatternCount ( ~field ; \"[\" ); ~dontInclude = ~related and " & ~omitRelated & " or ~rep

		Lazarus Sismanis, Decision Group Jun 18, 2019
Didn't take entire post... Here's the rest following ~jsonSetCalcFull: ~jsonSetCalcFull = ~jsonSetCalcStart & Case ( ~n = 1; ~fields ; Substitute ( ~fields ; "Â¶" ; ~jsonSetCalcEnd & "Â¶" & ~jsonSetCalcStart ) ) & ~jsonSetCalcEnd; ~jsonSetCalcFull = Case ( ~n = 1; ~jsonSetCalcFull ; "List ( " & Substitute ( ~jsonSetCalcFull ; "Â¶" ; ";Â¶" ) & ")" ); ~jsonSets = Evaluate ( ~jsonSetCalcFull ); ~jsonSets = Left ( ~jsonSets ; Length ( ~jsonSets ) - 1 ); ~jsonCreateCalc = "JSONSetElement ( \"{}\" ; " & ~jsonSets & " ) "; ~result = Evaluate ( ~jsonCreateCalc ) ]; ~result )

		Michael Wallace, Empowered Data Solutions Jun 18, 2019
Thank you Lazarus, I haven't had time to look at this since you noted the issue. I updated your change into this function and gave you credit in the comments.

		Tom RuBane, RuBane Law P.C. Jan 12, 2024
I tried adding this custom function in FMP 20.3 and got an error that the function name cant be used because it uses forbidden characters, logical operators, or an existing function name, etc. I've gotten a lot of use out of your other JSON functions, and was excited to find this. Not sure what I'm doing wrong.

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

JSON.FromRecord ( fields ; omitRelated )

Comments

Support this website.

Support this website.