Help

Problems using ?filterByFormula with a special character ('#')

1023 3
cancel
Showing results for 
Search instead for 
Did you mean: 
KevinScottGoff
4 - Data Explorer
4 - Data Explorer

I'm using the AirTable API with Powershell, to lookup records in AirTable where a column value contains a special character ('#')

For example, I'm trying to filter on AirTable rows for a Color column that contains a '#'.
Let's say the Color value is 'ABC#1'.      

I have tried different combinations of double quotes and back-slashes, but nothing seems to work:

Using the original color definition doesn't work....it generates an invalid formula error. 
But no matter what I do in the 2nd variable, I continue to get an invalid formula error.

$OriginalColor =  'ABC#1'      
$LookupColor = $Color.Replace('#',  '\#')

$filter = "?filterByFormula=And({Division}='$Division',{Style}='$Style',{Color}='$LookupColor')"

The error I get is: 
iwr : {"error":{"type":"INVALID_FILTER_BY_FORMULA","message":
"The formula for filtering records is invalid: Invalid formula. Please check your formula text."

Maybe it's something more specific I need to do with double quotes, but everything I've tried has generated the same error.

Any thoughts?
Thanks,
Kevin

 

3 Replies 3

Hmm when you tried using Airtable's URL encoder (https://codepen.io/airtable/full/MeXqOg) did it work?  Does your query work in Postman?

Some troubleshooting ideas

- Does your formula work in a formula field in Airtable? The # character should not need escaping in the formula itself, although it may need URL encoding.

- Are you url encoding the parameters?

- Airtable formula functions should be all capital letters. Try capitalizing your AND() function.

- Can you inspect the value of the filter and the final full url that you are sending to the Web API? URL decode it, to make sure that the value really is what you think it is.

Hello @KevinScottGoff 
Both @kuovonne & @TheTimeSavingCo  are right for giving you more ideas.
I've created one field, Color(text), for one of the test tables. which has value with #, when first try it it gives me the same results you've mentioned.
Then I've copy that url and put it into Airtable's URL encoder (https://codepen.io/airtable/full/MeXqOg).
Then I've realize that issue is # so I've just encode # to url encoder and all other things remains same.

See attached image.

FilterByFormula usageFilterByFormula usage

In my testing you can see the things. Just need to encode that # value and then combine it with other values.

👍