Blog‎ > ‎

Price lists with different quantity pricing threshold units

posted Oct 14, 2015, 10:23 AM by Matthew Marchant   [ updated Nov 16, 2015, 1:27 PM ]
If quantity pricing is used, NS's generate price list will produce a different table every time quantity thresholds are based on different units.

Ex. a company has different prices for above 1 pallet, 6 pallets and 12 pallets and yet sell in boxes.  
The issue arrises because some items have 30 boxes per pallet and others have 35, 36, 37, etc... boxes per pallet.  As a result, the generate price level tool will produce a different table for each different value of boxes/pallet and the resulting price list might be quite tedious to read.

Fortunately, it's possible to get around this with custom fields and SQL formulas in saved searches.  The first step is store the conversion rates (boxes/pallet in our example) in a custom field on item records.  The type of the field in our example is integer number as there can only be integer numbers of boxes per pallet.  The second step is to build the saved search.  To do I use an Item search and filter on the Price Level that I'm interested in.  I then group on the fields from the item record that I want to display (ie. name, description, class, type, etc...).  I then add formula fields to get the quantity pricing:

I select "Maximum" for the Summary Type and use the formula (case below threshold 1 pallet):
DECODE({pricing.minimumquantity},0,{pricing.unitprice},0)

For the case 1 pallet to 6 pallets I use the formula:
DECODE({pricing.minimumquantity},1*{custitem_uom_pal},{pricing.unitprice},0)

I continue to use similar formulas for other pricing thresholds (ie. 6 to 12, 12 and up, etc...)

I then add a link to this search to the Customer Record so that the pricing is always available to users.