Hello everyone! I have created a custom function for dynamically creating JSON arrays for related records in FileMaker, and I’d like to share it with you all.
The custom function can be found in the sample file. Its full signature looks like this:
#JSON_GetRelatedData ( objectName ; attributeList; relatedTO; relatedRecordCount; JSON; iterationRecord; iterationField )
It takes in several parameters, as described below:
- objectName – This will be what you want the parent object to be called, in my sample file I call it “RelatedModels.”
- attributeList – This is the field name you want to include in the JSON. This list needs to be return delimited. In my example, I am using “Name¶BodyStyle¶Year¶StartingPrice.” You do need to have at least one value in this list, and it has to have a corresponding field in the related table.
- relatedTO – This is the Table Occurrence name; in this example it’s simply “Model.” If you’re using anchor-buoy, it would look something like “MNF_MOD__Model.” A good hint is to put what you see in the bottom left corner of the portal when you’re in layout mode.
- relatedRecordCount – This custom function is recursive, so it needs to know when to stop. When you pass in the number of related records, then the CF will only go looking for those number of records. In my example, I am using Count( Model::ID) to determine how many models exist for the current manufacturer.
- JSON – leave this blank (empty quotes), it’s used in the recursive iterations.
- iterationRecord – leave this blank (empty quotes), it’s used in the recursive iterations.
- iterationField – leave this blank (empty quotes), it’s used in the recursive iterations.
My sample file has 2 buttons that use this custom function: Manufacturer and Model JSON and Model JSON. The Manufacturer and Model JSON button demonstrates how you would use this custom function in combination with other JSON data. The Model JSON button demonstrates how to use the custom by itself.
An example input would be as shown in Figure 2:
Note: This example has the JSONFormatElements() Function around it which is not required but makes your results more human readable.
And the example results will be as shown in Figure 3: