Airtable filterByFormula: match records containing a substring with SEARCH

TL;DR

Use SEARCH("your string", {Column Name}) > 0, which returns every record where the column contains the string. When you need a contains-match on an Airtable column, dont use an equality comparison in filterByFormula.

Steps

1. When you need a contains-match on an Airtable column, dont use an equality comparison in filterByFormula. Use SEARCH("your string", {Column Name}) > 0, which returns every record where the column contains the string. In code that looks like base('Table').select({filterByFormula: 'SEARCH("term", {Name}) > 0'}). The position argument defaults to 0 and the > 0 comparison keeps only rows where the substring was actually found.

When to use

You are seeing this: The position argument defaults to 0 and the > 0 comparison keeps only rows where the substring was actually found. Use this skill when you run into "Airtable filterByFormula: match records containing a substring with SEARCH".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Source: Stack Overflow #42429056 (accepted answer, 5 votes): the asker used filterByFormula to find Airtable records where a column contains a given string, but the query only returned rows holding exactly that string. The accepted answer points to the SEARCH formula function: SEARCH(findstring, withinstring) returns the index of the first match, so SEARCH("myFilter", Column) > 0 matches any record whose column contains the string anywhere.

Verified resolution: When you need a contains-match on an Airtable column, dont use an equality comparison in filterByFormula. Use SEARCH("your string", {Column Name}) > 0, which returns every record where the column contains the string. In code that looks like base('Table').select({filterByFormula: 'SEARCH("term", {Name}) > 0'}). The position argument defaults to 0 and the > 0 comparison keeps only rows where the substring was actually found.

Source URL: https://stackoverflow.com/questions/42429056/how-do-you-filter-an-airtable-record-that-contains-a-given-string-but-may-also-c