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

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

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

@DataTracker 

Try this formula:

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

NOTE: You may need to adjust the static values.

@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. 

@DataTracker 

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

@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>")

@DataTracker 

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

@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. 

@DataTracker 

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

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. 

2. 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 #. 

3. 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. 

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

@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