Zapier Advanced Search if field “CONTAINS”

You can use the formula functions (see here: https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference#text)

So the FIND or SEARCH functions would be what you want

Thanks for the response! Zapier didn’t like them, but I’ve found a workaround. Much appreciate your help regardless.

Can you please share your workaround Jeremy?
I’m attempting to do the exact same thing but am stuck and not sure how to proceed.

Try this: Formula help for an IF(logical, value1, value2)

@Arlo_Haskell, thanks for the link. However, my problem is not with the Airtable formula, but with the Zapier advanced search.
I am attempting to search the {Phone Number} field to see if it CONTAINS my trigger value “from”.

Here’s a sample of my zapier advanced search formula that’s not working:
(FIND("{{43369726__from}}<TriggerValue",{Phone Number}))>0

This should theoretically give me a “1” for the record that matches, and therefore “find” that one record in question that matches/partially matches my trigger value, however Zapier keeps telling me it can’t find the record.

Any help would be greatly appreciated :pray:

I’m assuming {Phone Number} is a phone number type field. This is just a guess, but maybe that field type is the issue. Have you tried converting phone numbers into a text value via a new formula field and searching that field instead?

I’ve figured out the problem (however, haven’t found a solution).
It is because my {Phone Number} field was a lookup from another table. Even converting it into a text value via a new formula field doesn’t work.
Creating another field within Airtable with the Find formula works just fine, giving me 1 or 0, however I guess the Zapier advanced search doesn’t work on lookup fields ??

Hey, sorry I didn’t see this sooner. My workaround was to manually ensure email addresses are entered correctly. Not much help at scale. Let me know if you find anything else!

Hey @jowan_qupty, I’m experiencing this exact same issue, was wondering if you or @Jeremy_Chevallier found a solution for this? Essentially, this doesn’t work because it appears to be an “exact match”:

Have you found an “advanced search” type that would allow for this type of “if contains” type of search?

Essentially, I lookup client information based on their email domain. In some edge-cases though, clients have 2 or even 3 email domains making things super difficult. Unless you are able to somehow so a search based on “contains”?

@openside maybe could you do this with On2Air: Actions? Any help would be greatly appreciated!

What you’ll want to do is use a Rollup function instead of a Lookup.

Under the hood, at the API level, lookups are treated like arrays, so the functions to find a match don’t work, but using a rollup converts it to a string, then the formulas work.

You can just use CONCATENATE(values) in the rollup formula.

I’ll give that a try! The one thing that seems strange though, is when I use the CONCATENATE(values) in the rollup formula, I am left with it just being connected:

It looks terrible on the left, is there any way to make that more visually appealing, like how it looks with a lookup (re: website1.com, website2.com)?

ah sorry, i didn’t look close enough. I use CONCATENATE for single item rollups. For multiple item look ups, use ARRAYJOIN(values). You could actually use that for both single and multiple item rollups.

and for some secret, undocumented stuff, use ARRAYJOIN(values, " | ") to seperate it with a | sign. or replace that with anything else you want to use as your separator.

:grinning_face_with_big_eyes: I quite literally found the ARRAYJOIN(values) piece within the documentation and I was about to do some testing to see if it still worked and then post here. Thank you so much for confirming that, I don’t even have to test now!

I lowkey was wondering if there was a way to separate it with something other than a comma, but didn’t see it in the docs so I figured not. Leave it up to you knowing the undocumented formulas though, love how much you know about Airtable - thank you!

Everything appears to be working well upon initial tests with rollup ARRAYJOIN(values, " | ") just want to double-check, this is what you would recommend for the search formula correct? I saw you also mentioned that the “FIND” formula may work. Wasn’t sure which one makes more sense or of the difference.

The description for FIND() is:

Finds an occurrence of stringToFind in whereToSearch string starting from an optional startFromPosition.(startFromPosition is 0 by default.) If no occurrence of stringToFind is found, the result will be 0.

Similar to SEARCH(), though SEARCH() returns empty rather than 0 if no occurrence of stringToFind is found.

I suppose, when would it really matter to have a 0 instead of empty? I think empty (using search) would work just fine in my scenario?

I so much love the Airtable community! :slightly_smiling_face:

either one would likely work, my personal preference is FIND

Good to see some other Zapier experts here :grinning:

I’m using FIND to look up a string in a URL field but keep getting errors in Zapier.
This is the test record in the Airtable field (Type “text” field) I am trying to find:

This is the formula I’m using in Zapier:

I could alterntively search for just the number “88888” but this also gave me an error.

Any suggestions would be mega appreciated.

"

it appears you’re missing the second parameter in your Search formula. you need to specify which field to perform the search against using FIND(). Should it not be: FIND("https://........", {ZAPIER TASK URL} )

Awesome Dan - you nailed it! :thumbs_up: