filterByFormula exact match on an array


#1

I have a field that is an array of strings. I’d like to filter a table based on this array field and return the row if one of the strings in the array is an exact match on, let’s say, the word “sugar”. It should be exact match only so it should not return rows that include the word “brown sugar” in the array. I run into the latter issue when using Find() and Search() on an ArrayJoin() of the field which seems to search for any occurrence of the string. Is it possible to search an array for an exact match?


#2

This is a bit of a head scratcher!

You cloud try adding your separator at the start and end of the field created with the ArrayJoin() and then searching for a string including the separator at the beginning and end of your desired string…

14

and

30

Not that pretty but I think it works.


#3

Wow. Great idea! I think it’s working. I was thinking I would need a new formula field that would format these items as something like #ZR0100#, but making the API call with your formula would be nicer since I won’t have to pollute the database with unneeded info. Thanks @Julian_Kirkness!