Sep 29, 2024 01:33 PM
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
Sep 29, 2024 07:48 PM
Hmm when you tried using Airtable's URL encoder (https://codepen.io/airtable/full/MeXqOg) did it work? Does your query work in Postman?
Sep 29, 2024 09:31 PM
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.
Sep 29, 2024 09:49 PM
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.
In my testing you can see the things. Just need to encode that # value and then combine it with other values.
👍