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.
Data Validation Rule at Design Time:
Form at Data Entry Time with Data Validations Applied:
If John is not 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 do not 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.
For additional scenarios, see Data Validation Rule Scenarios.