Re: Linking Tables to track inventory and vendors

762 0
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you in advance! I’m new to Airtable and have watched several tutorials and still I’m stumped. Here is the situation I have is a base with 8 Tables. Table 1 is the master list of products I sell. This table has fields with my product name, and number, etc. The other 7 tables are vendors where I can buy the products. Each vendor has a different name and price for the product. The first 5 fields on every table is the name of my product. The goal is to be able to move from Vendor A to Vendor B and so on and see my name and color of item and compare it to the Vendors product name. What I want to do is have the first 5 fields on table 1 linked to the other 7 tables. The goal is when I discontinue an item on my product list (table 1) and change the status to discontinued then the status changes on all the other vendor tables. Or if a vendor discontinues a product, I can change the status on the vendors table, and it will change on all other tables.

I hope this makes sense, and I really appreciate the assistance.

2 Replies 2

Hi @Christa_Patton1,

It’s a little tough to tell from your description what exactly your end goal is, but I can tell that you seem to be thinking of your Airtable base in terms of a spreadsheet.

Since I can’t quite decipher what your end goal is, it’s tough to give specific advice, but I will give this general advice, that you might want to consider structuring your Airtable base more like a database (which is Airtable’s intended purpose).

Rule #1 of databases is to keep all similar “things” in a table together, not spread across multiple tables. You have 7 vendors, you said, and are keeping a table for each one. They are all vendors, so they should all be together in a “Vendors” table.

Another rule is to use relationships to establish shared stake in a “thing” across multiple entities, rather than creating multiple copies of “thing”. You offer “Products”, and one or more of your “Vendors” may supply that “Product”. All the “Products” should live in one table, and a relationship established between a “Product” and a “Vendor” that supplies it.

Here’s a real quick example base for you that I think might start to get you moving in the direction you are wanting to go:

Take a look and ask more questions, and maybe we can home in on how to make this work for your use case.

To establish a relationship between a product you offer (from the “My Products” table) and a vendor you buy from (from the “Vendors” table), you can create a “Vendor-Products” record. You can do this from within the “My Products” table by clicking in the Vendors linked field:
CleanShot 2022-10-19 at 16.21.25

and selecting to create a new record:
CleanShot 2022-10-19 at 16.22.13

You’ll see that the “Product” link is already made for you (the product from which you clicked in the Vendors field. You can then select a “Vendor” that carries this “Product”, and list what special name that Vendor gives to this product, as well as that vendor’s price:
CleanShot 2022-10-19 at 16.24.52

Keeping records in this way will reap you many benefits, such as being able to average prices across vendors, or find high/low prices among vendors.

You can view all the “Products” from the perspective of a particular “Vendor” from this “Vendor-Products” table by creating a View that filters to show only that “Vendor”.
CleanShot 2022-10-19 at 16.28.17

5 - Automation Enthusiast
5 - Automation Enthusiast

Hi @Jeremy_Oglesby,
Thank you very much! I think this will get me moving in the right direction. This might just be the reason I was having trouble. My relationships and thought processes were more like a spreadsheet rather than a data base. Even though a data base is my goal. While I might not have been able to articulate exactly what I needed I think you did a great job tracking it and giving some insight. I’ll circle back if I run into any more snags as I learn the ropes. Plenty of information to put into action. Thank you