Brian Dunning's FileMaker Custom Functions

ExecuteSQLResultToJSON ( sqlResult ; fieldSub ; rowSub ; fieldList ; limit )

Converts an ExecuteSQL function result into a JSON object with an array of rows (

  Be the first to rate this function Log in to vote

Jeff Norris   Jeff Norris - Show more from this author

Share on Facebook Share on Twitter

  Sample input:
Let (

[
fieldSub = "|#!"; // some unique string not appearing in data w/o ¶
rowSub = "[*>"; // some other unique string not appearing in data w/o ¶
sqlResult = ExecuteSQL ( "[YOUR FANCY SQL QUERY HERE]" ; fieldSub ; rowSub { ; arguments... } )
];

ExecuteSQLResultToJSON ( sqlResult ; fieldSub ; rowSub ; "FieldName1¶FieldName2|JSONNumber¶FieldName3|JSONBoolean" ; 0)

)
  Sample output:
{
"data":[
{
"FieldName1":"Data from row 1 column 1 of SQL query",
"FieldName2":743,
"FieldName3":false
},
{
"FieldName1":"Data from row 2 column 1 of SQL query",
"FieldName2":27,
"FieldName3":true
}
],
"records":2
}

===== OR, IF ERROR =====

{
"error":1,
"message":"The error message"
}

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

A different approach to returning structured data (as a JSON object) from FileMaker ExecuteSQL functions.

I do not attempt to dynamically gin up a SQL query.

Rather, this function accepts the *results* from an ExecuteSQL function, along with a value list of field names (& optionally types), and *then* generates the JSON object. Much simpler, IMO, but your mileage may vary, etc.

For the parsing to work, the fieldSub, rowSub & returnSub parameters must (1) all be unique, (2) not contain ¶, and (3) not appear in the data itself. The fieldSub and rowSub must match the fieldSeparator and rowSeparator parameters in the ExecuteSQL function, so I usually just use a Let function and pass these into both functions (see sample input above).

To capture errors, you can check the returned result for "error", e.g.: If [ JSONGetElement ( $result_of_custom_function ; "error" ) ]

 

Comments

Log in to post comments.

 

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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: