ok, so I’m trying to pull in our “to print” pricing sheet. I have 8 different types of print items we order and want to have a table with all of the specs but then pricing is based on quantity ordered.
Example is
Item: New Listing Post card Mailers
table would have the item, then vendor, material, size. processing time, quantities ordered in, and then pricing but the pricing is set as 200 ordered = .49 ea, 500 ordered = .44 ea 1000 ordered =.38 ea
how can I set this up in a table?
This isn’t quite right…but should get you started. Create a “Unit cost” Field, and then use it as a multiplier
IF({Quantity ordered} > 200, IF({Quantity ordered} < 500, .49, IF({Quantity ordered} > 500, IF({Quantity ordered} < 1000, .44, .38), IF({Quantity ordered} >1000, .38, .50))), .50)
+21Welcome 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.
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.
wow, thank you! That is perfect!!
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.
I’m having an issue…it’s not calculating the total, just giving me $0 in the Final cost Even if I take out everything and just do Quantity * IF(NOT({T1#}), Price) it just gives $0 when entering anything in the Quantity column
+21I’m having an issue…it’s not calculating the total, just giving me $0 in the Final cost Even if I take out everything and just do Quantity * IF(NOT({T1#}), Price) it just gives $0 when entering anything in the Quantity column
Hmmm…check the type and formatting of all of your fields, including the lookup fields. All currency fields should be the Currency type, and the formula field formatting should be set to Currency. In theory things should still calculate correctly no matter how the formatting is set, because formatting is supposed to only affect data display, not the underlying data itself.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.