AND filterbyformula does not filter

[saumya_garg]

AND filterbyformula does not filter
bring up all records instead. Anyone have any ideas?

let step_count_records =  await log.selectRecordsAsync({
      filterByFormula: `AND((
    {Email} ="` + email + `"),
    ({Action} = "` + status + `"),
)`,
});

[kuovonne]

Welcome to the Airtable community!

filterByFormula is not available in scripting. It is only available with the REST api.

You will get all records with selectRecordsAsync and then you filter yourself with JavaScript.

[Dominik_Bosnjak]

Yeah, and while remoteFetchAsync allows you to ping any publicly shared base, the performance hit is a way bigger hassle than learning how to just manipulate the data yourself inside the Scripting block.

[Olpy_Acaflo]

Hi @saumya_garg ,

You can also limit the number of records to be filtered in javascript by first creating a filtered view and then acquiring only the records that are visible in that view!

Examples from Scripting App API
“Select records from the view.” :

EXAMPLE

// query for every record in "By Project"
let table = base.getTable("People");
let view = table.getView("By Project");
let query = await view.selectRecordsAsync();
console.log(query);

EXAMPLE

// query for every record in "Kanban"
let table = base.getTable("Tasks");
let view = table.getView("Kanban");
let query = await view.selectRecordsAsync({
    sorts: [
        // sort by "Priority" in ascending order...
        {field: "Priority"},
        // then by "Status" in descending order.
        {field: "Status", direction: "desc"},
    ]
});
// print ID & "Priority" from each record:
for (let record of query.records) {
    console.log(`
**${record.id}**
${record.getCellValueAsString("Priority")}
`);
}

olπ

[Bill_French]

Olpy_Acaflo:

then acquiring only the records that are visible in that view!

Very elegant. Nice work.

[Bill_French]

Dominik_Bosnjak:

the performance hit is a way bigger hassle than learning how to just manipulate the data yourself inside the Scripting block.

Yep - if the data is relatively small and the query is relatively straightforward involving fields from the same table, do it yourself in script - it’s fast and simple.

For complex data models and where queries are arbitrary across multiple tables/fields (and even multiple bases and workspaces), I use these approaches.

But in a few cases, I have needed sub-second filtering performance across large tables and an inverted index (Lunr - a derivative of ElasticSearch) has provided great relief despite the added overhead of maintaining the index which can be managed inside or external to Airtable.