Data API & JSONFormatElements for lazy sync

Discussion created by JaredHague on Mar 5, 2018
Latest reply on Mar 7, 2018 by alecgregory

I haven't really seen anything like this so I thought I would share to get opinions on this technique.


The problem speed. I have a solution that would sync large amount of records of data to an iOS device.  Normally speed wasn't an issue doing small updates where only some records would change.  The problem was when the update required getting all fresh data for some reason or another.  Getting the data from the server was quite quick.  Originally using xml then switching to data api I could get the whole set of records in a second or so.  Where things would take forever was looping over the data and creating records.  Sometimes it would take a hour or longer.  It could be several tables.


Attempt one.  I decided I didn't really need the records loaded until the user navigated to the record in question.  So I left the data in a field and if you needed recordId 1004 I would loop though the array of data looking for that record and then load it when I got there.  Obviously this is fine if you are going to loop though the whole array once and process each record.  But for this case I wanted to be able to find any record in the array instantly without testing the object at each index.  This method would produce a delay based on how far the record was in the array. So if it was in the first couple of records it was fine but if it was further in the index it would be slower and slower.


So the data out of the data api call was like this. (Simplified)


  "data" :



  "fieldData" :


  "companyName" : "testcompany1",


  "modId" : "36",

  "portalData" : {},

  "recordId" : "2"



So I get this list of objects and I can load a record simply by using

JSONGetElement ( datafield ; ".data[" & $n & "]" )

$n being the index of the object I want for loading the record.  The problem was as stated above I didn't always know the index and needed to test everything until I got to it.


So I used this formula to change the array into one object using the recordId as keys.

Let ([

~baseValue = "-99999##";  //this is just something to ensure JSONFormatElements brings the key to the top

~subJson = Substitute (  JSONFormatElements ( datafield ) ;

["\"recordId\" :" ; "\"" & ~baseValue & "\" :" ]);

~sortJson = JSONFormatElements ( ~subJson );

~tranformedJson = Substitute ( ~sortJson ; [Char(13) ; ""];[Char(9);""];["\",\"fieldData\"";"\":{\"fieldData\""];["{\"" & ~baseValue & "\" : \"";"\""];["],\"errorCode";"},\"errorCode"];["{\"data\" : [";"{\"data\" : {"])


JSONFormatElements ( ~tranformedJson )



Basically what is happening is the key "recordId" is dropped and the value is now the key for the object.  It happens in 3 steps.

1. The baseValue is added to the recordId key using substitute.

2. All the objects are sorted using JSONFormateElements so the value I want as the object key is at the top so substitute can do its magic.

3. Substitute changes the array to objects.

The resulting object was formatted like this.


  "data" :


  "100" :


  "fieldData" :


  "companyName" : "Another company name",


  "modId" : "11",

  "portalData" : {}


Now I am able to instantly get any record by recordId like so

JSONGetElement ( datafield ; ".data.1355" ) // 1355 begin the id of the record I want.


I am amazed at how fast the JSONFormatElements is able to take a huge amount of JSON and sort each object quickly.  At first I was kinda annoyed that it was sorting my objects by keys but this is a rather handy function for it.  Also the speed of the Substitute is amazing.


This has really made a process that took forever into a near instant result.  I am sure someone will have a better idea how to accomplish this in a better way and I look forward to hearing about it.  Also I would love to hear some feedback about if there is any flaws to this approach I haven't thought of yet.  I just thought it was a little unique and used some of the new features of FM16 in a creative way.