Help

Filterbyformula single quote AND comma in fields -- good times!

Topic Labels: API
2780 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Cindy_Shapiro
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello!

I’m trying to put together node js logic to find record ids for two concatenated fields as the key field.

I’ve gotten through my issues of having commas in my search strings, but now I have compound issues with both commas and single quotes in my strings.

I’m ending up with a filterbyformula variable that looks like this:
{Song ID} = ‘“A Midsummer’s Breeze”-“Meditations for Relaxation, Vol. 1”’

My logic has found both the single quote and the comma, enclosed both of the constituent pieces in double quotes and then enclosed the entire string in single quote.

Airtable doesn’t like this and cannot find the record.

In the DB, the record looks like this:
A Midsummer’s Breeze-“Meditations for Relaxation, Vol. 1”

Before I make myself nuts trying to suss out a solution, I thought I would ask you trustworthy and kind fellow coders…

Thanks in advance for any help!

Cindy

4 Replies 4
Tuur
10 - Mercury
10 - Mercury

You can probably change these extra (double!) quotes in %22 instances instead. I haven’t tried the comma myself, but that should be %2C.

Does that work for you? :slightly_smiling_face:

Have a look at http://www.asciitable.com and use the hexadecimal values in case of problems.

Thanks for your response! Thinking through it all.

Cindy

Scot_Rumery
5 - Automation Enthusiast
5 - Automation Enthusiast

Did you ever find a solution to the comma issue? I am experiencing the same thing. My code works for all records except the ones containing commas.

Example: ‘Company Name, LLC’

&filterByFormula={Customer}=‘Company Name, LLC’

The url is being encoded and still fails. Not sure if there is a way to escape the comma, or if this is an Airtable API bug.

Celeste_Bancos
7 - App Architect
7 - App Architect

I had this issue with a single quotation mark and was able to use Tuur's approach by replacing it with %27 and that worked great!

  const filter_by_formula = `{Company} = '${name.replaceAll("'", "%27")}'`