BlogFileMaker

Create Dynamic JSON for Related Records Using This Custom Function

By April 25, 2019 May 10th, 2019 5 Comments

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 )

Figure 1. Custom function for creating dynamic JSON


Expand image

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:

Example input using JSONFormatElements() Function

Figure 2. Example input

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:

Screenshot of example results

Figure 3. Results from the example input.

Get the Sample File

I hope that someone finds this useful!

Makah Encarnacao

Makah Encarnacao

Makah is a Technical Project Lead that hails from Albuquerque, NM. She joined Soliant a few months after graduating from UCLA in 2007, and must like it a lot because she’s still here! She is certified in FileMaker 9, 10, 11, 12, 13, 14, 15, 16 and 17, and placed 4th in the 2015 FileMaker DevCon Developer Cup. She was a speaker at the 2016 and 2018 FileMaker Conference. When she is not coding, she is enjoying life with her family that recently increased by 1.

5 Comments

  • Avatar Johan Hedman says:

    Thanks Makah for a great CF

  • Avatar julio says:

    It is a fastest way to put json together THANK YOU!!, but I found that when I tried to include a UUID value under one field in “allfields” CF came with random data like this:

    {
    “Host” : “JulioΓÇÖs MacBook Pro”,
    “ModifiedBy” : “Admin”,
    “RelatedProducts” :
    {
    “Measures” :
    [
    {
    “pKey” : 9
    },
    {
    “pKey” : “C5782E77-FEC1-4DA7-BAD7-F437A9C9089B”
    },
    {
    “pKey” : 36
    },
    {
    “pKey” : “CA5BF06B-45F5-4A61-BF67-23BD673EA69A”
    },
    {
    “pKey” : “DB23CEB6-6410-4C4C-ABF3-4B561451CC26”
    },
    {
    .
    .
    .
    {
    “pKey” : 4.4884e+92
    },
    {
    “pKey” : “BEF1D2B1-6E24-4043-9983-3A26148E63C2”
    },
    {
    “pKey” : “B9CFC202-085D-45E5-8AAC-C39D8DEAEA1A”
    }
    ]
    },
    “dateModify ” : “4/16/2019 4:31:09 PM”,
    “pKey” : “02FDC3D3-21AC-4DEA-A66F-DA5AD178F195”,
    “type” : “meaReport”
    }

    • Avatar Makah Encarnacao says:

      Hi Julio,
      Very odd….. I replied to your comment and it seems to have disappeared. I am replying again :). Thank you for your comment, you seemed to have found an issue with the way the the JSONSetElement () function’s auto-detection of data type is failing. I have updated the custom function to include a check for the data type and explicitly set it instead of relying on the auto-detection. Thank you for finding this issue!

  • Suggestion: use a calculated field Get(RecordId) to count the records rather than Model which might be empty.

    I will adapt this to use records in a table.

    • Avatar Makah Encarnacao says:

      Hi Jack, are you suggesting that you create a new field that is an auto-enter calc of get( recordID) and then use Count( Model::RecordID) instead of Count( Model::ID)? Since ID is a primary key and is required to be not empty, then I think it’s save to use Count( Model::ID). Maybe you can explain the benefit of creating a new non-empty field instead of using an existing non-empty field? Thank you for your question!

Leave a Reply