+2Hello 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.
Best answer by Fernando_Amorim
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().
+2It’s in the API docs. Pretty simple to use.
The documentation isn’t simple to use. I have read it 5 times already. Give an example if possible.
The documentation isn’t simple to use. I have read it 5 times already. Give an example if possible.
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.
+29Are 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
viewparameter, 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
viewparameter, 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
+2So… 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().
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.