Skip to main content
Best answer

How do I create an Airtable search formula using multiple fields?

i have a zap that updates someone’s registration for an event when they pay.

the table in Airtable with event registrations has all the registrations for all events, so i need the zap to find the right person and the right event. it finds the person by email address, and it can find the correct event by either the name of the event (which is a lookup field) or the created date, assuming they registered in the past month.

i am trying to use an AND function for my search formula, and i am getting an invalid formula error in both cases. here’s what i’ve tried:

AND(Demail changed by formatter to lowercase]={Lowercase email}, SEARCH(“(Ticket name]”, “{Event title lookup})>0)

AND(>email changed by formatter to lowercase]={Lowercase email}, TONOW({Created date})<31)

(stuff in italics comes from earlier steps in the zap or my payment platform)

i prefer the ticket name if i can get it to work, but the ticket name will not always exactly match the info in Airtable, it will always contain.

what am i missing? does AND not allow nesting? something else? thank you!!

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

11 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @AI_hello 

Try like this:

AND({FIELD}="VALUE",{FIELD}="VALUE")

The Airtable field needs to be within {}

The VALUE needs to be within “”

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
AI_hello

thanks for the response!

right, i get that. for the {email} = email, that works. but for the event title, it won’t be true that {event title} = event title. i’m looking for event title “contains” event title. so that’s why i was trying to embed a SEARCH function inside the AND function. is that not allowed? if not, any ideas for a workaround?

Troy Tessalone
Userlevel 7
Badge +14

@AI_hello 

Post screenshots with how your Airtable Search Formula is configured to give us full context.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
AI_hello

 

maybe i have too many quotes? i will try that.

AI_hello

thank you, that worked. i added quotes in the first part & removed them in the second part, and also removed the >0 for the position. i hope this is actually finding the text, but it did produce the right answer:

 

AI_hello

wait no, it’s not finding it. there’s still something wrong with my search formula. sorry!

Troy Tessalone
Userlevel 7
Badge +14

@AI_hello 
Can you show screenshots of your related fields as they are named in Airtable?

Can you show screenshots of the Airtable record you are trying to find? (field values)

Try structuring the search formula as instructed with the {FIELD} on the left and the “VALUE” on the right.

And reference the Airtable formula descriptions for guidance.

 

 

 

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

FIND() returns 0 rather than empty if no occurrence of stringToFind is found.

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
AI_hello

i still cannot figure out my search formula, i have tried about everything i can think of. i couldn’t get the find or search function to work with the event title lookup field.

so i added a step where i found the event in the list of events table first, which i was able to do by using the functionality of Zapier to not search for an exact match.

then i planned to use the lowercase email and the event found in the previous step to locate the correct registration, using this search:

AND({Lowercase email}=”Email from the trigger step”, {Training from list}=”Record ID of the event that was found”), like so:

 

here’s the training that it found successfully in Airtable:

 

and here’s the registration that it cannot find with my search formula:

 

argh! what am i doing wrong? something simple??

Troy Tessalone
Userlevel 7
Badge +14

@AI_hello 

Even tho “Training from list” is a linked field, it expects the text value of the linked record rather than the linked record ID.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
AI_hello

aaaaah, that worked, thank you thank you!!!

ken.a
Userlevel 7
Badge +6

That’s awesome @AI_hello! A huge thanks to Troy for lending a hand and pointing you in the right direction!

If you have any other questions, please don’t hesitate to reach out to the Community. We’re always happy to help! 🤗

Terms & Conditions