NateBrock73072

Convert Many FileMaker Records to JSON without Looping

Discussion created by NateBrock73072 on Mar 6, 2018
Latest reply on Mar 12, 2018 by Paul Jansen

Easily produce JSON Object Arrays of many FileMaker Records in a Found Set without Script Looping or Recursion.

Maybe this will help someone else in the FileMaker Community.

 

***!! Important Note: I don't use spaces in my table names or field names; if you do, you will need to wrap them in quotes to use them in the SQL query. ***!!

 

Another Function Update based on BigTom's Comment on Number Conversion:

Version 3 = time to Compile 10433 Records = 642 ms

In this you can "Cast" the data types properly to the JSON again without LOOPING or SUBSTITUTION CALLS TO THE RESULTSET. Just use the CASE WHEN Statement to change the output to whatever your want, even boolean if needed.

 

Let(  
[  
//run on any FileMaker Pro Layout to get the layouts underlying TableName  
tblName = Get ( LayoutTableName ); 


//Get the FieldNames From My Current Layout and Only Use those in the JSON Payload
layoutFieldNames = "'" & Substitute ( FieldNames ( Get(FileName) ; Get(LayoutName )) ; "¶" ; "','" ) & "'";

// ^^Optionally You can set the table name statically, with ScriptParameter, or however your would like.   
  
  


//CASE WHEN hide = 0 THEN 'false' ELSE 'true' END AS hide
//fetch all the Normal Fields in this table tied to this layout your on. Don't get Summary or Calc Fields  
tblfields = 


ExecuteSQL ( "




SELECT 


CASE WHEN FieldType LIKE 'decimal' 


THEN '''\"' + FieldName + '\" : '' + ' + 'STRVAL(' + FieldName + ')' 


ELSE '''\"' + FieldName + '\" : \"'' + ' + FieldName + ' + ''\"''' 


END AS Fields




FROM FileMaker_Fields WHERE TableName LIKE '"&tblName&"' AND FieldClass LIKE 'Normal' AND FieldName IN ("&layoutFieldNames&")" ; "" ; ","  );  
  
  
//fetch all the records in this table with these fieldnames and format the SQL Select Result as JSON Array of Records  
getRecordsInJson = "[" & ExecuteSQL ( "SELECT '{¶'+" & tblfields  & " FROM "& tblName ; ",¶" ; "¶},¶" ) & "¶}]"  
// ^^ Optionally above you can add your where clause to fetch specific records such as StartDate Only in 2015  
  
  
];


//tblfields  
getRecordsInJson
//Print the JSON Created Above in a Pretty Way. Plus this is an easy way to validate if your JSON Generated is Correct
//JSONFormatElements ( getRecordsInJson )  
  
)

 

 

Updated: Version 2 Added FieldNames Function. If you wanted to get just the fields on your layout with No Looping Required

Let(  
[  
//run on any FileMaker Pro Layout to get the layouts underlying TableName  
tblName = Get ( LayoutTableName ); 


//Get the FieldNames From My Current Layout and Only Use those in the JSON Payload
layoutFieldNames = "'" & Substitute ( FieldNames ( Get(FileName) ; Get(LayoutName )) ; "¶" ; "','" ) & "'";

// ^^Optionally You can set the table name statically, with ScriptParameter, or however your would like.   
  
  
//fetch all the Normal Fields in this table tied to this layout your on. Don't get Summary or Calc Fields  
tblfields = ExecuteSQL ( "SELECT '''\"' + FieldName + '\" : \"'' + ' + FieldName + ' + ''\"''' FROM FileMaker_Fields WHERE TableName LIKE '"&tblName&"' AND FieldClass LIKE 'Normal' AND FieldName IN ("&layoutFieldNames&")" ; "" ; ","  );  
  
  
//fetch all the records in this table with these fieldnames and format the SQL Select Result as JSON Array of Records  
getRecordsInJson = "[" & ExecuteSQL ( "SELECT '{¶'+" & tblfields  & " FROM "& tblName ; ",¶" ; "¶},¶" ) & "¶}]"  
// ^^ Optionally above you can add your where clause to fetch specific records such as StartDate Only in 2015  
  
  
];  


//Print the JSON Created Above in a Pretty Way. Plus this is an easy way to validate if your JSON Generated is Correct
JSONFormatElements ( getRecordsInJson )  
  
)

 

 

 

 

Original Method in Post. Version 1

Let(
[
//run on any FileMaker Pro Layout to get the layouts underlying TableName
tblName = Get ( LayoutTableName );
// ^^Optionally You can set the table name statically, with ScriptParameter, or however your would like. 


//fetch all the Normal Fields in this table tied to this layout your on. Don't get Summary or Calc Fields
tblfields = ExecuteSQL ( "SELECT '''\"' + FieldName + '\" : \"'' + ' + FieldName + ' + ''\"''' FROM FileMaker_Fields WHERE TableName LIKE '"&tblName&"' AND FieldClass LIKE 'Normal'" ; "" ; ","  );


//fetch all the records in this table with these fieldnames and format the SQL Select Result as JSON Array of Records
getRecordsInJson = "[" & ExecuteSQL ( "SELECT '{¶'+"&tblfields&" FROM "& tblName; ",¶" ; "¶},¶" ) & "¶}]"
// ^^ Optionally above you can add your where clause to fetch specific records such as StartDate Only in 2015


];


//Print the JSON Created Above in a Pretty Way
JSONFormatElements ( getRecordsInJson )


)

 

Outcomes