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 - Show more from this author
360Works
http://360works.com

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)

"JSONSetElement ( \"{}\" ; ¶" & 
Substitute ( 
  ExecuteSQL (
    "SELECT '    [\"', FieldName, '\" ; ', " & 
      " CASE FieldType WHEN 'date' THEN 'iso8601FormatDate(' " &
      "                WHEN 'time' then 'iso8601FormatTime(' " &
      "                WHEN 'timestamp' then 'iso8601FormatTimestamp(' " &
      "                END, " &
      " TableName, '::', FieldName, " & 
      " CASE FieldType WHEN 'date' THEN ')' " &
      "                WHEN 'time' THEN ')' " &
      "                WHEN 'timestamp' THEN ')' " &
      "                END, " & 
      " ' ; ', " & 
      " ' Case ( IsEmpty ( ', " &
      " TableName, '::', FieldName, " & 
      " ') ; JSONNull ; ', " & 
      " CASE FieldType WHEN 'decimal' THEN 'JSONNumber' ELSE 'JSONString' END, " &
      " ' ) ', " &
      " '] ' " &
      " FROM FileMaker_Fields " &
      " WHERE TableName=? AND FieldClass='Normal' AND FieldType != 'binary' AND FieldType NOT LIKE '%global%' AND FieldName NOT LIKE '\_%'" &
      " ORDER BY FileMaker_Fields.FieldName ASC" ;
    "~~~" ;
    ";¶" ;
    tableName
  ) ; 
  "~~~" ; 
  ""
)
& "¶)"

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

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

JSONRecordCalc ( tableName )

Comments

Support this website.

Support this website.