This validation rule requires that a conditional formula is satisfied to validate bid submissions.
You can use any of the following methods to construct the formula:
- Click the required attribute, operator, or function buttons.
- Type attribute, operator, or function names directly into the Enter a formula field.
- Copy and paste a formula from another source, such as Excel, into the Enter a formula field.
Note that for this method, the copied functions must also feature in Sourcing Optimizer.
The conditional formula used for validation should evaluate to either 0 or a non-zero number, such as 1.
If the bidder’s submission is correct and passes validation, the formula should evaluate to 0. However, if there is an issue with the bid, and it 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.
If we want to ensure that Total Price, a formula column derived from Price Per Unit and Number of Units, is above 100, we could create a conditional validation rule with the following formula:
- IF( Total Price >= 100, 0, 1)
You can make conditional validation rules more complex by nesting conditional statements using the IF() function. Instead of setting the true or false values to a number, use the IF() function. This function can contain further IF() functions, if at some point an IF() contains just numerical true and false values.
If we want to set a minimum number of units on lots with the origin, Cork, we could create a conditional validation rule with the following 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 and passes validation. If the origin is Cork, the formula checks if the number of units is above the threshold, and evaluates to 0 if it is above 100, and 1 if it is not.
To add a conditional validation rule, complete the following steps:
- Go to Design > Bid Sheet.
- Click the Validation Rules tab.
- Click Add a rule.
- Enter a rule description in the Description field.
- Optional: In the Error message field, enter an error message to be displayed to bidders if their bid doesn’t pass the validation rule.
- Click Require a condition to be satisfied.
- In the Condition formula area, enter the formula in the Enter a formula field, or else use the buttons provided to build your formula.
- Click Test this formula to test if your formula is correct.
- Click Save changes.