Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Vlookup script help

Topic Labels: Scripting extentions
504 1
Showing results for 
Search instead for 
Did you mean: 

The script is below. I have to tables. Provider AIS API Info and Global APIs.

Field “Provider ID” from the Provider AIS API tab should be looked up in the Global APIs tab, in field Provider ID. The matched records should return the “Is it global?” Field back into the “Global API?” field of the Provider AIS API info tab.

The script is below:

let mainTable = base.getTable("Provider AIS API Info");
let mainTableRecords = await mainTable.selectRecordsAsync();

let lookupTable = base.getTable("Global APIs");
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

for (let record of mainTableRecords.records) {  
     let lookupValue = record.getCellValue("Provider ID");

    for (let rangeRecord of lookupRangeRecords.records) {
        if (rangeRecord.getCellValue("Provider Summary") === lookupValue) {
            let returnValue = rangeRecord.getCellValue("Is it Global?");
            await mainTable.updateRecordAsync(record, {
                "Global API?": returnValue
1 Reply 1

Welcome @Joe_Pepper !

Will need a little more info to help you debug. What is the issue or error you are getting when you run this?

For one, the .selectRecordsAsync() method is deprecated so you need to pass in an object with the fields you want to select from that table. .selectRecordsAsync({ fields: [ "Provider ID", "Global API?"] }) for mainTableRecords and .selectRecordsAsync({ fields: [ "Provider Summary", "Is it Global?"] }) for lookupRangeRecords.

Secondly, can you give us some info on what type of fields each of these are?