I have created a table with my suppliers and the products the offer. I have the following tables:
Suppliers
Products
Invoices
Invoices lines
In Suppliers I enter every company I work or might be interested in working in the future.
In products I have all the products offered, each product has a price and the supplier that offers that product (It has many other fields but are not important here)
In Invoices lines I put the content of each invoice I create in the Invoices table and when I recall a product, it recalls as well the price.
The problem is sometimes I have exactly the same product offered by multiple companies and one of the ideas of this database is to be able to make a price comparision.
So I want every product to be connected to multiple suppliers and to have the price that every supplier asks for such product. And then, when I do the invoice lines and I recall the product, i’d love for airtable to recognize who is the supplier of that invoice and automatically recall the price that this supplier offers.
I thought of making a new table of prices where I can connect to suppliers and products.
Is there another way to do that without having to create a new table and working with the existing tables? (Also, if there isn’t, how can I move all the prices that I already have in my products table, to this new table?)
Thanks a lot for the help!
Supliers database and price comparision
+1
+28Hey
As you anticipate, the only way to understand what is the price PER supplier PER product would be by having a new table which you can call Prices. This is basically what is called a Junction Table -you can check out this YouTube video I recorded on junction tables.
Your invoice line items table should probably be linked to your prices table rather than to the Products table. So having said that, you might want to re-convert/re-name the Products table, and make applicable changes. This means that you would not loose the data you already have for prices. You will however need to create an additional Products table (you can copy and paste data on a new table).
Feel free to grab a slot using this link if you’d like to discuss this in further detail. I’d be happy to help!
Mike, Consultant @ Automatic Nation
+31I thought of making a new table of prices where I can connect to suppliers and products.
Is there another way to do that without having to create a new table and working with the existing tables?
Yeah that new table of prices would be the way to go and I think that’s the only way to deal with it really
---
(Also, if there isn’t, how can I move all the prices that I already have in my products table, to this new table?)
Hm, I’d say just treat your current table as the new ‘Prices’ table, and create a new ‘Products’ table instead. And so you’d:
- Rename the current table to ‘Prices’
- Duplicate the table and name the new one ‘Products’
- Create a link between ‘Prices’ and ‘Products’ and link all the records up via a unique value (e.g. SKU)
- Delete all the product info related fields in ‘Prices’
- Delete all the price related fields in ‘Products’, as well as the ‘Supplier’ linked field
And so now your prices are all linked to their respective products, and its linked to your original invoice table as well
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.