Help

Zapier <> Airtable Search Formula

Topic Labels: Formulas
1182 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Steven_Villarin
4 - Data Explorer
4 - Data Explorer

Objective

  • Zapier to search Airtable records: Description, Amount, Transaction ID exists, if Not, create a record.

Question

  • Can Zapier search across three columns (see screenshot)?

Screenshot
https://dsh.re/65cc0

5 Replies 5

Yes. Create a formula field in Airtable so that it results in the number 1 for the records that you are trying to match. (Type in “hardcoded values” into your Airtable formula for field values… in other words, choose a real description, a real amount, and a real transaction ID for your matching purposes.)

In your case, it looks like you would probably wrap your entire formula with an ‘AND()’ function to make sure that all 3 values exist.

After you‘ve got your formula in Airtable worked out perfectly so that it results in the number 1 for any matching records, then copy and paste that formula into the “search formula” box in Zapier.

However, back in Zapier, substitute out any “hardcoded values” for the “Zapier variables” (like you’ve already done in Zapier).

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

Steven_Villarin
4 - Data Explorer
4 - Data Explorer

:confused: Is this closer?

CleanShot 2020-06-29 at 09.35.43@2x

I got this error…
CleanShot 2020-06-29 at 09.35.12@2x

Airtable formula
CleanShot 2020-06-29 at 13.21.47@2x

The problem is that your formula in Airtable doesn’t result in 1 for the matching record that you’re trying to match on.

As I mentioned above, you would need to create a formula in Airtable that returns the number 1 for the matching record that you’re trying to match on. You can actually delete that formula in Airtable after you create it, but it’s just for you to figure out the proper structure for your formula.

So, in Airtable, your formula might look something like this:

AND(Description="Hardware",Amount="25.83",{Transaction ID}="1234567890")

That would return the number 1 for any records that match those 3 criteria, and it would return 0 for any records that DON’T match those 3 criteria.

So, once you’ve gotten that worked out in Airtable, then you can just copy-and-paste your formula into Zapier, but swap out the “hardcoded values” (the values within the quotation marks) with <TriggerValue>YourZapierFieldSelectionGoesHere<TriggerValue>

Nicolas_d_ANDRE
5 - Automation Enthusiast
5 - Automation Enthusiast

Great information @ScottWorld ! I'd like the output sent to Zapier to be sorted by a certain column. I haven't found a way to pass any sort capabilities. When returning a list of records matching the search formula, how is the list ordered by default ? Is there a way to change the default sorting ?

ScottWorld
18 - Pluto
18 - Pluto

@Nicolas_d_ANDRE 

I don’t use nor recommend Zapier anymore, because of TONS of limitations like this.

Make lets you search and sort records, and it’s all built natively into the product without needing to craft any custom API calls.

Make is significantly less expensive than Zapier, yet it is infinitely more powerful & customizable than Zapier.

There can be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses. There are also many Make experts hanging out there who can answer other Make questions.