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:
Get the Sample File
I hope that someone finds this useful!
Thanks Makah for a great CF
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”
}
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.
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!
Thanks Makah, a great CF!
Would it possible to iterate through another level? For example, I have a Companies table (where this function is running) that returns the company info and related “Branches” info (similar to Manufactures and Models) , but I have a third table that has Address information.
Could this function return Company > Branch > Address ?
This function turns all of my text fields that contain numbers only to a JSONNumber even though it should be a JSONString. Many API’s require percentages to be in a text format. “0.00” in a text field type returns a 0 instead of 0.00 because the function mistakenly changes text fields with all numbers to a number type. This makes this custom function completely unusable for almost anything.
I removed the dot in the filtered numbers and that fixed it for me. However, I am still having trouble with boolean values. Not sure how I am supposed to set that.
Hi Matt,
You are correct. In the custom function, there is a line “dataType = Case( Length( Filter( newValue; “1234567890.”))= Length( newValue); “JSONNumber”; “JSONString” ); “, if you don’t want to turn your numbers into JSONNumber, just update this line to “dataType = “JSONString” ;”. Thanks for pointing this out. Just be careful with comparisons of values or sorting by numeric values when using JSONString.
Also, this cf does not handle empty values at all. Many API’s require a name/value pair even when the value in the pair is empty. If I want to show a field called “id” but what to leave that id blank, I cannot do it with your cf. Also, if I had to specify a JSONNull value, I don’t think your cf knows what that is. I fixed the boolean type I posted about earlier by adding to the custom function case statement a condition that recognizes a “true” or “false”value and then assigns the type. Not sure what to do though about the blank values.
Hi Matt,
You are correct again. If the value is empty the JSON object will not be created. Depending on your situation this might not be ideal and should take it into account. Thanks for pointing this out.
Makah, I really appreciate this CF and the work and revisions that have gone into it, however I have a question I’m puzzling over.
I would like to use the results of this CF within a JSON calc field that I have built to include an array of related records (images), but I’m having trouble figuring out how to include the CF result as a sub-array with the rest of the local elements. Everything I’ve tried so far winds up with the related record elements including commented characters and not being structured properly. Here is an abbreviated version of the original calc:
JSONSetElement ( “{}” ;
[ “auctionid:” ; Inventory::zk.AuctionID.t ; JSONNumber ] ;
[ “lotNumber:” ; Inventory::LotNumber ; JSONString ] ;
[ “title:” ; Inventory::LotTitle ; JSONString ] ;
[ “description:” ; Inventory::LotDescription ; JSONString ]
)
Which formatted gives this:
{
“auctionid:” : 1,
“description:” : “Test description lot 77 this is just a test”,
“lotNumber:” : “77”,
“title:” : “That’s title lot 77”
}
I would like the above to include an array of the related images using your CF as follows:
#JSONGetRelatedData( “photos” ; “Order¶Caption¶URL_thumbnail¶URL_medium¶URL_large” ; “Inventory_Images_LotInvSN” ; Count( Inventory_Images_LotInvSN::id_image) ; “” ; “” ; “” )
{
“auctionid:” : 1,
“description:” : “Test description lot 77 this is just a test”,
“lotNumber:” : “77”,
“title:” : “That’s title lot 77”
“photos” :
[
{
“Order” : 1,
“URL_large” : “fm-images/1/large/1.jpg”,
“URL_medium” : “fm-images/1/medium/1.jpg”,
“URL_thumbnail” : “fm-images/1/thumbnail/1.jpg”
},
{
“Order” : 2,
“URL_large” : “fm-images/1/large/2.jpg”,
“URL_medium” : “fm-images/1/medium/2.jpg”,
“URL_thumbnail” : “fm-images/1/thumbnail/2.jpg”
},
{
“Order” : 3,
“URL_large” : “fm-images/1/large/3.jpg”,
“URL_medium” : “fm-images/1/medium/3.jpg”,
“URL_thumbnail” : “fm-images/1/thumbnail/3.jpg”
}
]
}
How would you include the related photos array in the primary array in a calculation (not building it in a script)?
Also, just because I ran into it, your screen shots above and demo file include JSONFormatElements in the example code but you appear to have later added it to the CF calculation itself so that it’s redundant in the example code.
Hi Will, I will shoot you an email and see if we can find a time to meet to help you troubleshoot. Thanks!
Thanks Makah. I think I’ve basically solved the issue. It occurred to me – in the middle of the night as insights tend to do – that perhaps the issue was related to the JSON “Type” value, which I had set to JSONString. I was not familiar with all the possible options but the last one JSONRaw jumped out at me as one likely to allow me to embed the results of your CF within an existing array.
JSONSetElement ( “{}” ;
[ “auctionid:” ; Inventory::zk.AuctionID.t ; JSONNumber ] ;
[ “lotNumber:” ; Inventory::LotNumber ; JSONString ] ;
[ “title:” ; Inventory::LotTitle ; JSONString ] ;
[ “description:” ; Inventory::LotDescription ; JSONString ]
[ “” ; #JSONGetRelatedData( “photos” ; “Order¶Caption¶URL_thumbnail¶URL_medium¶URL_large” ; “id_Inventory” ; Count( Inv_Images::zk_Constant) ; “” ; “” ; “” ) ; JSONRaw ] ;
)
Embedding it this way gives me exactly what I was looking for. I just needed to change the “type” parameter to JSONRaw and set the initial “json” parameter to empty quotes so that “photos” was not repeated.
Makah, just a followup to say that while my solution above to leave the initial “json” parameter empty works for one embedded array so that the array name – “photos” in this case – is not repeated, I’ve discovered that JSONSetElement will only allow you to do that once. If you try to do it again it just displays the first (alphabetically) sub-array and ignores any others. So, I’m still trying to figure out how to build a calculation with multiple nested arrays. I know it can be done with multiple script steps but I’d like to do it as a single calculation. I’m experimenting with Let() but so far haven’t found the right syntax.