Skip to main content
Solved

Having trouble filtering in automation script

  • January 12, 2023
  • 6 replies
  • 206 views
A
Forum|alt.badge.img+5

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

Best answer by Karlstens

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

 

 

 

6 replies

Karlstens
Forum|alt.badge.img+24
  • Karlstens
  • Brainy
  • 602 replies
  • Answer
  • January 12, 2023

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

 

 

 

A
Forum|alt.badge.img+5
  • Anthony_William
  • Author
  • New Participant
  • 4 replies
  • January 13, 2023

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
Forum|alt.badge.img+24
  • Karlstens
  • Brainy
  • 602 replies
  • January 13, 2023

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. 

Gather
Forum|alt.badge.img+5
  • Gather
  • Participating Frequently
  • 7 replies
  • January 26, 2023

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
Forum|alt.badge.img+24
  • Karlstens
  • Brainy
  • 602 replies
  • January 26, 2023

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.

Gather
Forum|alt.badge.img+5
  • Gather
  • Participating Frequently
  • 7 replies
  • January 27, 2023

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.


Terms & ConditionsAccessibility statement