The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
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.