Help

Zapier Advanced Search if field “CONTAINS”

Topic Labels: Integrations
11044 17
cancel
Showing results for 
Search instead for 
Did you mean: 
Jeremy_Chevalli
6 - Interface Innovator
6 - Interface Innovator

I need to know how to tell Zapier to return an Airtable record where {Email Address} CONTAINS a specific string. I have already read the article everyone seems to be linking to in other threads, which has not proven useful in educating me on advanced search parameters.

Formula I’m currently using returns only cells which match exactly the string, but again, I need to find cells that CONTAIN that string, even if they have more characters at the end, like a tab or space.

{Email Address}="<TriggerValue>{{42085547__So exciting! What is your email address?}}<TriggerValue>"

Would I simply change the = to something else?

Do I need a command such as CONTAINS= but then how would I know that other than asking the community? Is there a place where I can find a whole list of advanced search parameters like this?

17 Replies 17
openside
10 - Mercury
10 - Mercury

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.

@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?

jowan_qupty
6 - Interface Innovator
6 - Interface Innovator

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!

alex
5 - Automation Enthusiast
5 - Automation Enthusiast

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”:

Image 2019-09-04 at 1.00.50 AM.png

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!

openside
10 - Mercury
10 - Mercury

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.

alex
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
image.png

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.

alex
5 - Automation Enthusiast
5 - Automation Enthusiast

: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?

image.png

I so much love the Airtable community! :slightly_smiling_face:

openside
10 - Mercury
10 - Mercury

either one would likely work, my personal preference is FIND

ChrisPreen
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
image

This is the formula I’m using in Zapier:

image

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

Any suggestions would be mega appreciated.

"

openside
10 - Mercury
10 - Mercury

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: