AnsweredAssumed Answered

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" ]

Go to Portal Row

[ 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 ]


     Go to Portal Row

          [ Select; Next; Exit after last ]

End Loop


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.