Brian Dunning's FileMaker Custom Functions

ExecuteSQLToJSON ( tableName ; fieldList ; whereClause )

Returns the result of a simple query as JSON

  Be the first to rate this function Log in to vote

Richard Dyce   Richard Dyce - Show more from this author
Dyce & Sons Ltd.
http://dyce.com

Share on Facebook Share on Twitter

  Sample input:
ExecuteSQLToJSON ( "user" ; "email¶active¶label¶id" ; "WHERE label LIKE '%r%'" )
  Sample output:
{
"1" :
{
"active" : "",
"email" : "rdyce1@its.jnj.com",
"id" : "6130949133090751582760057699633805740451913808838159790903",
"label" : "Dyce, Richard"
},
"2" :
{
"active" : "",
"email" : "lfrey@its.jnj.com",
"id" : "5308457655743778943661807436098065035874886682206779462798",
"label" : "Frey, Lukas"
}
}

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

The WHERE clause is manual currently, but can be left empty for smallish sets ;-)

This is a version 0, obviously. Improvements for v1 ;-) :

* Should probably look at Sam Barnum's use of the FileMaker_Fields table to correctly select the JSON field types. For my purposes though, text is mostly fine.

* Maybe look at building the WHERE query using JSON criteria

* Maybe look at providing ID field for enumerating result rows ;-)

* Better handling of ¶ in returned data ;-)

 

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: