Zapier Advanced Search if field “CONTAINS”

[Jeremy_Chevalli]

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?

[openside]

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]

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

[openside]

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]

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

[openside]

either one would likely work, my personal preference is FIND

[ChrisPreen]

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.

"

[openside]

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} )

[ChrisPreen]

Awesome Dan - you nailed it! :thumbs_up: