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

[scubachris]

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"

}

[dilipborad]

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.

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

My testing response image.

URL Encoder image.

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

Try it on your side.

👍

[scubachris · Accepted solution]

@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]

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.

👍