I am trying to build a saved search that will export Item Pricing in a format that can be re-imported with no changes required to the data.
Our Item Quantity pricing is typically set up like this:
Items without quantity pricing just have the Qty 0 price set:
We also use Quantity Pricing when Items are on special offer by setting a lower price for Qty 1:
In this example the offer price is cheaper than all our normal bands, but often this is not the case. If the special offer for this Item was for 6.49, then we would add Qty1 pricing of 6.49 for Base, Trade, Trade+, Silver, and then the others would have the same as their Qty 0 price (because they are entitled to it at a lower price anyway). We have to do this rather than use promotions as NetSuite doesn't seem to have an option for 'best pricing' (to take the lowest price for an item that the customer is entitled to).
When importing pricing via CSV, the Quantity field needs to lowest Quantity for the Quantity Pricing Band (e.g. 0 and 10 for the top example, or 0 and 1 for the special offer example)
A saved search to extract this pricing uses this field: {pricing.quantityrange}, but this exports the quantity bands in a different format.
- If there is only Qty 0 pricing, then this is shown as 1+
- If there is Qty 0 and Qty 10 pricing, then this is shown as 1-9 and 10+
- If there is Qty 0 and Qty 1 pricing, then this is shown as 1-0 and 1+
I have been trying to build various formulas in a Saved Search to convert these formats to the format needed for import, but with no success.
SUBSTR({pricing.quantityrange},0,(CASE WHEN INSTR({pricing.quantityrange},'+') = 0 THEN INSTR({pricing.quantityrange},'-') ELSE INSTR({pricing.quantityrange},'+') END)-1)
This will extract the first digit before either the + or - character. However, in the third example above, both '1-0' and '1+' will return the result '1' (where I need 1-0 to return 0).
CASE WHEN SUBSTR({pricing.quantityrange},0,3) = '1-0' THEN 0 END
This will identify the (incorrect!) result of 1-0 and change this to 0
However, I cannot work out how to identify when the result of '1+' needs converting to '0' (if that is the only Quantity Price Band, shown as Qty 0 in NetSuite), or '1' (if there is a Qty 0 price in NetSuite which is shown as '1-0' in the search results, and this is a 'genuine' '1+' price).
Does anyone else have any working formulas or suggestions on how to extract Item Pricing in a logical fashion?