Issue with data update to the correct record

Ask the community
,
1

Hi,
I have an issue where I was unable to update the correct record in the collection if I am looping through multiple items and calling external webservice API to get some results to update to the respective records.

The update will update certain records with the correct data but some records are not updated with the correct data and some records are not updated at all even though it should be updated.

Below code in written in the main page to call my backend code to run the data query and update:

import { QueryUpdateStatusFunction } from ‘backend/collection’ ;

$w . onReady ( function () {
// Write your JavaScript here
const myCollection = “recycleOrder” ;

QueryUpdateStatusFunction ( myCollection ) 
. then (( results ) => { 
    console . log ( "status: "  +  results ); 
}) 
. **catch** (( error ) =>  console . log ( error ));

});

Below code is written in the backend to query data to get all items from collection and loop through the items and call the web service to get status and other fields updates from external website and update the data retrieved from the webservice call into the collection:

//backend/collection.jsw
import wixData from ‘wix-data’ ;
import { GetApiData } from ‘backend/fetch’ ;

export async function QueryUpdateStatusFunction ( myCollection ){
let results = await wixData . query ( myCollection )
.eq(“vinrequestid”, “109”)
.or(
wixData.query(myCollection)
.eq(“vinrequestid”, “039”)
)
.or(wixData.query(myCollection)
.eq(“vinrequestid”, “038”)
)
. find ();

**let**  allItems  =  results . items ; 

**while** ( results . hasNext ()){ 
    results  =  **await**  results . next (); 
    allItems  =  allItems . concat ( results . items ); 
} 

**const**  url  =  "url" ; 
**let**  item ; 
**let**  payloadData ; 
**let**  payloadDataJson ; 
**const**  options  = { 
    "suppressAuth" :  **true** , 
    "suppressHooks" :  **true** 
}; 
**let**  returnResults ; 
**for** ( **let**  i  =  0 ;  i  <  allItems . length ;  i ++){ 
    item  =  allItems [ i ]; 
    payloadData  = { 
        "RequestId" :  item . vinrequestid 
    }; 
    payloadDataJson  =  JSON . stringify ( payloadData ); 
    
    GetApiData ( url ,  payloadDataJson ) 
    . then (( results ) => { 
        **switch** ( results . StatusId ){ 
            **case**  0 : 
                item . status  =  "A" ; 
                **break** ; 
            **case**  1 : 
                item . status  =  "B" ; 
                **break** ; 
            **case**  2 : 
                item . status  =  "C" ; 
                **break** ; 
            **case**  7 : 
                item . status  =  "D" ; 
                **break** ; 
            **case**  3 : 
                item . status  =  "E" ; 
                **break** ; 
            **case**  5 : 
                item . status  =  "F" ; 
                **break** ; 
            **case**  6 : 
                item . status  =  "G" ; 
        } 
        
        **if** ( results . Pin ){ 
            item . status  =  "H" ; 
            item . pincode  =  results . Pin ; 
        } 
        
        **if** ( results . PinExpiryDate ){ 
            item . pinexpiry  =  **new**  Date ( results . PinExpiryDate ); 
        } 

        wixData . update ( myCollection ,  item ,  options ) 
        . then (( updateResults ) =>  updateResults ); 
    }); 
} 
returnResults  =  allItems . length ; 
**return**  returnResults ;

}

Below code in written in the backend to call external webservice and get the response:

//backend/fetch.jsw
import { fetch } from ‘wix-fetch’ ;
export function GetApiData ( url , payload ){
return fetch ( url , {
method : “post” ,
headers : {
“Content-Type” : “application/json”
},
body : payload
})
. then (( httpResponse ) => {
if ( httpResponse . ok ){
return httpResponse . json ();
} else {
return Promise . reject ( “Error” );
}
});
}

2

using .update (or.save, or.insert) inside a loop is a known source for trouble: lots of i/o over the line, some might get lost into cyberspace. To solve it, use .bulkUpdate (see wixdata docs).
Don’t use 1000 rows at a time, use chunks of 25 or 50, maybe 100. Doing smaller chunks gives to most reliable result.
Also, (bulk)Update does not patch a row, it overwrites it. So if it holds 3 fields with data, and you only provide a value for 1 field, the other 2 will be overwritten with empty data.

3

Thanks for the advice to use bulkUpdate but is there a good suggestion how to update all the rows in the collection if I have, for example, 110 rows to be updated and I use bulkUpdate to update 50 rows at a time. How to ensure I update all the 110 rows?

4

well, you set a counter (110), every batch you subtract 50, you check every time if the remainder is still >= 50, and if not the last batch size is the remainder (10).

5

Thanks for the advice. I placed the bulkUpdate in a do while loop to check if there is hasNext() in the collection so that I can call bulkUpdate again if hasNext() is true but the bulkUpdate results are not updating all the rows correctly with the updated data.

So it seems like I cannot put the bulkUpdate in any kinds of loop.