How do I update an airtable record based on the content from another table?

Userlevel 1

So ... I have an airtable Table Vendor, and Table Transactions... the Name field in the Transactions table sometimes contains the same text as in the Vendor field (which is called 'Who' [i know ... awful field name]), but not always, so I want to search for the text in the Transations:Name to find a the name of the vendor in the Vendors:Who field.

I'm trying to find a way to search the Vendors so that it'll populate the LookUp field or linked Field automatically.

I made a view that has all the empty vendors fields.. and I am starting an automation that triggers when it's entered this view, then I wanted to search the vendors list for the text that is in the Name field ... but ... i can't do that ... it'll only find the entire line, cause it's taken from a bank statement, and you know how weird those are.

For instance if the Transaction name would be "RECURRING PYMNT 6MAR2023AIRTABLE.COM/BILL CA 716.76X1.397259891" I want to search and find Airtable, but ... it's not letting me do that obviously.  Any ideas? 

So I’m creating a Zap to format the transaction name, and remove numbers, and create a method that its more searchable. Any ideas on how to accompilish this?

3 replies

Userlevel 7
Badge +11

Hey@AmandaAdama! 👋

Would the transactions all follow the exact same format? Or would they be different depending on the supplier?  

If they always follow the exact same format then you could potentially extract the vendor company name using a Formatter (Text > Extract Pattern) action with the following Regex pattern:


The \d{1,2}[A-Z]{3}\d{4} part would first find a match for the date and the ([A-Za-z]+) part finds the group of letters (regardless of case) after the date, that should contain the vendor company name. You can find out more about how to use Regex patterns here: Find text with regular expressions in Zaps

So if the transaction was RECURRING PYMNT 12MAY2023apple/BILL CA 123.12X1.123456789 it would extract the word apple. If the transaction was RECURRING PYMNT 2JAN2023GOOGLE/BILL CA 123.12X1.123456712 it would extract the word GOOGLE. ​​​​​​

If the transaction name is in a different format every time though this approach wouldn’t work. Instead you might want to look into using a Code by Zapier action to handle transaction references that come in a variety of formats, but that’s a bit more advanced so might not be an ideal solution here. 

Hope that helps to get you pointed in the right direction on this. Please do keep us updated on how you get on! 🙂

Userlevel 1

Thanks @SamB, unfortunately it would be unpredictable, not always the same format.  

Userlevel 7
Badge +6

Hi there @AmandaAdama,

SamB's suggestion to use Code by Zapier might be just the thing you need. It's a bit more advanced, but don't worry if you're not familiar with coding. We have a team of certified Zapier Experts who are ready to help you out. You can connect with them right here: Zapier Experts


Hopefully, this helps! 😊