It is possible to disallow a combination of options in the pricing calculator pricing engines, causing the price engine to show an error when those options are selected. This demonstrates how to disallow options in both a grid and a calculator row situation in a price calculator pricing engine.
Using a Grid Parameter
Using a grid parameter is the simple approach. Having no value input for a selected grid option in the pricing calculator will make that option disallowed and cause an error to be returned if that option is selected when using the price engine.
Selecting a quantity of 500 with a paper choice of Matte will then show the error, "That combination of choices is not allowed" when using the price engine, as shown below. Note that in the screenshot, the quantity of 250 is shown, that is because when the disallowed quantity of 500 is selected, the error is shown, and the quantity dropdown returns to the first value available.
Using a Calculator Row
In the calculator row of a price calculator pricing engine is an Applies To column. The applies to column indicates the quantity value that can be applied to the settings in the calculator row. By default the Applies To value is going to be Q1 on any pricing calculator price engine. But Applies To could be referencing any quantity value from Q1 to Q5, it could be reference some kind of calculation of the quantity values like Q1*Q2*Q3, or it could be referencing the result of a calculation embedded in the Applies To column.
This example uses a calculation embedded in the Applies To column. The calcuation is ifn(Paper="Matte",Q1,-1) and is a basic Excel calculation that returns a numeric value using an ifn() numeric function. The calculation follows a pattern of ifn(CONDITION,TRUE,FALSE), and it checks against the value selected in the grid one option that is labeled as Paper. If Paper is equal to Matte, then the Q1 value selected for the quantity is returned. If Paper is not equal to Matte, then a negative value is returned. The negative value of -1 will make the price engine return an error. This allows for forcing an error if Folding is Yes when Gloss is selected.
Then if Folding is set to Yes when Gloss is selected for the Paper option, the price engine gets a negative value. The negative value is outside of the range of quantities set in this price engine, so an error is returned by the pricing engine when that option is selected.
The error shown by a price engine is always "That combination of choices is not allowed", and that error string message cannot be customized in a price calculator pricing engine.
Default Calculations in Applies To
When you first create a new price calculator pricing engine, the Applies To column for any calculator rows will be Q1. If you select the Applies To cell for any calculator row, you will notice a dropdown menu item becomes available with a number of defaults like Q1 to Q5, Q1*Q2, and other basic calculations.
If you receive an error from Excel when trying to add a new calculation to the Applies To cell in your price calculator pricing engine worksheet, it will be necessary to not paste the calculation into that cell, but to select that calculation like you would one one of the default options. In order to do that, you need to replace one of the default calculations in the Excel sheet with the calculation that you are going to use in your Applies To column.
This is simple to do. You scroll down the price calculator pricing engine until you find the rows with the default calculations that are somewhat hidden down the worksheet. Replace one of those default calculations with your new calculation. For example, you would not want to replace the Q1 value in that listing, as that is probably being used in your price engine. Replacing -- pasting over -- the Q1*Q2 calculation would get your new calculation available on the Applies To dropdown, but would not affect your price engine, unless you were actually using Q1*Q2 in your price engine calculator rows.
Example Download
The pricing calculator price engine Excel file used in this example can be downloaded from this link as
ExampleDisallowed.xls.