We use Airtable to manage a sourcing catalog. We gather price quotes from vendors in one table and use those quotes to build out customer orders in another. Selecting a quote record applies the price to customer orders. This means a single product can have multiple quotes associated with it for different quantities ordered. Quotes have a date, quantity, and price associated with them. I currently use a Lookup field to display the associate quotes per product into a view that we share with customers so they can see product descriptions, photos, and the prices at different order quantities. Table A (Product Info) Looks Up linked quotes in Table B (Vendor Quotes)
The problem is that vendors might change prices over time and I don’t want the Lookup field to display quotes that are not the most recent, but I still want the Lookup field to display a quote for each quantity.
Currently, if in March I receive quotes for a Product A at 500 units, 100 units, and 50 units my Look Up field will display those three quotes. Perfect. But if the vendor changes the price for 100 units in April I want the field I display to customers to show the March 500 quote, April 100 quote, and March 50 quote. I want to keep a history of all quotes received especially since some orders will have old quotes associated with them, so updating or deleting the March 100 quote isn’t an option.
Having a separate field for each quantity isn’t really an option since some products might be quoted at 10, 50, and 100 units, while other might be quoted at 1000, 5000, and 10,000.
How do I get Airtable to display all quotes for the different quantities, but only the most recent quotes for those quantities?
