text: Constructing JSON from Related Records | FileMaker Community
  Constructing JSON from Related Records
  Question asked by fmdataweb on Nov 26, 2018
  Latest reply on Nov 29, 2018 by FileKraft
  I have half a dozen related tables that I need to generate JSON data from and I'm currently using the following approach of going to the portal containing the related data and looping through the portal records to build the JSON Object/Array as necessary:
  Set Variable [ $json; Value:"[]" ]
  Go to Object [ Object Name: "portalPhones" ]
  [ Select; First ]
  Set Variable [ $counter; Value:0 ]
  Set Variable [ $json; Value:Let([ ~json = JSONSetElement ( "" ;
  ["type"; Phones::Type; JSON_string ];
  ["number"; Phones::PhoneNumber; JSON_string]
  JSONSetElement( $json ; $counter ; ~json ; JSON_object ) )]
  Set Variable [ $counter; Value:$counter + 1 ]
  [ Select; Next; Exit after last ]
  Set Field [ Contacts::JSON; JSONFormatElements ( $json ) ]
  This is working well but I was wondering if there is another way to do this without having to go to a portal and loop through the portal records? I'd like to remove the dependancy on the portal if possible and shorten the scripts up as they get quite long when you have to do this 6 times in the one script. I've tried to come up with a single calculation to create the same result but haven't been able to get anything working so far.
  Visibility: Discussions233 Views
  Last modified on Nov 26, 2018 1:15 PM
  Jason Wood Nov 26, 2018 1:51 PM
  You could define a calculation field in Phones, jsonCalc =
  Then in your parent table, your JSON array can be obtained like this:
  "[" & Substitute ( List ( Phones:jsonCalc ) ; "¶" ; "," ) & "]"
  mikebeargie Nov 26, 2018 1:59 PM
  You could pull the data via ExecuteSQL() instead of via a portal. This would remove the Loop requirement for "walking the portal" to assemble the data, but you would then need to simulate the relationships in SQL form to get the correct related data back, and after that you'd need to parse said data from a known delimited format returned by ExecuteSQL() into JSON.
  After doing that, all you're really gaining is removing some script steps. There will probably be less performance, and it will take a lot of time to figure out how to write both of those calculations.
  So instead, why not build yourself a context-free script, like "Portal to JSON", where you call the script with a parameter of the portal name and exit the script with a result.
  That would change your scripting setup to:
  Perform Script [ "Portal to JSON" ; parameter: "portalPhones" ]
  If [ Get(ScriptResult) = "ERROR" ]
  Show Custom Dialog [ ERROR ]
  Set Field [ Contacts::JSON; Get(ScriptResult) ]
  "Portal to JSON" SCRIPT
  Go to Object [ Object Name: Get(ScriptParameter) ]
  Exit Script [ "ERROR" ]
  Go to Portal Row [ Select; First ]
  Go to Portal Row [ Select; Next; Exit after last ]
  Exit Script [ $json ]
  This methodology also has other advantages, better error checking, not having to reset your $json and and $counter variables every time you want to reuse it, etc...
  And it gets you the "clean" look you're after.
  mikebeargie @ Jason Wood on Nov 26, 2018 2:01 PM
  He would have to setup those cached JSON fields all over the database, since he mentions he may be doing this up to six separate times in a script (assuming with different portals). That may add more to the clutter than it's worth, but is an alternative that would work.
  philipHPG Nov 26, 2018 2:15 PM
  You could use the GetNthRecord function and essentially loop through related records that way. It still involves looping, but eliminates the dependency on the portal object being on the layout. However, if the portal is filtered or sorted, you may need to accommodate in the GetNthRecord loop.
  Jason Wood @ mikebeargie on Nov 26, 2018 2:40 PM
  You could also make them Unstored so that they don't cache and the only "clutter" is the additional fields that would be seen in the development environment. Even with these values unstored, I doubt this method would run slower than a loop...
  steve_ssh Nov 26, 2018 3:20 PM
  Plenty of great thoughts already here.
  Some added thoughts/opinions:
  1) I agree with the idea of getting away from using the portal objects to collect your related data. My personal opinion is that such data collection is more robust when performed via some other means (such as other options already mentioned in this thread), and so I view getting away from portal scraping as an improvement.
  2) The method Jason mentioned (regarding having a dedicated JSON field) is a technique which seems to be tried and true, and used by many. I've tended to shy away from it, because I prefer less data stored in tables, but, even so, I see some very distinct advantages to such an approach, and there are developers for whom I have great respect who use this as a go-to methodology. Jason has mentioned using un-stored fields for this purpose, and this is something that I've recently considered, and which has made me warm up to this technique. I have yet to try the un-stored method, but my suspicions are in agreement with what Jason said, i.e. I doubt that it would be any slower than other techniques mentioned here.
  2) When applicable, I like Mike's suggestion to factor repeated script logic into a single re-useable script. Again, my preference would be to get away from using the portal, so, if going in this direction, my own approach might be to write a script that can collect my target data from a found set, and then I would focus my attention on using GTRR or scripted finds to programmatically create the needed found sets.
  3) Note: If there is going to be a single script which can be applied to different found sets and tables, it may require a certain amount of abstraction in order to accommodate being able to run against different contexts (and possibly with different field collection requirements). This is certainly possible, and even elegantly so, but I suspect it will require at least a small amount of effort to implement.
  4) When scraping data from a found set of records (or through a relationship), I like the approach mentioned by Philip, which is to use GetNthRecord to access each record, instead of navigating to each record in a found set. I note, however, that it may not work in all scenarios, as, AFAIK, GetNthRecord does not allow easily comprehensible access to related data which is one or more hops away from a target child record.
  5) As already mentioned: Whether or not the portals in question are filtered will have some bearing on this. Also, whether you are collecting data from a single child context, or whether you are also collecting data from contexts which are one or more relationship hops away from the child records will matter, too.
  6) I would not entirely rule out an ExecuteSQL approach, but it would not be near the top of my list for options to try. As Mike mentioned, one would have to write queries to reproduce the logic of the portal relationship (+ any filtering), and how messy this would get depends on the nature of the relationships (and filtering).
  Sincerely & hope this helps,
  fmpdude Nov 27, 2018 3:16 AM
  You could also use JDBC with SQL and construct the JSON in fast complied micro-service logic using standard JSON APIs like org.json. Extremely fast. Free. Would work with FMP. Avoids relatively FMP's slow looping. Since I've been doing JSON for a long time and, more recently, in FMP since version 12, I use this approach exclusively.
  fmdataweb Nov 28, 2018 3:49 AM
  Thanks for the thoughtful and varied suggestions. I'll take my time to consider the pros and cons of each approach - always interesting to see how others approach the same problem.