Skip to main content

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

  • September 29, 2024
  • 3 replies
  • 0 views
K
Forum|alt.badge.img+2

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

    TheTimeSavingCo
    Forum|alt.badge.img+28

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

    Adam, Consultant @ The Time Saving Co
    kuovonne
    Forum|alt.badge.img+27
    • kuovonne
    • Brainy
    • 6001 replies
    • September 30, 2024

    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.

    dilipborad
    Forum|alt.badge.img+20
    • dilipborad
    • Brainy
    • 215 replies
    • September 30, 2024

    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.

    👍

    Reply


    Most helpful members this week

    Terms & Conditions