Finally, John is asked to design a data validation rule to validate that the total compensation for employees in a particular department is within the allowed range. The rule evaluates Existing Employees in the Operations department. It validates that, if Total Compensation is > than Min allowed, and is <= ¾ of the compensation range for the employee’s grade, no action is needed.
If Total Compensation is greater than ¾ of the compensation range, a validation message is provided, and the planning units must be approved by a human resource manager. If the value is less than Min and greater than Max, an error is generated, and users cannot promote their planning units.
John opens the Employee Expenses Summary form in the Form Management dialog box. The form has employees and departments on the page, accounts (such as Total Compensation) on the row, and time period on the column. To make validations easier to build, John adds a calculated row to calculate ¾ of the compensation range, and adds Min Compensation and Max Compensation members to the form, as shown in the following figures. Min Compensation and Max Compensation for the employee’s grade are calculated using member formulas.
Data Validation Rule to Stop Promotion of Planning Units:
Data Validation Rule to Add the Human Resources Manager as Reviewer:
Form at Data Entry Time with Data Validations Applied and Validation Messages Shown:
For additional scenarios, see Data Validation Rule Scenarios.