Zum Hauptinhalt wechseln
Dokumentation
Dokumentation
Adaptive Planning
Zuletzt aktualisiert: 2025-03-14
Set Up Modeled Sheet Data Validation Rules

Set Up Modeled Sheet Data Validation Rules

  • Select the
    Data validations in Sheets
     check box on the
    Visual Preferences
     page of
    Administration
    .
  • Remove any spaces from column codes in
    Model Management
     for columns that you want to use in validation rules.
  • To use planning configuration manager applications to manage data validated modeled sheets, select
    Plan in Adaptive Planning
     for your application
    Purpose
     on the
    Manage Workforce Planning Configuration
     task. You can't change this selection on existing applications.
  • Security:
    Model includes: sheets, accounts, dimensions, and formulas
     permission.
As a model administrator, you can create data validation rules for user-entered data on modeled sheet cells to ensure:
  • Data accuracy.
  • Data consistency.
You can enforce these validation rules on:
  • Modeled sheets.
  • Modeled sheets on dashboards.
  • Modeled sheet data API imports.
  • Planning data loaders that import to modeled sheets.
These rules apply to all versions for your modeled sheets. If you create validation rules for a sheet with existing data, we validate the data the next time you open or refresh the sheet.
When you trigger validations, we display:
  • Red borders around rows that trigger critical warnings. We don't allow you to save the sheet until you fix the invalid data.
  • Orange borders around rows that trigger soft warnings. You can save the sheet with this invalid data.
You can configure the validations to display custom help text to help you fix the invalid data. You can also filter on invalid data for faster corrections.
  1. Select
    Model Management
     from the main menu.
  2. To access the sheet builder for your modeled sheet, select either:
    • Level Assigned Sheets
    • User Assigned Sheets
  3. Select
    Edit
     next to your modeled sheet.
  4. Select
    Data Validation Rules
    .
  5. As you create or edit validation rules, consider:
    Option Bezeichnung
    Expression
    Enter your row dot notation boolean expression manually on this field or use the
    Expression Builder
    . When you refer to columns in your expression, use the codes for those columns. We evaluate your data based on the codes for these columns:
    • Attribute columns.
    • Dimension columns.
    • Initial Balance data entry columns, which we evaluate based on the code of the corresponding account.
    • Text Selector data entry columns.
    Data validation rules check if the data is within the acceptable values of this expression. If the data is outside the acceptable values, then we trigger a violation for that cell.
    We automatically lowercase strings when you save. This lowercasing doesn't impact your validations because the strings aren't case-sensitive.
    Example: Your modeled sheet includes a Row ID column and you want all row IDs to contain 4 characters. Enter
    Length (ROW.ID) = 4
    .
    Expression Builder
    Click the
    Expression Builder
     icon so that you can either:
    • Enter your row dot notation boolean expression manually. You can refer to the options available on the drop-down menus to ensure that you enter the formula expression correctly.
      Example: View the
      Column
       drop-down menu to ensure that you use the correct codes for your columns.
    • Use drop-down menus to insert the different parts of your row dot notation boolean expression.
    Help Text
    Enter help text that's fewer than 128 characters.
    Name
    Enter a rule name that's fewer than 65 characters.
    Type
    • True
      : We display the error when the data fails conditions. Example: For a rule that checks if the pay rate is over 100,000, we display the error when pay rate is under 100,000.
    • False
      : We display the error when the data meets conditions. Example: For a rule that checks if the pay rate is over 100,000, we display the error when pay rate is over 100,000.
    Action
    • Critical
      : We display red borders around the rows. We don't allow you to save the sheet until you fix the invalid data.
    • Warning
      : We display orange borders around the rows. You can save the sheet with this invalid data.
  6. (Optional) Activate or deactivate validation rules from the More Actions menus of the rules.
    Only 15 active rules can exist. You can use inactive rules as drafts.
We only apply validations to the rows that we currently display on your sheet view. To save a valid row when critical errors exist, change your sheet view to filter out the invalid rows, then save.
If you add, change, or delete validation rules for a sheet that a user already loaded, that user must refresh the sheet to apply the changes.
When you trigger validations, you can hover over the invalid data to view:
  • Whether this is a critical or warning validation.
  • The validation help text for that rule.
When you import modeled sheet data using the importConfigurableModelData API, you can choose to enforce or skip validation rules. When you choose to enforce validation rules, we analyze the data and identify all the cells that fail the rules before we import. If critical errors exist, we:
  • Don't import.
  • Provide a list of rows with errors so you can go to your data source and fix the issues.
To use validations rules for data imports, you must select the
Data validations in Import Data (requires Data validations in Sheets)
 check box on the
Visual Preferences
 page of
Administration
.