Hi Folks,
I have come up with a workaround for not being able to filter on query within scripting automations. Since you can query on a view but views can't be adjusted via the API either we use a view that has a checkbox and then check the box through update records and uncheck when done. This is useful when you have a list of records from a linked field more than a dyanamic filter. It is also still best to filter the records that are returned to ensure they do meet your criteria but greatly reduces the number of records returned.
First we use this async function to make the object for updates:
async function setAutomationFlags(table, recordIds, flagValue) {
const automationFlags = recordIds.map(recordId => ({
id: recordId.id,
fields: { "Automations": flagValue }
}));
await updateRecordsInChunks(table, automationFlags);
}
updateRecordsInChunks is another function that takes an array and slices it:
const chunkArray = function (arrayToChunk, chunkSize = 50) {
const arraysOfChunks = []
for (var i = 0; i < arrayToChunk.length; i += chunkSize)
arraysOfChunks.push(arrayToChunk.slice(i, i + chunkSize))
return arraysOfChunks
}
const updateRecordsInChunks = async function (table, records) {
const arrayOfChunks = chunkArray(records)
for (const chunkOfRecords of arrayOfChunks) {
const updateResult = await table.updateRecordsAsync(chunkOfRecords)
//console.info(`🔁 Updated ${chunkOfRecords.length} record(s) in the '${table.name}' table`)
}
}
and here is how it's called:
await setAutomationFlags(someTable, someRecords, true)
await setAutomationFlags(someTable, someRecords, null)
I hope this can be of use to others and would also welcome any questions or feedback. If you've come up with other workarounds that would be great to see as well.
Best case would be Airtable adding the filterByFormula as an option here though which would greatly improve the efficiency.
