Selecting from multiple sale prices to show product discount

[Ron_Daniel]

We have a database of thousands of products. Hundreds of them go on sale each month - and Airtable shows my designers what the % off or $ off is for each product.

The challenge is that a product may have a different sale price from month to month, and we're often working on marketing for two or three different sales at the same time. Rather than creating formula fields for each sale, I'd like the designer to be able to tell Airtable which sale they're working on, and have it show the discounts for just that sale.

Any ideas on how I could approach this? (The screencap isn't an actual solution - it's just one idea of what the solution might look like.) 

[Hannah_Wiginton]

Hey Ron,

You might consider making the  Sales a Linked Record field instead. Since it's such a fluctuating item, it would probably be better to track it in its own table and then link to your Products.

In your Sales table, you could add a description about the Sale and then add the percentage, date, and more info if needed, and have it linked to specific Products. You would then add a Lookup in your Products table to lookup the Sale Percentage and create formulas to calculate the $ Off and then the Current Sale Price.

[Ron_Daniel]

Hannah,
For the sake of brevity, I may have over-simplified my description of the issue. I do have a table for the Promotions (start date, end date, sale code, products in the sale, etc.), and another one for the product information. (It's a huge base - over 30 unique tables, and 6 more tables from synced bases.) Unfortunately, there's no "universal truth" for sales (i.e. "25% off everything"), so I need to have each product's sale price for each sale. Now that I've written it that way, I wonder if I need to create a new table that combines them, so that there is a separate record for each sale/product combo....

[Hannah_Wiginton]

Gotcha! Yeah that could the option to create table the combines them. It would be easier to track and customize.