Configuring a Validation Rule
When editing a validation rule you will be asked to input a number of basic settings (which are common to all validation rules) and some type specific settings.
First of these basic settings is the rule description. This is the text which will be displayed on the rule list page. It is also used to describe active validation rules to bidders, and will be visible on the bidding overview and in the bid sheet workbook.
Additionally you can specify an error message. This is an optional field and can be used to communicate extra information to bidders whose bids fail validation for this rule. This text will only be seen by bidders if there is a problem with their bid.
You will also be asked to specify the rule type. The rule types are:
- Require uniform values for a specific column across groups of lots.
- Require complete bid coverage on groups of lots.
- Require a condition to be satisfied.
These options are discussed in more detail below.
Require uniform values for a specific column across groups of lots.
This rule type ensures that groups of lots all have the same value in one of the bidder input columns. You will be prompted to select a column for which the values should be uniform. Clicking on the input field will open a drop down list of all the columns which are available.
Next input field allows you to select how you would like to group lots. You must select at least one purchaser input column here, but you may select multiple columns if you wish to further refine the groups of lots. Lots will be grouped such that all lots in a group will have the same value in all of the selected columns.
It may be the case that you wish to exclude lots with a specific value from this consistency rule (i.e. lots with “n/a” in the bidder input column). This value can be set in the final input field, which is optional.
Require complete bid coverage on groups of lots.
This rule type can be used to require bidders to submit bids on all lots in a group.
You will be asked to select columns to group lots by, similar to configuring the uniform value rule. Selecting one or more columns forces bidders to bid on all lots with the same set of values across these columns if the they wish to bid on any of them.
Require a condition to be satisfied.
This rule type allows for a conditional formula to be used to validate bid submissions. The formula input field can be used to specify criteria for valid bid submissions. This field functions similarly to other formula fields in Sourcing Optimizer.
All number and currency type attributes and cost functions are displayed below formula field. Construct a condition formula by combining these with constants, operators (+, -, /, *, >, <, etc.), predefined functions (IF, MIN, MAX, AVERAGE, etc.) and other user-defined functions.
You can construct the formula by:
- Clicking on the appropriate attribute, operator or function button.
- Typing attribute, operator, and function names directly into the formula bar or
- Copy-and-pasting from another source such as Excel into the formula bar, as long as the names and functions exist in Sourcing Optimizer.
The formula which is being used for validation should evaluation to either zero or non-zero number (i.e. 1). If the bidder’s submission is correct and passes validation then the formula should evaluate to 0. However, if there is an issue with the bid, and fails to meet the intended criteria the formula should return something other than 0, usually 1. This can be done using the IF() function. This function takes a condition, a value for when the condition is true and a value for when the condition is false, in that order.
For example, if we want to ensure that the “Total Price”, a formula column derived from “Price Per Unit” and “Number of Units”, is above a particular value then we could create a rule with the formula ‘IF( Total Price >= 100, 0, 1)’
Validation rules can be made more complex by nesting conditional statements (using the IF() function). Instead of setting the true or false values to a number, another IF() function can be used instead. This function can contain further IF() functions, as long as at some point an IF() just contains numerical true and false values.
For example, if we wished to set a minimum number of units on lots with a particular origin we could create a rule with the formula ‘IF( Origin="Cork", IF( Number of Units>100, 0, 1), 0)’. If the Lot does not have “Cork” as the origin, the formula evaluates to 0 (passes validation). If the origin is cork, then check if the number of units is above the threshold, and evaluates to 0 if it’s above it, and 1 otherwise.