Formula Filter for E-Mail: Problems with special Characters in E-Mail address

[Robert_Keck]

I use a formula filterByFormula=Lower(E_Mail)=Lower("{user_email}".

If there is a special Character of # or & or + in the e-mail address, for example debby+maier@gmail.com the formula does not find correspondig records in airtable. Special Characters like !$%’*-/=? within the E-Mail address are found.
Can someone give me a workaround or solution.
Thank you for your help

[Bill_French]

Robert_Keck:

If there is a special Character of # or & or + in the e-mail address, for example debby+maier@gmail.com the formula does not find correspondig records in airtable. Special Characters like !$%’*-/=? within the E-Mail address are found.

I believe this behaviour is expected if – and only if – you fail to URL-encode the query. Try testing just one by changing the plus sign (+) to %2B. i.e., …

SUBSTITUTE({yourURL},"+","%2B")

Just hard code a test to see if URL encoding is the remedy. If it is - this example might help you achieve a seamless solution.

[Robert_Keck]

Bill.French:

SUBSTITUTE({yourURL},"+","%2B")

Hi Bill, thank you for the fast response. May i ask for your support again.

i tried
filterByFormula=LOWER(SUBSTITUTE("{user_email}","+","%2B"))=Lower(E_Mail)=Lower("{user_email}")

it does not help. Is this what i should have done?

Thank you

[Bill_French]

Robert,

Can you tell me what context you are running this filter process in? Is it a script block?

[Robert_Keck]

Hi Bill,

i am using the https command within voiceflow api using get command.

I used this https command in the Request URL. (Special Characters don’t work]
“{user_email}”)

Now i have tried:

I don’t know the difference. But the second worked.

Thank you very much for your good support

[Bill_French]

Robert_Keck:

But the second worked.

Glad you have it working!

I suspect the second approach is automatically URL-encoding the request. But I’m still a little foggy concerning the second approach. Other people with similar issues would love it if you shared a few more details that led to your resolution.

[Justin_Barrett]

Robert_Keck:

Now i have tried:

I don’t know the difference. But the second worked.

I’ll echo @Bill.French’s request for further details. Your second option didn’t appear in your message for some reason, as you can see from the quote above.

[Robert_Keck · Accepted solution]

Issue:

I have a Voiceflow Skill using Airtable Database containing records with Name, E-Mail, etc. Records are found via search by E-Mail Address given from Amazon Alexa Account.

While testing i discovered that records with e-mail-Address including signs of #, &, + are not found although corresponding records exist in the Airtable Database. Also i got no Error Message.

Example 1 not working correctly:

Within the voiceflow api i used the following Request URL:
https://api.airtable.com/v0/blablabla/Table-Name?filterByFormula=Lower(E_Mail)=Lower("{user_email}")

This Command went ok for all E-Mail Adresses except containing signs of #, &, +.

Example 2 Working correctly.

After some try and error i found a solution working with all my testet E-Mail Addresses.

Within the voiceflow API Get Command i used the following Request URL and Parameter Assignments.

https://api.airtable.com/v0/blablabla/Table-Name?

I don’t know the Differenz between A und B. Maybe somone from the Airtable forum knows the answer.

Thank you for your good support

[Justin_Barrett]

For a formula to go directly into a URL, it would need to be properly encoded:

https://api.airtable.com/v0/blablabla/Table-Name?filterByFormula=Lower%28E_Mail%29%3DLower%28%22%7Buser_email%7D%22%29

Your initial URL didn’t encode the non-alphanumeric symbols in the formula, so it didn’t work. By entering the formula as a parameter, the application applied the correct encoding for you behind the scenes.