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


#1

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.


#2

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’


#3

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.


#4

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.