Help

Re: Search formula to identify record with specific value in 2 fields as part of a Zap

2309 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Ben_Green
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi

I am setting up a zap connecting stripe to airtable.

I want the zap to find a record in Airtable that matches the stripe data in 2 fields

To do this I need to enter an AirTable search formula into the zap

The 2 Airtable columns I want it to search are “Email Address” and “Subscribed to”

So let’s say that a change is made in stripe that fires the Zap.

The zap will search Airtable for a record where both the “Email Address” field and the “Subscribed to” field match the data in both those fields in stripe. The combination of the 2 fields indicates it is the correct record to be updated with the changes made in Stripe.

What search formula would get this result for me in my Zap?

ChatGPT helped me get to this formula:

"{Email Address} = {Stripe Subscription}[customer.email] AND {Subscribed to} = {Stripe Subscription}[items.data.0.price.nickname]"

but I'm getting the message

"Failed to find or create a record in Airtable

Error from halted execution: The formula for filtering records is invalid: Invalid formula. Please check your formula text."

And Chat GPT says I've asked it too many questions 🙂

Thank you

Ben

3 Replies 3

Hi Ben, not sure if you've figured this out or not, but I would recommend checking out Zapier's guide on this in the "Advanced tips and tricks" section

Specifically:


The search formula should be a valid Airtable formula that is true when a record matches. The names of Airtable fields should be surrounded in curly brackets (e.g. {Contact Name}). You can insert values from your trigger app using the button to the right of the input. Note, however, that you'll need to surround the value tokens from the trigger app with <TriggerValue>. So, in our earlier example, we could write the formula:

{Contact Name}="<TriggerValue>(Step 1: From Name)<TriggerValue>"

This would mean "search to see whether the From Name value in Step 1 matches anything in the Contact Name field of my Airtable database."


And so, combined with Airtable's syntax, it might look something like the following.  I can't say for sure as I'm unclear how your Zap's set up

AND({Email Address}="<TriggerValue>{Stripe Subscription}[customer.email]<TriggerValue>",{Subscribed to}="<TriggerValue>{Stripe Subscription}[items.data.0.price.nickname]<TriggerValue>")

 

Thanks Adam. I had tried what they recommended in the tips and tricks link you provided but it didn't work for me. I have since had a reply from AirTable support that tell me "At the moment, we, unfortunately, aren't equipped to provide individual assistance with building/troubleshooting complex formulas. I did reach out to my team who have, however, informed that they do not believe it is currently possible to do this."

I will double check by trying your formula, just in case and will report back if it works. Thanks again

Ah roger that.  If you wanted to investigate this further I'd be really interested in doing that; no worries if you've already found another solution