Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Having trouble filtering in automation script

Topic Labels: Scripting
Solved
Jump to Solution
167 6
cancel
Showing results for 
Search instead for 
Did you mean: 

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

1 Solution

Accepted Solutions
Karlstens
10 - Mercury
10 - Mercury

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

 

 

 

See Solution in Thread

6 Replies 6
Karlstens
10 - Mercury
10 - Mercury

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!

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

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.