How do you filter an airtable record that contains a given string, but may also contain others?

Question

Using the airtable api's filterByFormula method I can query and select records that contain one specific item (string). However, this query only returns the records that have ONLY that specific string.

Searching the airtable api formula documentation here: https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference yields no answers.

What I have:

base('Table').select({
    view: 'Main View',
    filterByFormula: `Column = "${myFilter}"`
}).firstPage(function(err, records) {
    if (err) { console.error(err); reject( err ); }

    records.forEach(function(record) {
        console.log(record.get('Another Column'));

    });

Again, this returns records that have ONLY myFilter, and not records that contain any additional values in the given Column.

edit: airtable refers to these columns as 'multiselect' field types.

Answer 1

It appears that airtable has support for the js equivalent of indexOf in the form of SEARCH. From the docs:

SEARCH(find_string, within_string, position)
Returns the index of the first occurrence of find_string in within_string, starting at position. Position is 0 by default.

As such, we can use the following as our search filter value

filterByFormula: `SEARCH("${myFilter}", Column) > 0`

This will search our Column for the presence of myFilter and return any result were myFilter is located at some position within the Column.

NB:It may be worth caveating, that your myFilter property is probably best to have a minimum character count of at least 2 for performance. The docs also do not specify an option for case insensitivity, so you may wish to wrap both Column and "${myFilter}" in a LOWER() function, if you want to return results regardless of upper or lower case characters. For example

filterByFormula: `SEARCH(LOWER("${myFilter}"), LOWER(Column)) > 0`