Help with simple code block

[Jeff_Miller3]

Hi,

I’m trying to run the below VLOOKUP type script. When I type it in I get a strikethough on mainTable.selectRecordsAsync - I’m not a programmer and wondering 1) if this is why my script is not running and 2) how to fix it.

Any help is appreciated.

Thanks!

let mainTable = base.getTable(“Price Book”);
let mainTableRecords = await mainTable.selectRecordsAsync();

let lookupTable = base.getTable(“Inventory”);
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

for (let record of mainTableRecords.records) {
let lookupValue = record.getCellValue(“Number”);

for (let rangeRecord of lookupRangeRecords.records) {
if (rangeRecord.getCellValue(“Item”) === lookupValue) {
let returnValue = rangeRecord.getCellValue(“CA INV”);

        await mainTable.updateRecordAsync(record, {
            "CA Inv": returnValue
        });
   }
}

}

[Justin_Barrett]

Welcome to the community, @Jeff_Miller3! :grinning_face_with_big_eyes: That particular syntax for .selectRecordsAsync() is deprecated. Long story short, that syntax used to grab all fields for all records, but now the preferred (and soon-to-be required) method is to specify exactly which fields you want to retrieve. This makes the whole process faster, especially for tables with lots of fields.

A couple other threads have already discussed this; this is the first that I could find after a quick search:

Is selectRecordsAsync Deprecated in automations script? Ask the community

Hi @Claire_Conza - if you scroll down a bit further on the pop-up you will see the deprecation notice: [Screenshot 2021-09-16 at 11.16.51] So your script will continue to work for now, but will fail at some point in the future. You can future proof it now by passing in the fields you need: let table = base.getTable('Table 1'); let result = await table.selectRecordsAsync({fields: ['Name', 'Notes']}); Have a look at this post too, which passes all fields easily:

[Jeff_Miller3]

Thanks so much Justin.

I’m sorry to be so inexperienced, but below is what I’ve entered based on this new syntax. I’ll post a screenshot also. It would be fun to be able to run things like this as a newbie to anything programming related, so I’m determined to get it figured out :slightly_smiling_face:

…also you’ll find a screenshot of each table.

Basically I’m looking to match the six digit item numbers to pull the inventory value associated with each item number in the field “CA INV” in the “Inventory” sheet, and set it next to the value in the “Price Book” Sheet in the “CA Inv” field there.

…and from there the next and final step would be to have this run continually.

Thanks in advance for your help

let mainTable = base.getTable(‘Price Book’); let mainTablerecords = await table.selectRecordsAsync({fields: [‘Number’, ‘CA Inv’]});

let lookupTable = base.getTable(“Inventory”); let lookupRangeRecords = await table.selectRecordsAsync({fields: [‘Item’, ‘CA INV’]});

for (let record of mainTableRecords.records) {
let lookupValue = record.getCellValue(‘Number’);

     for (let rangeRecord of lookupRangeRecords.records) {
    if (rangeRecord.getCellValue('Item') === lookupValue) {
        let returnValue = rangeRecord.getCellValue('CA INV');

        await mainTable.updateRecordAsync(record, {
            'CA Inv': returnValue
        });
   }
}

}

[Jeff_Miller3]

Here is my latest try at this - There are no errors now but the circle just keeps spinning once I run it with a saving…all changes saved…saving…all changes saved happening over and over with no data actually populating. Any help on the forum here is very appreciated. Thanks

[Jono_Prest]

Hi @Jeff_Miller3 ,

I can’t find a bug in your code regarding the latest picture you posted. In the previous pasted code you were referencing table.selectRecordsAsync() rather than mainTable.selectRecordsAsync() so that could have caused some problems but it appears you have fixed that. I’m not sure what’s causing the continuous spinning - unless you have huge amounts of records? You are performing a nested loop so if there is significant amounts of data it could take some time.

A general debugging tip is to console log values in your script and see what values you are getting back. For instance:

let mainTablerecords = await mainTable.selectRecordsAsync({
  fields: ["Number", "CA Inv"],
});

console.log(mainTablerecords);

let lookupTable = base.getTable("Inventory");
let lookupRangeRecords = await lookupTable.selectRecordsAsync({
  fields: ["Item", "CA INV"],
});

console.log(lookupRangeRecords);

