Having trouble filtering in automation script

[Anthony_William]

Hello!  I am in desperate need of help. I can't figure out how to "filterByFormula" in the following script. I am trying to send a JSON payload via webhook to MAKE.com. I only want to send records where a given field is blank. I have tried multiple variations of the filter but can't figure out why it always sends EVERY record.

let table = base.getTable("BA Aggregated Calendar 2022-2023");
table.selectRecordsAsync({
fields: ["Event ID", "Outlook Category", "Users"],
filterByFormula: "blank(Outlook Category)",
sorts: [
// sort by "Users" in ascending order...
{field: "Users"},
]
}).then(query => {
const payload = {
records: query.records.map(record => ({
id: record.id,
eventID: record.getCellValueAsString("Event ID"),
outlookcategory: record.getCellValueAsString("Outlook Category"),
Users: record.getCellValueAsString("Users")
}))
};

const options = {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify(payload)
};

fetch('webhookhere', options);
});

[Karlstens · Accepted solution]

I would approach this task like this, by using "Await". (note, my code might not work exactly, but hopefully you'll get the idea.)

EDIT: Ahh, I think I know what's wrong - If you're within an Automation Script, I don't think filterByFormula is available for use. The filterByFormula  optional is detailed in the API help, but it's not detailed within the Airtable Automation Script API.

You'll need to chain in a JavaScript filter before your map, I put in a dummy filter that you'll need to update to suit your blank/null Outlook field check.

Hopefully this helps!

let table = base.getTable("BA Aggregated Calendar 2022-2023");
let myCalendarData = await table.selectRecordsAsync({
    fields: ["Event ID", "Outlook Category", "Users"],
    filterByFormula: "blank(Outlook Category)",
    sorts: [{field: "Users"}]
})

console.log(myCalendarData)

const payload = {myCalendarData.records
    .filter( record => record.name === "Some Magic Filter String")
    .map(record => ({
        id: record.id,
        eventID: record.getCellValueAsString("Event ID"),
        outlookcategory: record.getCellValueAsString("Outlook Category"),
        Users: record.getCellValueAsString("Users")
    }))
};

[Anthony_William]

Nailed it. I haven't figured out the "magic filter string" but filters I have tried in there are working as intended as opposed to the previous method. Thank you so much!

[Karlstens]

Not a problem - happy to hear you’ve made progress. If you find yourself stuck, reach out again. 

[Gather]

Curious to hear when/if filterByFormulas will be supported within Automation Scripts directly. It would make my Automation’s code and steps much more simple (and more resilient). More of a feature request / desire to subscribe for related updates. 🙂

[Karlstens]

That would have been a great question for the just passed Airtable Ask-Me-Anything. I unfortunately couldn’t make it due to timezone.

[Gather]

@Karlstens I did ask it there yesterday, but they didn’t get to answering that question, though they did say that improving Automation Scripting in general is a Big priority of theirs for 2023 and I can’t imagine that it wouldn’t include adding the ability to filter requests somehow... maybe they’re also working on improving how filters are composed too? We’ll see.