Searching for user by email in AirTable database

Other Questions Airtable
1

Hey everyone! 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 AirTable API.

Here’s what I’ve tried so far:

const TableBase = require('dbtable');
const db = new TableBase({ key: process.env.DB_KEY }).table(process.env.DB_TABLE);

app.post('/registerUser', (req, res) => {
  const { id, name, email, avatar, authType } = req.query;
  db('members').search({formula: `FIND(email = '${email}')`}, (error, result) => {
    if (error) res.status(400).send(error);
    console.log('Found', result.id);
    db('members').update([{"data": { id, name, email, avatar, authType}}], (error, results) => {
      if (error) console.log(error);
      else console.log(results[0]) 
      res.status(200).send(results[0]._jsonData);
    });
  });
});

But I keep 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 here? Thanks in advance for any help!

2

I encountered a similar issue when working with AirTable’s API. The problem might be in how you’re constructing the search formula. Instead of using FIND(), try utilizing the SEARCH() function for case-insensitive matching. Here’s an adjusted version that should work:

db('members').select({
  filterByFormula: `SEARCH('${email}', {email}) > 0`
}).firstPage((err, records) => {
  if (err) {
    console.error(err);
    return res.status(400).send(err);
  }
  
  if (records.length > 0) {
    // User exists, update the record
    db('members').update(records[0].id, { fields: { id, name, email, avatar, authType } }, (updateErr, updatedRecord) => {
      if (updateErr) {
        console.error(updateErr);
        return res.status(400).send(updateErr);
      }
      res.status(200).send(updatedRecord);
    });
  } else {
    // User doesn't exist, create a new record
    db('members').create({ fields: { id, name, email, avatar, authType } }, (createErr, newRecord) => {
      if (createErr) {
        console.error(createErr);
        return res.status(400).send(createErr);
      }
      res.status(201).send(newRecord);
    });
  }
});

This approach should handle both cases: updating existing users and creating new ones if they don’t exist. Make sure to properly handle errors and send appropriate responses to the client.

3

hey liam23, had similar issues. try using ‘select’ with proper filter:

db('members').select({
  filterByFormula: `{email} = '${email}'`
}).firstPage((err, records) => {
  if (err) console.error(err);
  if (records.length > 0) {
    // user exists, update
  } else {
    // user doesn't exist, create
  }
});

this worked for me. hope it helps!

4

I’ve dealt with AirTable’s API quirks before, and it can be tricky. One thing that’s worked well for me is using the ‘select’ method with a more specific filter. Here’s a snippet that might help:

db('members').select({
  filterByFormula: `LOWER({email}) = LOWER('${email}')`
}).firstPage((err, records) => {
  if (err) {
    console.error('Error searching for user:', err);
    return res.status(500).send('Internal server error');
  }
  
  if (records.length > 0) {
    // User found, update their info
    db('members').update(records[0].id, { fields: { id, name, avatar, authType } }, (updateErr, updatedRecord) => {
      if (updateErr) {
        console.error('Error updating user:', updateErr);
        return res.status(500).send('Failed to update user');
      }
      res.status(200).json(updatedRecord);
    });
  } else {
    // User not found, create new record
    db('members').create({ fields: { id, name, email, avatar, authType } }, (createErr, newRecord) => {
      if (createErr) {
        console.error('Error creating user:', createErr);
        return res.status(500).send('Failed to create user');
      }
      res.status(201).json(newRecord);
    });
  }
});

This approach uses case-insensitive email matching and handles both updating existing users and creating new ones. Make sure to sanitize inputs and handle errors properly in production code.