Trying to build out a pricing sheet
Welcome to the community, @Maureen_Robison! :grinning_face_with_big_eyes: @Andrew_Wingrave has the right idea if for single products. However, you said you’ve got 8 different types of items. I’m guessing they each have their own discount tiers based on the quantity ordered, and some might not have discounts at all. To account for that, I suggest building a pricing table. Here’s the setup I created:
The {Price} field is the base price that applies before any volume discounting comes into play, or the flat per-item price for products like the second item that have no discount tiers. The other fields outline the different tiers available. {T1#} is the quantity where the Tier 1 discount begins, with {T1$} being its associated price, and so on for the second and third tiers. You can add as many tiers as you need for your specific situation, and just make a minor adjustment to a formula that I’ll outline later to account for any added tiers you use.
Over in your orders table, it might start out looking something like this:
The primary field is just a formula that combines the item and quantity for easy reference. After the {Item} and {Quantity} fields, there’s a formula field for the final cost, followed by lookup fields that pull in the pricing details for the linked item.
The formula in the {Final Cost} field is the following:
Quantity * IF(NOT({T1#}), Price, IF(Quantity>={T3#}, {T3$}, IF(Quantity>={T2#}, {T2$}, IF(Quantity>={T1#}, {T1$}, Price))))
The beginning checks to see if the item even has any discount tiers, defaulting to the base price if not. The rest is the same basic structure as Andrew’s example above, but working the tiers in reverse order to reduce the number of checks needed.
If you have fewer than three tiers for a given item, just make the “extra” tier count and cost values identical. If you need more tiers, add the appropriate fields to both tables, and adjust the core of the {Final Cost} formula to start with the highest tiers, working down to tier 1 as in my example.