for (let record of mainTablerecords.records) {
  let lookupValue = record.getCellValue("Number");

  for (let rangeRecord of lookupRangeRecords.records) {
    if (rangeRecord.getCellValue("Item") === lookupValue) {
      let returnValue = rangeRecord.getCellValue("CA INV");
      console.log(returnValue);

      await mainTable.updateRecordAsync(record, {
        "CA Inv": returnValue,
      });
    }
  }
}

You might find you are not getting matches as you are expecting to or you are not getting back the correct data shape you are expecting etc.

I would also suggest that what you are currently trying to do is a job for a linked field and lookup fields. I obviously don’t know your workflow but it would be beneficial to establish a link between your tables with common data and then look up various field values that you need between the two. Linked fields also allow you to perform rollups, aggregations and various useful things. You may need to set up a similar script to initially set up the linked fields.

[Justin_Barrett]

Jono_Prest:

I’m not sure what’s causing the continuous spinning - unless you have huge amounts of records?

That’s my guess. Updating records one at a time in a large record set will take a loooooooong time, and as you’re seeing @Jeff_Miller3 , Airtable will happily show you the progress of each individual record update. Even though it says “All changes saved”, it does that even when only a single record is updated. If you scrolled elsewhere in your table, you would see the records updating one at a time…very…very…slowly.

The recommended way to update large batches of records is to store all updates into an array, and then use a loop to pass them in batches of 50 to the .updateRecordsAsync() method.

Before the start of your outer loop, create an empty updates array:

let updates = [];

Instead of updating each record as it’s found, store the update in the array. Replace the current update lines with this:

      updates.push({
        id: record.id,
        fields: {"CA Inv": returnValue}
      });

Once both loops are done, the updates array will contain all necessary updates, which can be batch-updated with this:

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

Here’s the script that @Jono_Prest shared with the above changes applied. I’ve commented out the logging lines, as those will slow down the script execution if you’re processing a ton of records; feel free to uncomment them if you want to check the script progress, but my gut says that the batch processing will take care of the issue.

let mainTablerecords = await mainTable.selectRecordsAsync({
  fields: ["Number", "CA Inv"],
});

//console.log(mainTablerecords);

let lookupTable = base.getTable("Inventory");
let lookupRangeRecords = await lookupTable.selectRecordsAsync({
  fields: ["Item", "CA INV"],
});

//console.log(lookupRangeRecords);

let updates = [];
for (let record of mainTablerecords.records) {
  let lookupValue = record.getCellValue("Number");

  for (let rangeRecord of lookupRangeRecords.records) {
    if (rangeRecord.getCellValue("Item") === lookupValue) {
      let returnValue = rangeRecord.getCellValue("CA INV");
      //console.log(returnValue);

      updates.push({
        id: record.id,
        fields: {"CA Inv": returnValue}
      });
    }
  }
}

// Process all updates in batches of 50
while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

[Xavier_G_Sabate]

Hi @Jeff_Miller3

You may use a different approach to look for the right value, it is possible to substitute the inner loop for a filtering on the recordset.
If you do that, you transfer the burden of finding the record to the API instead to your code. This fact will result in shorter execution time (I guess), more clarity on the code and a better debugging.

In your case I will code something like this script:

for(let record of mainTablerecords.records){
    let lookupValue = record.getCellValue("Number")

    let foundValue = lookupRangeRecords.records.filter(filt =>{
        return filt.getCellValue("Item") === lookupvalue;
    })
    await mainTable.updateRecordAsync(record, {"CA Inv"}: foundValue.getCellValue("CA INV"))
}

You may need to fine tune this code, but I think that the idea behind is good.
Let me know if you try it. Good luck

[Jono_Prest]

Xavier_G_Sabater:

let foundValue = lookupRangeRecords.records.filter(filt =>{
        return filt.getCellValue("Item") === lookupvalue;
    })
    await mainTable.updateRecordAsync(record, {"CA Inv"}: foundValue.getCellValue("CA INV"))

Running the .filter array method will return an array. So you won’t be able to call .getCellValue on foundValue. You will have to iterate once again with a block like this:

for (let value of foundValue) {
await mainTable.updateRecordAsync(record, {"CA Inv"}: value.getCellValue("CA INV"))
}

Or you could just append your foundValue array (provided you format your filter return in the correct structure) to an array like @Justin_Barrett is suggesting and then call the updateRecordsAsync method in batches, which is certainly the most performant way to update large amounts of data.

[Xavier_G_Sabate]

You’re right,
The filtering will return an array. I normally use this method when I’m looking for a unique value and then do the do the updateRecordAsync on the foundValue[0] element.

The updateRecordsAsync in batches is indeed the most performant.