Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 07, 2020 07:33 AM
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
Solved! Go to Solution.
Jul 09, 2020 07:41 AM
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
Jul 07, 2020 09:01 AM
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.
Jul 07, 2020 10:15 AM
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
Jul 07, 2020 11:41 AM
Robert,
Can you tell me what context you are running this filter process in? Is it a script block?
Jul 08, 2020 01:18 AM
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
Jul 08, 2020 07:28 AM
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.
Jul 08, 2020 08:58 AM
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.
Jul 09, 2020 07:41 AM
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
Jul 09, 2020 08:09 AM
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.