Skip to main content
Question

Unable to find specific Airtable record using Vendor ID and Invoice number in Zapier

In my Airtable Base, I have multiple records that could contain the same Vendor ID # and in the Invoice # field, I could be tracking multiple invoices per record. 

I am trying to figure out a formula to find a specific record based on two parameters - a matching Vendor ID # and if the Invoice # field contains a specific invoice #. I cannot figure out the formula for the “contains invoice #” part. 

I am using the Zapier “Find Record” tool and the Search Formula option, but am getting nowhere. 

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

13 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @DataTracker 

To help us have more context, post screenshots with how your Zap step is configured in EDIT mode.

Reference the help link in the field description: https://support.airtable.com/docs/using-zapier-s-multi-step-zaps-to-find-and-update-records

AND({FIELD_X}="X",{FIELD_Y}="Y")

 

 

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

Attached here are the screenshots of how this is setup and also the error I am receiving 

 

Troy Tessalone
Userlevel 7
Badge +14

@DataTracker 

Try this formula:

AND({FIU Vendor ID}="0000041976",{Vendor Invoice #}="$264728")

 

NOTE: You may need to adjust the static values.

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

@Troy Tessalone the problem is that I can’t put static information here, the search values will always depend on the record added in Table 1 from step 1 and I have to find matching information in Table 2 in step 2. 

 

Troy Tessalone
Userlevel 7
Badge +14

@DataTracker 

If you need dynamic values, then map variables from previous Zap steps into the Airtable formula field.

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

@Troy Tessalone that is what I am having trouble with in the current formula. I am getting an error message with the way the formula is currently setup and I don’t know how to fix it. Do I need to remove the “<TriggerValue>” text from the formula? 

 

AND({FIU Vendor ID}="<TriggerValue>1. Vendor ID: 0000041976<TriggerValue>",{Vendor Invoice #}="<TriggerValue>1. Invoice: S264728<TriggerValue>")

Troy Tessalone
Userlevel 7
Badge +14

@DataTracker 

Previous example shows formula without the extra values for <TriggerValue> as those are not needed.

 

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

@Troy Tessalone I am still having a problem with this formula - I am getting an error saying it can’t find the record in Airtable, when the record is what is being used as the Test Data. 

 

I think my issue is that there could be multiple lines of information in the Vendor Invoice # field. So I need to check to see if the cell contains a specific invoice number. Does that require a different formula or would the AND formula function properly in this instance? 

 

Also to provide some more clarification, some of my records will have the same Vendor ID, but they will have separate invoice numbers. That’s why this formula needs to find two different parameters to pull the correct record back for me to update. 

Troy Tessalone
Userlevel 7
Badge +14

@DataTracker 

For us to have true context, post screenshots with how your current formula is configured in the Zap step.

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

Apologies. Below are screenshots with more explanation. Important information highlighted in yellow with some information blacked out for privacy. 

 

The goal is to find the record with the matching Vendor ID and Invoice # in a different table from Step 1 in the zap so I can update other information on that record. 

 

  1. Step 1 in my Zap to find the new record added on Table #1. 

 

 

  1. Screenshot of Airtable setup where I have to find the matching information from step 1 on Table 2 in my Airtable. This shows that I have multiple records with the same Vendor ID, but different invoice numbers, with some cells containing more than one Invoice #. 

 

  1. Step 2 in Zap, with the Find Record in Airtable tool using the search formula that I cannot figure out, I think because of there being multiple invoice numbers in certain cells. 

 

Troy Tessalone
Userlevel 7
Badge +14

@DataTracker 

This is likely the issue.

The Vendor Invoice # field is a long text field that allows formatting.

The example you provide has 2 options.

So the field does not equal (=) the mapped variable value, rather it contains the mapped variable value.

The formula would have to be adjusted to be search/find the variable value within the the Airtable field value.

Airtable Formula reference: https://support.airtable.com/docs/formula-field-reference

 

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

@Troy Tessalone sorry to come back to this again, but I have spent all weekend trying to figure out the correct formula and I haven’t had any success. Some questions below - 

  1. What is the best formula to “find” information in a field? I am assuming it is the SEARCH formula, but I can’t seem to get it to work with the AND formula to also confirm the Vendor ID is the same. 
  2. Is there a different formula that would work better for this? 
  3. Would it help if the Vendor Invoice # field was a short text field with comma separated values? 
  4. Any other suggestions to get this to work properly? 
Troy Tessalone
Userlevel 7
Badge +14

@DataTracker 

  • Formula
    • Try asking ChatGPT for help configuring the formula to use that include find/search
  • Would it help if the Vendor Invoice # field was a short text field with comma separated values? 
    • Yes

 

If you are looking to hire help, there is a Directory of Certified Zapier Experts: https://zapier.com/experts

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