FilterByFormula - Exact match for Multi-Value Colu...

[LordPug]

Hi there,
I have this script I am working on to update all records in a table based on their currency. Except for records that are in stage 4. I am using this statement to filter through the records

filterByFormula: "AND(NOT({Currency} = ''), {Exchange} != '', NOT({Stage} = 'Stage 4'))"

The problem is that it ignores this parameter and rus it for all the records. I will be grateful for any help on how I can successfully filter out stage four records. Here is the full script.

const dealsTable = base.getTable('deals - test copy');
const currencyTable = base.getTable('test-currency exchange DB');


const deals = await dealsTable.selectRecordsAsync({
    filterByFormula: "AND(NOT({Currency} = ''), {Exchange} != '', NOT({Stage} = 'Stage 4'))"
});
console.log(deals);


const updates = deals.records.map(async (deal) => {
    const currencyType = deal.getCellValueAsString('Currency');
    const currencyField = currencyType.toLowerCase();
    const currencyValue = deal.getCellValue('Exchange');
    
    if (currencyField !== '' && currencyValue !== '') {
        const queryResult = await currencyTable.selectRecordsAsync({
            sorts: [{field: 'Timestamp', direction: 'desc'}],
            filterByFormula: `{test-currency exchange DB} = "${currencyField.toUpperCase()}"`,
            maxRecords: 1
        });

        const currencyRate = queryResult.records[0].getCellValue(currencyField.toUpperCase());

        
        return {
            id: deal.id,
            fields: {
                'Exchange Rate': currencyRate
            }
        };
    }
});


await Promise.all(updates).then((updateResults) => {
    return dealsTable.updateRecordsAsync(updateResults.filter((result) => result !== undefined));
});

[TheTimeSavingCo]

I think you need to encode the formula before use.  Here's your formula encoded using Airtable's API Encoder tool:

AND(NOT(%7BCurrency%7D+%3D+'')%2C+%7BExchange%7D+!%3D+''%2C+NOT(%7BStage%7D+%3D+'Stage+4'))

Relevant bit of the docs: