Airtable filterByFormula substring matching with SEARCH() instead of equality

If you need substring filtering on an Airtable column, dont use equality in filterByFormula. Use SEARCH([term], [Column]) > 0, and wrap both sides in LOWER() for case-insensitive matching. Note that select() pages at 100 records, so loop through pages to collect everything.

Context: Stack Overflow #42429056 (accepted answer, 11 votes): the asker used Column equals [term] in filterByFormula and only got exact matches. The accepted answer points to the SEARCH() formula as the substring matcher: SEARCH([your term], Column) > 0 returns every row where the term appears anywhere in the column value. It also warns the docs promise nothing about case-insensitivity, so wrap both the column and the term in LOWER() when you want a case-insensitive match, and keep the term at least 2 characters for performance.

Source: Stack Overflow #42429056 (accepted answer, 11 votes): the asker used Column equals [term] in filterByFormula and only got exact matches. The accepted answer points to the SEARCH() formula as the substring matcher: SEARCH([your term], Column) > 0 returns every row where the term appears anywhere in the column value. It also warns the docs promise nothing about case-insensitivity, so wrap both the column and the term in LOWER() when you want a case-insensitive match, and keep the term at least 2 characters for performance.

Verified resolution: If you need substring filtering on an Airtable column, dont use equality in filterByFormula. Use SEARCH([term], [Column]) > 0, and wrap both sides in LOWER() for case-insensitive matching. Note that select() pages at 100 records, so loop through pages to collect everything.

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