(06-09-2020, 07:55 PM)mark_talluto Wrote: One of our beloved LiveCloud developers asked a great question about cdb_update. He wanted to know how to efficiently update only data that has changed. Below is a copy of the email response. Please let us know if you have any questions about this solution.

Suppose the following array (record):

tInputA =
[“firstName”]
[“lastName”]
[“age”]
[“phone”]

Existing record id "435756d6-0576-4a86-8d01-7557168f6528" contains:

firstName: Tom
lastName: Perkins
age: 78
Phone: 123-456-7890

If you pass an array like: 
tInputA =
[“firstName”][“Tom”]
[“lastName”][“Perkins”]
[“age”][“78”]
[“phone”][“555-888-9999”]

This will push an update to all the keys in this array even though some of the data may not have changed.

If you pass an array like:
tInputA =
[“phone”][“555-888-9999”]

You are only touching the “phone” key. The other data will stay as it is. The cloud call will be smaller and more efficient (...marginally). The question is how do you know that the phone data has has been updated?

It sounds like you are looking to come up with a method to pass as little data as possible (optimization) by determining what has changed.
This can be an important optimization if your array contains many keys. Or, it you may have heavy data in some of the keys. LiveCloud is not able to determine what is new for you without making a round trip to the cloud and compare. Solving this in your app is best.

People solve this problem in different ways.

Method #1: Develop a flag system to track which fields have been touched by your app
Method #2: Use the set operations in LiveCode to compare the original array with the final version and see if changes have been made
Method #3: Send the full array up using cdb_update and let LiveCloud push both new and old data into place
Method #4: Pick up the changes in a field and call cdb_update as each field is updated with: closeField

I am going to supply you the code to do Method #2 as it is not always apparent how to do this. 
Drop this code into a single button. Put breakpoints into the code so you can walk through it. Once you get the hang of it, you can use this API in your own code.


—COPY ALL THE CODE BELOW TO A SINGLE BUTTON IN LIVECODE
//>GLOBALS
global gArrayA1, gArrayA2


on mouseUp
     local tInputA, tRecordID
     
     --BUILD THE TWO ARRAYS TO COMPARE
     buildArray1
     buildArray2
     
     --CALL CUSTOM API TO COMPARE THE TWO ARRAYS
     put compareTwoArrays(gArrayA1,gArrayA2) into tInputA
     
     --WE NEED TO CONVERT THIS ARRAY
     --THE KEY AND VALUE NEED TO BE IN THE RIGHT PLACE
     combine tInputA with lf and empty
     split tInputA with lf and comma

     --RECORD TO BE UPDATED
     put "435756d6-0576-4a86-8d01-7557168f6528" into tRecordID
     
     --DO CLOUD UPDATE
     cdb_update tInputA,"myTableName", tRecordID
end mouseUp


function compareTwoArrays pArray1, pArray2
     local tArrayA3
     
     /* BREAKPOINT THROUGH THIS TO SEE WHAT IS HAPPENING STEP BY STEP */
     
     --TURN ARRAYS INTO A STRING
     --WE ARE TAKING THE ELEMENT AND ITS VALUE AND SMASHING THEM TOGETHER
     --gArrayA1 WOULD BE YOUR CURRENT CLOUD OR LOCAL RECORD
     --gArrayA2 WOULD BE THE ARRAY CAPTURED FROM YOUR APP THAT MAY HAVE NEW OR UPDATED DATA
     combine pArray1 with lf and comma
     combine pArray2 with lf and comma
     
     --TURN STRING BACK INTO AN ARRAY
     --WE WANT THE COMBINED KEY AND VALUE TO BE THE NEW ELEMENT
     --THIS WILL MAKE IT EASY TO COMPARE THE TWO ARRAYS
     --IT IS INTENDED THAT THE VALUE WILL BE EMPTY
     split pArray1 with lf and empty
     split pArray2 with lf and empty
     
     --WE DO A SET OPERATION TO FIND WHICH DATA IS NEW
     --DIFFERENCE IS ONLY LOOKING AT THE ELEMENT, NOT THE VALUE OF THE KEY WHEN DOING ITS OPERATION
     --WHATEVER IS LEFT IN THE tArrayA3 ARRAY IS NEW
     difference pArray2 with pArray1 into tArrayA3
     
     return tArrayA3
end compareTwoArrays


on buildArray1
     /* CHANGE THIS ARRAY TO MATCH YOUR TABLE */
     put "Tom" into gArrayA1["firstName"]
     put "Perkins" into gArrayA1["lastName"]
     put "78" into gArrayA1["age"]
     put "123-456-7890" into gArrayA1["phone"]
end buildArray1


on buildArray2
     /* CHANGE THIS ARRAY TO MATCH YOUR TABLE */
     put "555-888-0000" into gArrayA2["phone"]
end buildArray2