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

[Rory_Crew]

Exchange rate table
An exchange rate is given for each currency, within each month’s data rage

Project details tables
Shows the project start date and currency

I’m trying to write a script that:

• Looks at each project ID, its currency and start date
• Then looks at the Exchange rate table, matches the currency and finds the right date range that the Project start date fits into
• (The exchange rate table only has rates for past months. So if the Project start date isn’t in a date rate in the table, it should take the latest date for that currency)
• Then in the Project details table includes the correct exchange rate in the field Exchnage rate

My current code is:

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
})
}
}
}

[kuovonne]

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.

[Rory_Crew]

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!

[Alexey_Gusev]

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.