Airtable's API filterByFormula to get a record by a unique field value (example email or username)
Tags: airtable, node, express
Published: 2019-05-07 13:59:46

---
title: Airtable's API filterByFormula to get a record by a unique field value (example email or username)
published: true
description: Using filterByFormula to get a record by a unique field value
tags: Airtable, NodeJs, ExpressJs
canonical_url: https://chinarajames.com/airtable-filterbyformula-to-get-a-record-by-a-unique-field-value/
---

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](https://support.airtable.com/hc/en-us/articles/203255215-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.

```javascript

// 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.

```javascript
// 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.

```javascript
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.