Data Validation Rule Scenarios

These scenarios provide examples of how data validation can help implement business policies.

Scenario 1

John is hired by a company called Acme, Inc. as a consultant to design forms and implement data validation rules that enforce some of the company policies. He is asked to implement a validation rule that flags Actual amounts in red if the Total Cost in actuals exceeds the budgeted amount. This test must be repeated for each year and time period in the application. John designs the form and adds a data validation rule at cell level using a cross-dimension member, as shown in the following figures.

Form Layout at Design Time:

Data Validation Rule at Design Time:

Form at Data Entry Time with Data Validations Applied:

Tips:

• John can split Total Cost into its own segment and apply the data validation rule at that segment for a slight performance gain. However, doing so would increase maintenance as new accounts and scenarios were added to the form.

• If the requirements changed such that only the YearTotal Period in Actual had to be flagged in red, John would have two options. The best option is to add an IF entry to check if the Period member is YearTotal. Another option is to split the YearTotal member into a separate column for better performance. However, doing so would break the spreading logic, the column header for Year would repeat, and the form would be harder to maintain as new years were added.

Scenario 2

After reviewing the form designed by John in Scenario 1, Acme decides that they want Budget on the column instead of the row. To implement this requirement, John can move members within the axes to change the form layout. However, he doesn't need to update the data validation rules. John updates the form as shown in the following figure.

Form Layout at Design Time:

Form at Data Entry Time with Data Validations Applied:

Scenario 3

Following the successful rollout of these forms, John is asked to implement the next policy, which is to ensure that this year’s Budget amounts are not significantly higher than previous year’s Actual amounts. If the difference is greater than 5%, then flag the difference in red.

John decides to use a member with a member formula to calculate the variance between this year’s Budget and the previous year’s Actual amount. He adds this member formula:

@varper(@Prior("Actual", 1, @Relative("Year", 0)), budget)/100;

John designs the form and adds a data validation rule at cell level, as shown in the following figure. He uses Member Name to apply the validation only to Total Cost.

Form Layout at Design Time:

Data Validation Rule at Design Time:

Form at Data Entry Time with Data Validations Applied:

Tips:

• If John isn't allowed to change the outline, or if he experiences performance issues related to member formulas, he can use a formula column. See Designing Forms with Formula Rows and Columns.

• John defines the rule at the Variance Percent column for these reasons.

  • It improves performance. The rule is evaluated only on the cells in the Variance Percent column. If the rule had been assigned to YearTotal, it would have to be evaluated for all Time Periods for the current year budget.

  • It helps users respond to the data validation message. John can add a message to the Variance Percent column stating that the variance is higher instead of adding it to YearTotal. This way, users don't have to look for Variance Percent to determine the difference.

• John could have flagged both YearTotal and Variance Percent in red if this had been part of the requirement.

Scenario 4

In addition to flagging the cell in red, the rule is also required to prevent anyone from promoting the approval unit if this year’s Budget is significantly higher (> 5%) than the previous year’s Actual amounts. To implement this requirement, all John needs to do is edit the data validation rule’s processing instructions and select Do Not Promote, as shown in the following figure.

Data Validation Rule at Design Time:

Scenario 5

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 approval 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 can't promote their approval 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.

Form Layout at Design Time:

Data Validation Rule to Stop Promotion of Approval 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: