Brian Dunning's FileMaker Custom Functions

JSONRecordCalc ( tableName )

Returns a calculation which can be used to quickly build a JSON representation of a single FileMaker record's JSON-eligible fields.

  Be the first to rate this function Log in to vote

Sam Barnum   Sam Barnum - Show more from this author
360Works
http://360works.com

Share on Facebook Share on Twitter

  Sample input:
JsonRecordCalc ( "Person" )
  Sample output:
JSONSetElement ( "{}" ;
["id" ; Person::id ; Case ( IsEmpty ( Person::id ) ; JSONNull ; JSONString ) ] ;
["created" ; iso8601FormatTimestamp(Person::created) ; Case ( IsEmpty ( Person::created ) ; JSONNull ; JSONString ) ] ;
["createdBy" ; Person::createdBy ; Case ( IsEmpty ( Person::createdBy ) ; JSONNull ; JSONString ) ] ;
["modified" ; iso8601FormatTimestamp(Person::modified) ; Case ( IsEmpty ( Person::modified ) ; JSONNull ; JSONString ) ] ;
["DOB" ; iso8601FormatDate(Person::DOB) ; Case ( IsEmpty ( Person::DOB) ) ; JSONNull ; JSONString ) ] ;
["modifiedBy" ; Person::modifiedBy ; Case ( IsEmpty ( Person::modifiedBy ) ; JSONNull ; JSONString ) ] ;
["firstName" ; Person::firstName ; Case ( IsEmpty ( Person::firstName ) ; JSONNull ; JSONString ) ] ;
["middleName" ; Person::middleName ; Case ( IsEmpty ( Person::middleName ) ; JSONNull ; JSONString ) ] ;
["lastName" ; Person::lastName ; Case ( IsEmpty ( Person::lastName ) ; JSONNull ; JSONString ) ] ;
["phone" ; Person::phone ; Case ( IsEmpty ( Person::phone ) ; JSONNull ; JSONString ) ] ;
["email" ; Person::email ; Case ( IsEmpty ( Person::email ) ; JSONNull ; JSONString ) ] ;
["isDriver" ; Person::isDriver ; Case ( IsEmpty ( Person::isDriver ) ; JSONNull ; JSONNumber ) ]
)

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

The purpose of this is to generate a calculation which could be passed to an `Evaluate()` statement, or (if performance is at all important), used within a calculation definition. The resulting calculation will likely benefit from some tweaking, but provides a good starting point for building JSON from a FileMaker record.

Unlike recursive implementations, the resulting calculation builds a JSON string in a single operation, which is significantly faster than calling `JSONSetElement` multiple times when there are many fields involved.

This function excludes Calculation, Summary, Global, and Container fields.

This function also excludes fields beginning with an underscore.

Does NOT support repeating fields.

Date/Time/Timestamp fields: These reference the various ISO8601Format* functions on this site. You may want to replace this with your own date formatting functions.

Update 2019-10-21: Any empty value is treated as `NULL`

 

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 15 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: