How to return create filterByFormula that uses WHERE, AND?

[Fernando_Amorim]

Hello there!

I was trying to create an filter that acts like this SQL:
SELECT * FROM table t
WHERE t.Status = “Pending” AND t.Name == “Robert” AND … (and so on…).

How can I translate that to Airtable Filter By Formula in the API? I just want to return the records that match those conditions.

Using airtable.js to do it.

[Bill_French]

Fernando_Amorim:

How can I translate that to Airtable Filter By Formula in the API?

It’s in the API docs. Pretty simple to use.

[Fernando_Amorim]

The documentation isn’t simple to use. I have read it 5 times already. Give an example if possible.

[Bill_French]

Fernando_Amorim:

The documentation isn’t simple to use. I have read it 5 times already.

I assume you have a working API integration without the filtering feature implemented, right? If so, paste your working example here, and I’ll show you how to add the filterByFormula feature.

[kuovonne]

Are you using curl or JavaScript? While the formula will be the same, how you send the formula to the API is different.

Here is the JavaScript documentation from api.airtable.com

A formula used to filter records. The formula will be evaluated for each record, and if the result is not 0 , false , "" , NaN , [] , or #Error! the record will be included in the response.

If combined with the view parameter, only records in that view which satisfy the formula will be returned.

For example, to only include records where Name isn’t empty, pass in NOT({Name} = '') as a parameter like this:

filterByFormula: “NOT({Name} = ‘’)”

Here is the curl documentation from api.airtable.com

A formula used to filter records. The formula will be evaluated for each record, and if the result is not 0 , false , "" , NaN , [] , or #Error! the record will be included in the response.

If combined with the view parameter, only records in that view which satisfy the formula will be returned.

For example, to only include records where Name isn’t empty, pass in NOT({Name} = '') as a parameter like this:

filterByFormula=NOT%28%7BName%7D%20%3D%20%27%27%29

[Fernando_Amorim · Accepted solution]

So… As you didn’t read my question properly, I’m here to answer it.

Using the airtable package, airtable.js, I’d reached what I wanted using the filterByFormula inside the select() function with a custom function that concatenates the fields I want to filter by using the AND() clause.

Here’s the solution:

function generateFilterWhereField(field, data){
  var filter = "{" + field + "} = \"" + data + "\"";
  return filter;
}

function generateAndFilter(fieldsAndValues){
  var filter = "AND(";
  for(let key in fieldsAndValues){
    console.log(key);
    filter += generateFilterWhereField(key, fieldsAndValues[key]);
    filter += ',';
  }
  return filter.substring(0,filter.length-1) + ")";
}

It helps identify a single record inside your table, when you don’t have the Hash ID information to use .find().