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 - Show more from this author
Michael Kupietz FileMaker Consulting
http://www.kupietz.com

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)

Let (
        theFieldNames =           
                Case (
                        IsValid(GetFieldName(fieldForRecord)) = 0 and fieldForRecord="";
                         FieldNames (
                Get ( FileName ) ; Get ( LayoutTableName ) );
       IsValid(GetFieldName(fieldForRecord)) = 0 and fieldForRecord≠"";
                         FieldNames (
                Get ( FileName ) ; fieldForRecord );
ExecuteSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName='"&
GetValue(Substitute(GetFieldName(fieldForRecord);"::";"¶");1)
&"' AND FieldClass='Normal'";"";"")
                ) ;
        While (
                [ count = ValueCount ( theFieldNames ) ; out = "{}" ] ;
                count > 0 ;
                [
                        thisField = GetValue ( theFieldNames ; count ) ;
                        out =
                            JSONSetElement (
                                out ;
                                thisField ;
                                GetField ( "${"&Get ( LayoutTableName ) & "}::${" & thisField&"}" ) ; /* Need to ${} field and table names in case they contain FM function names within them */
                                JSONstring
                                ) ;
                        count = count - 1
                ] ;
                out
                  )
        )

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

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

recordAsJson

Comments

Support this website.

Support this website.