Creating and Updating Data Validation Rules

To implement business policies and practices, you can build data validation rules that are checked when conditions are met in forms. Validation messages can be generated if entered data violates validation rules. You can also build limits on submitted approval unit data using validation rules, and designate a reviewer, owner, or notifier to review data that meets some condition.

For example, data validation can ensure that a department’s capital expenses adhere to company policies by preventing planners from submitting budgets that contain capital expenditures that fall outside the company’s guidelines. Sample scenarios that can be addressed using data validation rules are described in Data Validation Rule Scenarios.

Defining data validation rules involves these main tasks:

  • Identifying the data cells or location that you want to display with validation messages or in different colors when conditions are met.

  • Identifying the cells that need to participate during rule evaluation, and defining the rule accordingly.

  • Creating the data validation rule at the location identified, as described in this topic.

To create and update validation rules:

  1. On the Home page, click Navigator Navigator icon, and then under Create and Manage, click Forms.
  2. Create or edit a form, and then on the Form and Ad Hoc Grid Management page, click Layout.
  3. In Layout, right-click the grid, row heading, column heading, or cell for which you want to add or update the validation rule.


    When you hover the cursor over cells in Layout, a context menu displays whether the cell contains a validation rule. To view the validation message, select Show Data Validation Messages. The context menu also displays when a single cell is selected.

  4. Select Add/Edit Validation Rules to create or update rules.
  5. To add a rule, click Add Rule icon and enter a name and description for the rule.

    If necessary, move the rule by selecting an option from Location. To create a rule similar to an existing rule, click Duplicate icon and then update the rule. To view rules, click View Rule. See Viewing Data Validation Rules.

  6. Update the rule.
    1. For Condition, select an option to begin the condition statement: If, Else If, Else, Then, Check Range, or Range.

      The first part of a rule must include an If condition. Rules must also contain some form of Then condition. See Conditions Supported by the Rule Builder.

    2. For Source Type, select an option for evaluation by the rule.

      The Source Type list displays the appropriate options for the Condition. For example, If conditions can include Current Cell Value, Cell Value, Column Value, Row Value, Member Name, Member, Cross-Dim Member, Account Type, Version Type, Var Reporting Type, UDA, or Attribute. For detailed information about each type of condition, see Conditions Supported by the Rule Builder.

    3. If applicable for the selected Source Type, enter a value in Source Value by selecting an option or entering a free form value.
    4. Select an operator for the evaluation: =, !=, <, <=, >, >=, Equals, Not Equals, Contains, Starts With, or Ends With, In, or Not In.
    5. Select an option for the appropriate Target Type for the rule.
    6. Update conditions by clicking an icon in the Actions column to the right of the condition builder:
      • Add Condition icon—Add a condition next to the current row.

      • Delete Condition icon—Delete a condition at the current row.

    7. Select conditions or condition blocks to update.

      To update condition blocks, click an icon in the Condition area, at the top of the condition builder:

      • Add Condition Block icon—Add a condition block within the validation rule starting with If. You can expand or collapse the condition. See Conditions Supported by the Rule Builder.

      • Delete Condition Block icon—Delete the selected condition block.

      • Cut Conditions icon—Delete selected conditions, and copy them to paste in a new location.

      • Copy Conditions icon—Copy selected conditions.

      • Paste Conditions icon—Paste selected conditions to a new location.

      • Group Conditions icon—Group the selection within a condition, and add a grouping parenthesis. In addition to groupings that you set, the If statement in the grouping block is grouped when a condition is grouped, and the Custom Grouping option is enabled.

      • Ungroup Conditions icon—Ungroup the selected conditions. The grouping parentheses are removed from the selected condition. One grouping is removed from the condition each time Ungroup is selected.

        You can also set your own grouping for conditions by selecting Custom Grouping, then setting up the grouping in the rule definition area.

        Selected conditions are displayed as shaded. To clear selected conditions, click once more to the left of the Condition column.

  7. Click Process Cell icon in the rightmost column to add processing instructions.


    If the icon isn't displayed, ensure that the rule is valid and that it permits cell processing instructions. For example, cell processing instructions are included for Else, Range, and Then conditions. The icon isn't displayed until all required columns are selected for a rule.

    See Formatting Cells and Setting the Promotional Path.

  8. When you're ready to enable the rule to make it available in the form, select Enable Validation Rule.


    While you're building a rule, you can save the rule without enabling it. After any errors are resolved and the rule is ready to be validated and used, you can enable and save the rule to make it available in the form. You can temporarily disable a rule by clearing Enable Validation Rule.

  9. When you finish updating the rule, validate the rule:
    1. Click Validate.

      The validation status displays at the top of the dialog box. You must correct errors before you save changes. If you close the dialog box without validating rules and fixing errors noted during validation, updates are not saved.

    2. After fixing any errors noted during validation, ensure that Enable Validation Rule is selected above the rule definition area to enable the rule for the application.
    3. After the rule is validated, click OK.
  10. Optional: In the Form and Ad Hoc Grid Management page, view and update rules:
    • In the Validation Rules pane on the right side of the Form and Ad Hoc Grid Management page, add, edit, or delete rules by clicking Add Validation Rule icon, Edit Validation Rule icon, or Delete Selected Validation Rule(s) icon.

    • If multiple rules are defined at the same location, you can change the order in which rules are processed when rules have the same precedence. To move a rule up, down, or to the top or bottom of the list, select the rule and click the arrows. See Order of Evaluation and Execution for Data Validation Rules.

    • Select form validation rules options:

      Table 16-1 Form Validation Rules Options

      Option Description
      Validate only for users with access to this form If the currently logged-in user does not have access to the form, do not execute validations associated with the form when validating the Approval unit.
      Validate only for pages with existing blocks When enabled, the system figures out which page combinations have potential blocks and runs the validations only for those page combinations. There are a few exceptions to this. If a page combination has any Dynamic Calc, Dynamic Calc and Store, Label only, or Store with one child member, then that page is always loaded.
      Validate only for cells and pages the user has access to When enabled, validations are run as the currently logged-in user and not as the administrator, which means the user’s security will be applied to the form members.
  11. In the Form and Ad Hoc Grid Management page, preview and validate the form, resolve any validation errors, and then save changes.

    For forms that have data validation rules enabled, rules are validated when the form is loaded or saved. Data validation rules are saved when the form is saved. See Creating Simple Forms.

    When users open the form, they can view and resolve validation messages using the Data Validation Messages pane. See Working with Planning.