Airtable's API filterByFormula to get a record by a unique field value (example email or username)

Airtable’s API provides a find method for retrieving a single record. However, this method can only take the record ID as a parameter to find the record. However, there are several use cases where the record ID is not known to us. In these cases, this method is not useful. So how do we retrieve a record based on some other field in the table? We use the select method and the filterByFormula parameter.

The filterByFormula parameter is self explanatory, it allows us to filter the results of the select method using a formula. You can use any formula that the Airtable formula field type supports as a filter. Airtable provides a handy Formula field reference.

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.

An Example (Get a user by email address)

I have abstracted the select method to a function and placed in a file called dataController.js. Note the second parameter options. The options parameter will take a object of the parameter we want to pass to the select method.

// dataController.js
exports.getAirtableRecords = (table, options) => {
  let records = [],
      params = {
        view: 'Grid view',
        pageSize: 15
      };

  Object.assign(params, options);

  return new Promise((resolve, reject) => {
    // Cache results if called already
    if (records.length > 0) {
      resolve(records);
    }

    const processPage = (partialRecords, fetchNextPage) => {
      records = [...records, ...partialRecords];
      fetchNextPage();
    };

    const processRecords = (err) => {
      if (err) {
        reject(err);
        return;
      }

      resolve(records);
    };

    table.select(params).eachPage(processPage, processRecords);
  });
};

Now imagine you have a page with a user login form with inputs for username and password. In my userController.js file I will create a function to get a user by email address.

// userController.js

exports.getUserByEmail = (req, res, next) => {
  const { username, password } = req.body;
  const options = {
    filterByFormula: `OR(email = '${username}', username = '${username}')`
  }

  data.getAirtableRecords(table, options)
    .then( users => {
      users.forEach(function(user) {
      // Call the next middleware (function) e.g validate password
        next();
      });
    })
    .catch(err => {
      console.log( Error(err));
    });
}

In the body request we now have access to the email address, available to us in req.body. I can now construct a formula comparing that value we received to the one in our database.

ES6 string interpolation makes creating the formula string easy. I have used the OR formula because my input can accept either email or username. From the field reference we can see what the OR logical operation does.

OR(logical1, [logical2, …])
Returns true if any one of the arguments is true.
Example OR(Finished, Reviewed)

And we know that filterByFormula will return the matching records if the result is not 0, false, “", NaN, [], or #Error!.

In plain English this formula is saying if the value of email in the table is the same as the value of the username form input OR if the value of the username in the table is the same as the value of the username form input then return true, if not return false.

filterByFormula: `OR(email = '${username}', username = '${username}')`

In another article I will demonstrate how to use Airtable to create a user database with authentication and sessions using the above.