How to search for existing record by email address in Airtable API

Other Questions Airtable
1

I want to check if a customer already exists in my database by searching for their email address. If the email is not found, I need to create a new customer record.

const airtableLib = require('airtable');
const database = new airtableLib({ apiKey: process.env.API_KEY }).base(process.env.BASE_ID);

app.post('/createCustomer', (req, res) => {
  const { userId, fullName, emailAddress, profileImage, authProvider } = req.body;
  database('customers').find({filterByFormula: `FIND(emailAddress = '${emailAddress}')`}, function(error, result) {
    if (error) res.status(400).send(error);
    console.log('Found record', result.id);
    database('customers').replace([{"fields": { userId, fullName, emailAddress, profileImage, authProvider}}], function(error, results) {
      if (error) console.log(error);
      else console.log(results[0]) 
      res.status(200).send(results[0]._rawJson);
    });
  });
});

But I keep getting this error message:

AirtableError {
  error: 'NOT_FOUND',
  message: 'Could not find what you are looking for',
  statusCode: 404
}

I also tried using select method instead:

database('customers').select({filterByFormula: `FIND(emailAddress = '${emailAddress}')`}, function(error, result) {

This gives me a different error saying select only accepts one parameter and I should use eachPage or firstPage methods instead. What am I doing wrong with the formula syntax?

2

Your filterByFormula syntax is incorrect. In Airtable, the FIND function does not work the way you are using it. For exact matches, use {Email} = '${emailAddress}'. Alternatively, if you’re looking for substring matches, you could use FIND('${emailAddress}', {Email}). I recommend using the select method along with firstPage for better results, such as database('customers').select({filterByFormula: {Email} = ‘${emailAddress}’}).firstPage((err, records) => { ... }). Additionally, ensure that your field name matches exactly as it appears in your Airtable base, as Airtable is case-sensitive.

3

Been dealing with this exact issue in production. Your formula syntax and method choice are wrong.

Ditch the find method - it’s only for record IDs. Use select with proper field syntax:

database('customers').select({
  filterByFormula: `{emailAddress} = "${emailAddress}"`
}).firstPage((error, records) => {
  if (error) {
    res.status(400).send(error);
    return;
  }
  
  if (records.length === 0) {
    // Create new customer
    database('customers').create([{
      fields: { userId, fullName, emailAddress, profileImage, authProvider }
    }], (error, results) => {
      if (error) res.status(400).send(error);
      else res.status(200).send(results[0]._rawJson);
    });
  } else {
    // Update existing customer
    const recordId = records[0].id;
    database('customers').update(recordId, {
      userId, fullName, emailAddress, profileImage, authProvider
    }, (error, record) => {
      if (error) res.status(400).send(error);
      else res.status(200).send(record._rawJson);
    });
  }
});

Two key fixes: use double quotes in the formula instead of single quotes (way more reliable), and switch from replace to update since you’re modifying an existing record, not replacing the whole table.

Also double-check your Airtable field is actually named “emailAddress” and not “Email” or something else.

4

You’re mixing up API methods and using the wrong approach. find gets a single record by record ID - it doesn’t search by field values. Since you want to search by email, use select with firstPage:

database('customers').select({
  filterByFormula: `{emailAddress} = '${emailAddress}'`
}).firstPage((error, records) => {
  if (error) {
    res.status(400).send(error);
    return;
  }
  
  if (records.length > 0) {
    // Customer exists, update record
    console.log('Found existing customer:', records[0].id);
  } else {
    // Customer doesn't exist, create new record
    database('customers').create([{"fields": { userId, fullName, emailAddress, profileImage, authProvider }}], (error, results) => {
      // handle creation
    });
  }
});

Double-check that your field name in the formula matches exactly what’s in Airtable. You need those curly braces around the field name or the formula won’t work.