Skip to main content

Hi all,

 

I'm am building a price comparison base which will be used to compare pricing from 1000+ suppliers on a range of products (3000+), but as a former Excel user, I'm struggling to mentally picture the base.

I understand it would be one table for products and one table for suppliers, but I'm not sure how I structure the records to report on the cheapest supplier price.

So if Product A is sold by supplier 1,2,3 & 4:

1. How do I link each supplier to the product with their respective price?

2. How do I get a column to show the cheapest price and from which supplier?

My brain automatically reverts to rows for products and columns for suppliers, but I know that's a  spreadsheet mentality and I will regret it as the data grows...

 

Any help sincerely appreciated!

The best method for this kind of problem is to have three tables, with Suppliers, Products and a linking table for the two. For example:

  1. Suppliers
  2. Products 
  3. Supplier Prices

I built out a simple example for you that you can get started with, see it here: Link to Demo

Suppliers table

Products table

 

Supplier Prices table

 

This one has two linked fields, Supplier and Product. And a currency, Price.

Get the minimum price

To get the minimum price, you can add a Rollup field based on the auto-created Prices in the Products table:

I hope this can get you started 🙂 Good luck with your Airtable journey!

Best, Milan - Automable.AI Free Airtable consultation

 

The best method for this kind of problem is to have three tables, with Suppliers, Products and a linking table for the two. For example:

  1. Suppliers
  2. Products 
  3. Supplier Prices

I built out a simple example for you that you can get started with, see it here: Link to Demo

Suppliers table

Products table

 

Supplier Prices table

 

This one has two linked fields, Supplier and Product. And a currency, Price.

Get the minimum price

To get the minimum price, you can add a Rollup field based on the auto-created Prices in the Products table:

I hope this can get you started 🙂 Good luck with your Airtable journey!

Best, Milan - Automable.AI Free Airtable consultation

 


Getting the cheapest supplier is possible with an automation. The trigger is the change of Minimum Price, then it runs a script to find the cheapest supplier(s) (there can be multiple with the same price), and set that field.

I also added this to the demo base, and here is a screencast of it:

Best, Milan - Automable.AI Free Airtable consultation

Hey @textmonkey!

Architecture described by @Milan_Automable will work great!

However, for finding/showing the cheapest supplier you might want to avoid the automation & script as not to overbuild. Otherwise, at some point you might even hit hard limits on your Airtable automations.

1. You can easily have a rollup field at Products level, which will rollup from the Supplier Prices the Price field applying the MIN(values) formula. Name this field "Min Price". For more information on Rollup Fields, you can check this post.

2. At the Suppliers Prices level, you can now create a Lookup field which will lookup, from the Suppliers Prices table, the field "Min Price". You can call this field "Global Min Price for Product". For more information on Lookup Fields, you can check this post.

3.At the Suppliers Prices level, create a Formula field that will say: Global Min Price for Product = Price
If it is true that Global Min Price for Product = Price then formula will output 1, otherwise it will output 0. You can call this formula "Cheapest Pricing".

4. For last, at a Product table, you can create a new Lookup field called "Cheapest Supplier". This field should Lookup the field Supplier, from the Suppliers Prices table. However, you need to remember to set the Condition within the Lookup to fetch only records where field "Cheapest Pricing" = 1. You can find more information on how to set conditions to a Lookup field here!

Please let me know if the above is not clear enough, and feel free to schedule a call if needed.

Mike, Consultant @ Automatic Nation

@Milan_Automable @Mike_AutomaticN  Thank you both for your thorough and prompt replies.

First time posting on the discussion forum, but I don't think I've ever been met with such succinct, friendly or helpful replies.

 

Thank You! 🙂

@Milan_Automable @Mike_AutomaticN  Thank you both for your thorough and prompt replies.

First time posting on the discussion forum, but I don't think I've ever been met with such succinct, friendly or helpful replies.

 

Thank You! 🙂


I'm happy that you found it useful! 🙂 Feel free to reach out to me directly in DM or at vasarmilan@gmail.com as well if you need more guidance.

Reply


Terms & Conditions