Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Vlookup script help

Topic Labels: Scripting extentions
1095 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Joe_Pepper
4 - Data Explorer
4 - Data Explorer

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
Jono_Prest
6 - Interface Innovator
6 - Interface Innovator

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?