Locating user records in AirTable using email address

Other Questions Airtable
1

I’m working on a project where I need to check if a user exists in my AirTable database by looking up their email. If they’re not there, I want to add them. But I’m running into some issues with the API.

Here’s what I’ve tried so far:

const AirTableAPI = require('airtable-wrapper');
const db = new AirTableAPI({ key: process.env.API_KEY }).connectTo(process.env.DB_ID);

app.post('/registerUser', (req, res) => {
  const { userId, name, userEmail, avatar, authMethod } = req.query;
  db.table('members').findOne({query: `SEARCH(userEmail = '${userEmail}')`}, (error, result) => {
    if (error) {
      res.status(500).json({ error: 'Database error' });
      return;
    }
    console.log('Found:', result.id);
    db.table('members').upsert([{data: { userId, name, userEmail, avatar, authMethod}}], (error, results) => {
      if (error) {
        console.error(error);
        res.status(500).json({ error: 'Update failed' });
      } else {
        res.status(200).json(results[0].raw);
      }
    });
  });
});

But I’m getting a ‘NOT_FOUND’ error. I also tried using ‘select’ instead of ‘find’, but that gave me a different error about the number of parameters.

Any ideas on what I’m doing wrong or how to fix this? I’ve checked the API docs but I’m still stuck. Thanks for any help!

2

In my experience dealing with AirTable API quirks, one common pitfall is the case-sensitivity of the SEARCH function. If the cases don’t match exactly, you might end up with unexpected results. I’ve found that using LOWER() on both the search term and the field can resolve this.

Also, filterByFormula typically provides a more reliable approach compared to directly querying with SEARCH. For instance, this snippet simplifies the process:

db.table('members').select({
  filterByFormula: `LOWER({userEmail}) = LOWER('${userEmail}')`
}).firstPage((err, records) => {
  if (err) {
    console.error(err);
    return;
  }
  if (records.length > 0) {
    // Update existing record
  } else {
    // Create a new record
  }
});

This method has helped me overcome similar challenges, and I hope it brings you closer to your solution.

3

I’ve encountered similar issues with AirTable’s API before. One thing that often trips people up is the structure of the filterByFormula parameter. Instead of using SEARCH, I’ve had better luck with the FIND function. Here’s a modification that might work for you:

db.table('members').select({
  filterByFormula: `FIND('${userEmail}', LOWER({userEmail})) > 0`
}).firstPage((err, records) => {
  if (err) {
    // Handle error
  } else if (records.length > 0) {
    // User exists, update
  } else {
    // User doesn't exist, create new record
  }
});

This approach converts the email field to lowercase before searching, which helps avoid case-sensitivity issues. It’s also more efficient than SEARCH for exact matches. Remember to sanitize your inputs to prevent injection attacks. Hope this helps!

4

hey there ExcitedGamer85! looks like ur using the wrong syntax for the SEARCH function. try this instead:

{SEARCH('${userEmail}', {userEmail})}

also, make sure ur field names match exactly whats in airtable. if that dont work, maybe try using filterByFormula instead of query. good luck!