Brian Dunning's FileMaker Custom Functions

recordAsJson

Specify a local or related field to data entry fields from that field's record as JSON. Easy way to export, copy, or archive records.

  Average rating: 4.0 (1 vote) Log in to vote

Michael Kupietz   Michael Kupietz - Show more from this author
Michael Kupietz FileMaker Consulting
http://www.kupietz.com

Share on Facebook Share on Twitter

  Sample input:
recordAsJson( self )
  Sample output:
"{\"_ParentRecordUUID\":22,\"_RecordUUID\":\"BE53AEBC-7F4C-4674-9BE4-78C0D2C7C164\",\"acctInst_assetType\":\"EQUITY\",\"acctInst_cusip\":74347,\"acctInst_description\":\"\"}"

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

Pass this function a single field, any field, from the record you want, and it will return a JSON object with all the data entry fields (number, text, date, timestamp, etc... NOT calculated fields) from that record in the form "fieldname":"value".


For instance, if you had a client table with a related address table, using the following function, you might grab a client record as JSON by creating an unstored calc field clients::clientAsJson in that table, defined as:

recordAsJson( self )

Or you might grab the first related addess as JSON from a Clients record with

recordAsJson( addresses::_kp )

or

recordAsJson( addresses::street_address )

As it's intended to be used in calculations where I don't want to risk it causing a loop, and because I only plan to use it for duplicating or moving records into archives, it only grabs the data entry fields (number, text, date, time, etc), not calculated fields. This can easily be changed by modifying or removing " AND FieldClass='Normal'" from the ExecuteSQL function.

This function has been battle tested and been through several rounds of revisions to handle edge cases and various FileMaker idiosyncrasies that broke early versions, such as table or field names containing FileMaker function names, or text strings containing only numeric digits and hyphens, which would be rendered as only the numeric part before the hyphen without specificing JSONstring. I suffer so you don't have to.

 

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