Loop to update records in table A if a matching record is found in table B

[Michael_Unrath]

Hello all,

I know this looping question has been posed in various forms over the years, but I could use a gentle nudge in the right direction as my research has reached an impasse.

I have two tables - the 'primary' table has ~1200 records, and the 2nd table has ~800 records.  I'd like to create a script to compare the two tables and update field XXL2 in the primary table with the string 'XXX' if a matching record in the 2nd table matches a field in the primary table.

I have seen that I need to populate an array with the 'found' results and then cycle through updating the results using slices of 50, but I know there is more to it to ensure that the code completes within the 30-second timeout.

What I have thus far is:

let uncontractedComponentsTable = base.getTable("Uncontracted Components Report");
let vsaCancellationsTable = base.getTable("VSA Cancellations");
let updates = [];

// grab the records from the Uncontracted Components Report table
let uncontractedComponentsRecords = await uncontractedComponentsTable.selectRecordsAsync();

// grab the records from the VSA Cancellations table
let vsaCancellationsRecords = await vsaCancellationsTable.selectRecordsAsync();

// loop through each record in the VSA table
for (let vsaRecord of vsaCancellationsRecords.records) {
  let cancelledTour = vsaRecord.getCellValue("CancelledTour");

  // loop through each record in the Uncontracted Comp table
  for (let uncontractedRecord of uncontractedComponentsRecords.records) {
    let record = uncontractedRecord.id;
    let depCode = uncontractedRecord.getCellValue("Dep. Code");
    

    // if the fields match, update the array with the recordID and XXX
    if (cancelledTour === depCode) {

      updates.push({
        id: record,
        fields: { "XXL2": "XXX"}
      })

    }
      
  }
    while (updates.length > 0) {
  await vsaCancellationsTable.updateRecordsAsync(updates.slice(0, 50));
  updates = updates.slice(50)
}
}

In a previous iteration, it worked when I limited the records in the 2nd table (VSA Cancellations) to just one or two. 

The above code now throws an error "Could not find a record with ID 'xxxxxxx' " .... yet the ID does exist. 

Can anyone point me in the right direction, please?

[Alexey_Gusev]

Hi,

I think, it's time for you to begin to implement ES6 features. You will avoid 1200*800 loops and write less code. Instead of atomizing program flow to separate variables, use array transforming and array functions.
Thus, you will not miss the fact you are trying to update second table using record id from first table))
I will not write working code, but I suggest you to do that and improve you JS skills for future use.
Under let vsaCancellationsRecords = await..... , instead of for loops:

At first, arrow functions - is a key element for other stuff to work.

example:   let double =  x => x*2  (underscored is arrow function).      

You need other one :  record=>record.getCellValue('ABC')   
then use  map     (it passes each array member through function and returns a new array,  [1,2,7,4].map (double) = [2,4,14,8] ) 

in following way:    let values_to_compare =  some_query.records . map ( arrow function with getCellValue )  to get an array of 800 values from 2nd table

and then apply  filter  &  includes:

let records_to_change=uncontractedComponentsRecords.filter

( rec=>values_to_compare.includes(rec.getCellValue('1st_table_field'))

I recommend you to temporary insert lines like 

output.inspect(records_to_change)   after each transformation to fully understand what's happening

Now final transformation:

let updates=recs_2_change.map(record=>{

  id : record.id,

  fields: { ...xxx....}

})

then continue with  while (updates.length....   (the last part can be improved too but that's another story..) 

[Michael_Unrath]

Hello @Alexey_Gusev - thank you for your reply and suggestions.  It is appreciated.  

I have started down the path using maps, array transforming and functions ... with plenty of console logging!

Cheers!