Help

List records API with filterByFormula: find email address with a '+' fails (like bob+1@gmail.com)

Solved
Jump to Solution
432 3
cancel
Showing results for 
Search instead for 
Did you mean: 
scubachris
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

I'm attempting to locate a know record with an email address containing a "+".  I've tried introducing a '\' and '\\' before the '+' to no avail.  Tried CURL with your API encoder, no luck.

CURL EXAMPLE:

curl -H "Authorization: Bearer patZZZZZZ" https://api.airtable.com/v0/appXXXXX/tblYYYYY? filterByFor...

I can get results with an email without a '+' character in nodeJS and CURL.

How do I specify a filter that enables the retrieval of a record by email address containing a plus?  This does NOT work:

const header = {
   maxRecords: 1,
   filterByFormula: `{Email}="${email}"`,
   fields: [
      "Record ID"
   ],
   view: "BulkEmail"
}



 

1 Solution

Accepted Solutions
scubachris
5 - Automation Enthusiast
5 - Automation Enthusiast

 

@dilipborad:  First, thanks so much for looking at this and trying to help!

I was not clear on what I'm trying to accomplish.  I want to look up a known email address that has a '+' and retrieve the recordID for that specific email, not all emails.  But you gave me a tool that does work -- REGEX_MATCH.  Not sure why you have the AND, but the solution in JS is to prepend '\\' before the plus.  So bob+1@bob.com --> bob\\+1@bob.com.

var Airtable = require('airtable');
var base = new Airtable({apiKey: 'XXX'}).base('appYYY');

var targetEmail='bob+1@bob.com';
targetEmail = targetEmail.includes('+') ? targetEmail.replace('+', '\\+') : targetEmail;

base('Candidate').select({
   maxRecords: 1,
   filterByFormula: `REGEX_MATCH({Email},"${targetEmail}")`
}).eachPage(function page(records, fetchNextPage) {
   records.forEach(function(record) {
      console.log('Found', record.get('Record ID'));
});
}, function done(err) {
   if (err) {
      console.error(err); return;
   }
});

THANK YOU!

See Solution in Thread

3 Replies 3
dilipborad
8 - Airtable Astronomer
8 - Airtable Astronomer

Hello @scubachris 

This is bit tricky.

First of all use this to encode your url and change input parameters accordingly. https://codepen.io/airtable/full/MeXqOg

Now let's see what i've tested on my side.

This is my created table just for testing purpose.

dilipborad_0-1704778908508.png

I've used Regular Expression based function with filterByFormula. check it https://support.airtable.com/docs/guide-to-regex-functions

My testing response image.

dilipborad_2-1704779098067.png

URL Encoder image.

dilipborad_4-1704779398616.png

This need a bit of technical knowledge but I hope you get it. 

Try it on your side.

👍

 

 

 

scubachris
5 - Automation Enthusiast
5 - Automation Enthusiast

 

@dilipborad:  First, thanks so much for looking at this and trying to help!

I was not clear on what I'm trying to accomplish.  I want to look up a known email address that has a '+' and retrieve the recordID for that specific email, not all emails.  But you gave me a tool that does work -- REGEX_MATCH.  Not sure why you have the AND, but the solution in JS is to prepend '\\' before the plus.  So bob+1@bob.com --> bob\\+1@bob.com.

var Airtable = require('airtable');
var base = new Airtable({apiKey: 'XXX'}).base('appYYY');

var targetEmail='bob+1@bob.com';
targetEmail = targetEmail.includes('+') ? targetEmail.replace('+', '\\+') : targetEmail;

base('Candidate').select({
   maxRecords: 1,
   filterByFormula: `REGEX_MATCH({Email},"${targetEmail}")`
}).eachPage(function page(records, fetchNextPage) {
   records.forEach(function(record) {
      console.log('Found', record.get('Record ID'));
});
}, function done(err) {
   if (err) {
      console.error(err); return;
   }
});

THANK YOU!

dilipborad
8 - Airtable Astronomer
8 - Airtable Astronomer

OK @scubachris 

AND is used if there are multiple conditions need to apply. I've forget to remove it but that's no need to used when a single condition is used.

I'm glad that issue is fixed.

👍