Updating field using data from a separate table

[Richard_Plumrid]

Hopefully this is a simple one and I explain this correctly:

I have a table of products containing several fields, including: unique product ID and price. (Let’s call this the primary table)

I am using the Data Fetcher Airtable app to pull updated product information from an API into a separate table in the same base. (Let’s call this the secondary table). This also contains a unique product ID and price

I want to fetch the price in the secondary table and update the relevant price field in the primary table, using the product ID to ensure the correct item is updated. If these were two tabs in Excel, I would just use a vlookup to look up the id and return the price. However I can’t figure out how to get this to work in Airtable.

Any advice much appreciated.

For info - the two tables do have a name field, containing the name of the product, however the names in the primary & secondary tables don’t always match, so I cannot use this field to match an item.

Many thanks

[Adam_Minich · Accepted solution]

Hi @Richard_Plumridge,

Adam from Airtable here. Welcome to the community! You can find our guidelines and FAQ’s here.

There have been several other threads in the community about this kind of scenario and vlookups in general. I’d suggest searching through those posts for some tips and hints. While we do not offer exact vlookup functionality many folks are able to accomplish similar behavior using linked records and lookup or rollup fields.

An important distinction is that in Airtable you will manually select (“link”) records (rows) from one table to records in another rather than finding matches programmatically (at least in part) You’ll need:

• A linked record field to specify a relationship between records across two tables (If the manual linking feels like a lot of work/doesn’t make sense for your workflow, there’s a good chance that you should consider combining tables)
• A lookup or rollup field — this pulls in the field value (or aggregate) of your choice from the linked table
• A conditional rollup — here, instead of pulling in all linked values, you’ll pull in only values that match a certain criteria, which you can define in an IF statement. This captures the functionality in the VLOOKUP which actually does the matching.

Just to be clear. Are the product ID’s in both tables matching? In other words, does the same product in the two different tables have the same matching ID? If so, then there is likely a way to build out a solution using our native Automations feature.

Let me know~~

[Richard_Plumrid]

Hi @Adam_Minich

Many thanks for the response and all the additional information. You have ultimately answered my question with the following quote:

‘An important distinction is that in Airtable you will manually select (“link”) records (rows) from one table to records in another rather than finding matches programmatically’

In other words I have to manually go down every single item in my table and match it with the relevant item in my secondary table (once the table is linked of course). Only then can I lookup the price for that item. Obviously this is long-winded initially, but once done I shouldn’t have to amend.

In my particular example, where the Product ID is the unique identifier, I have added it as the first column in my secondary table. This means when I look to link from the primary table I can just copy and paste the Product ID into my link field column to easily connect, rather than trying to match products where the name is different.

Many thanks