Help

Using API to filter records fails when the value contains a comma

Topic Labels: API
4147 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Scot_Rumery
5 - Automation Enthusiast
5 - Automation Enthusiast

Value for the field in the record = ‘Company Name, LLC’.

The querystring contains: &filterByFormula={Customer}=‘Company Name, LLC’

That string is run through urlencode() and looks like this…

&filterByFormula={Customer}=%27Company%20Name%2C%20LLC%27

No matter what I try, it is failing for every customer that contains a comma. Not sure if there is a way to escape the comma, or if this is an Airtable API bug.

3 Replies 3
Andrew_Johnson1
8 - Airtable Astronomer
8 - Airtable Astronomer

Try using the Airtbale API Encoder on CodePen (You should find the link on your base’s API documentation)

Running your formula through the Airtbale API Encoder gave me

filterByFormula=%7BCustomer%7D%3D’Company+Name%2C+LLC’

Thank you for your help, Andrew. I am still unable to get this to work. No matter what I try, it is failing for every customer that contains a comma. Not sure if there is a way to escape the comma, or if this is an Airtable API bug. I am sending a support request in to see if we can’t get this resolved.

This has been resolved with the Airtable support staff. I thought it would be good to post the resolution here for anyone else that might run into this.

What I found out was…
I ran a few more experiments and I found something interesting. If the field used in the filterByFormula command is a Link to Another Record field. It fails when a comma is used. My “Customer” field in the Websites table is a Link field. Every search for a string with a comma fails. This isn’t a matter of properly encoding the URL. It just doesn’t work. I think this is a bug. If I remove the comma from the field in the database and then remove the “,” or “%2C” from the URL string, the correct record is returned. By adding the comma back in both places, it returns an empty record set, {“records”: }.

To further troubleshoot this issue, I modified the URL to query the Customers table and directly search the “Company Name” field, which is a Single Line Text field. The query works with a comma in it.

Resolution from Airtable Support:
In order to escape the internal commas, I wrapped the search term for each individual record in double quotes, and wrapped the entire search string in single quotes:

{Field 20}='"Success Team, Weekly"'

Where {Field 20} is a linked record field, with linkages to a record named Success Team, Weekly

You could extend this, since it’s possible to search for a single record linked to multiple, e.g.,

{Field 20}='"Success Team, Weekly","Success Team, Monthly"'

And so we need to wrap each individual record in double quotes while wrapping the entire search string in single quotes.