Skip to main content

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

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

 

 

 

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!

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!


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

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. 🙂

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. 🙂


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

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


@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.

Reply


Terms & Conditions