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