I have a table (“Containers”) with 3000+ records and for each record I would like to do a Vlookup from another table (“F”) in the same way as is done in Excel. I came across the below script. The problem is the script runs out of time before it can update every record so I have to run the script multiple times. It seems the script is looking through all 3000+ records every time and not zoning in on the records that have not yet been updated. The result is I got about 2500 records updated but now the script only finds 10 or so to update each time compared to 150 at the beginning.
I tried to use “Paul view” to limit to running the script only on un-updated records but it does not speed things up at all so maybe it is still running on all records.
//Substitute “Containers” for table name which contains values
//on which you want to run the vlookup
let mainTable = base.getTable(“Containers”);
let view = mainTable.getView(“Paul view”);
let mainTableRecords = await mainTable.selectRecordsAsync();
//Substitute “F” for table which contains range to search in
let lookupTable = base.getTable(“F”);
let lookupRangeRecords = await lookupTable.selectRecordsAsync();
//Replace “Date loaded” with column name which has the values you want to look up
for (let record of mainTableRecords.records) {
let lookupValue = record.getCellValue(“Date loaded”);
//Replace “Date” with column name which is the range to search in
//Replace “All freights” with columnn name which value should be returned
for (let rangeRecord of lookupRangeRecords.records) {
if (rangeRecord.getCellValue(“Date”) === lookupValue) {
let returnValue = rangeRecord.getCellValue(“All freights”);
//Replace “Vlookup freight” with column name from mainTable which should contain the link
mainTable.updateRecordAsync(record, {
“Vlookup freight”: returnValue
});
}
}
}
