Script Lookup with multiple criteria including a date rate, using mainTable.selectRecordsAsync

Welcome to the Airtable community!

You state what you want the script to do, but you do not state what actually happens or where you want help.

Your script is difficult to read because all of your lines of text start at the left margin. It is much easier to read the code if you indent your code to show which blocks of code belong together.

If you are not getting the result you expect, I suggest liberal use of console.log() to see the actual runtime values of your variables.

Thanks Kuvonne,

I’m seeking help to update the script I repurposed from Greg Vonf’s excellent post. The updates summarised are:

• lookup two criteria (instead of one)


• one lookup matches a lookup date to a date range

Here’s the code with the formatting maintained

let mainTable = base.getTable("Project details");

let maintTableRecords = await mainTable.selectRecordsAsync({fields:["Project start date"]});

let lookupTable = base.getTable("Budgeting exchange rates");

let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["Start date","Exchange rate"]});

for (let record of maintTableRecords.records) {

    let lookupValue = record.getCellValue("Project start date");

    for (let rangeRecord of lookupRangeRecords.records) {

        if (rangeRecord.getCellValue("Start date") === lookupValue) {

            let returnValue = rangeRecord.getCellValue("Exchange rate");

            await mainTable.updateRecordAsync(record, {

                "Exchange rate": returnValue

            })

        }



    }

}

Thanks all!

let mainTable = base.getTable("Project details");

let maintTableRecords = await mainTable.selectRecordsAsync({fields:["Project start date"]});

let lookupTable = base.getTable("Budgeting exchange rates");

let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["Start date","Exchange rate"]});

for (let record of maintTableRecords.records) {

    let lookupValue = record.getCellValue("Project start date");

    for (let rangeRecord of lookupRangeRecords.records) {

        if (rangeRecord.getCellValue("Start date") === lookupValue) {

            let returnValue = rangeRecord.getCellValue("Exchange rate");

            await mainTable.updateRecordAsync(record, {

                "Exchange rate": returnValue

            })

        }



    }

}

It’s like usual lookup. but a bit updated.

I can’t find you comparing currency, so I’ve added it in following way:

I don’t know how you set USD and GBP in other table, because ‘EURO’ != ‘EUR’.

also, Single Select returns object



that’s why I check if text value includes property name of it, instead of check by “===”

const mainTable = base.getTable("Project details");

const lookupTable = base.getTable("Budgeting exchange rates");

const [XR,CUR,PCUR,SD,PSD]=["Exchange rate","Currency","Project currency","Start date","Project start date"]

const projects = await mainTable.selectRecordsAsync({fields:[PCUR,PSD]});

const rates=await lookupTable.selectRecordsAsync({fields:[CUR,SD,XR],sorts:[{field:SD,direction:'desc'}]});

const updRate=(proj, rate) => ({ id:proj.id, fields:{[xr]:rate} })

const compare=(proj,tab)=>(proj.getCellValue(PCUR).includes(tab.getCellValue(CUR).name))

const money=rec=>rates.records.filter(x=>compare(rec,x))

const findRow=rec=>money(rec).find(x=>x.getCellValue(SD)==rec.getCellValue(PSD)) ||money(rec)[0]

const getRate=x=>findRow(x).getCellValue(XR);

const updates=projects.records.map(p=>updRate(p,getRate(p)))

while (updates.length) await mainTable.updateRecordsAsync(updates.splice(0, 50))

I took mine lookup script for edit, where I use aliases to avoid hardcoding fieldnames in code. Rates table sorted by date descending to get defaul latest rate at he beginning of lookup array.