AX1142

Using Data Validation in spreadsheet-formatted grids

Certain features of Microsoft Excel's Data Validation are supported for use in Formatted Grid components, if the grid uses the legacy spreadsheet formatting option:

  • List: You can use the List option of Data Validation to create a drop-down list in a formatted grid.
  • Decimal: You can use the Decimal option of Data Validation to define valid numeric inputs in a formatted grid.

To set up these options, configure the cell using Excel's Data Validation feature as normal (from the Data tab of the Excel ribbon). The specific configuration requirements for use in Axiom forms are detailed below.

If you are using the Axiom Windows Client instead of the Excel Client to configure the form, you can access Data Validation features by right-clicking the cell and selecting Range Explorer. Within the Range Explorer dialog, click Validation. The settings are similar to those presented in Excel.

IMPORTANT: The spreadsheet formatting option for Formatted Grid components is deprecated, and only exists to support backward-compatibility for older forms. All new formatted grids should use the default thematic formatting, where formatting is determined by assigning row and column styles within the Grid data source.

Using Data Validation to create a drop-down list

You can use the List option of Excel's Data Validation feature to create a drop-down list of choices within a Formatted Grid component.

  • For the list Source, you can specify a comma-separated list of values, or you can use the Indirect function to point to a range (for example: Indirect("Info!A1:A10"). The Indirect function must be used with all ranges, whether on the same sheet or cross-sheet.
  • The cell that is configured for Data Validation must be unlocked so that it will be editable for the form user.

When the form user clicks on the cell configured with Data Validation, a drop-down list will present the list of valid values. The user can select an item from the list. The selected item is submitted back to the source file and placed in that cell.

Keep in mind that until the user clicks on the cell, no visual cues are present to tell the user that the cell is editable. The list arrow does not display until the user clicks on the cell. You may want to format the cell using a convention that indicates the cell is editable (such as a yellow background), and/or place text in the adjacent cell (such as "Select a category >>").

NOTE: The Excel Data Validation feature should only be used for simple lists that do not relate to data stored in Axiom Financial Institutions Suite. The Select content tag should be used for most drop-down lists in formatted grids, due to the many additional features supported by the tag, and for the ability to source the list from Axiom Financial Institutions Suite data.

Using Data Validation to validate numeric inputs

You can use the Decimal option of Excel's Data Validation feature to define valid numeric inputs for cells of a formatted grid. For example, you can specify that the input must be greater than or less than a specific value, or that the input must be within a range of values. This validation is performed before any updates are submitted to the source file.

The following rules apply when setting up numeric Data Validation:

  • Only the Decimal option is supported in Axiom forms for purposes of validating a non-list input. The options for whole number, date, etc. are not supported and will be ignored.

  • The validation values can be constant or can use a formula cell reference to read the values from the source file. If you use a cell reference, it is recommended to reference a cell within the current sheet. If you must use a cross-sheet reference, the reference must be placed within an INDIRECT function. Note that the Ignore blank setting is ignored in all cases.

  • A custom error message must be defined on the Error Alert tab of Data Validation. If no custom error message is defined, then the validation will not be enforced in the Axiom form. Note that the alert Style setting is ignored; all validation errors display in the same message style within Axiom forms.

  • The Input Message settings are not supported in Axiom forms and will be ignored.

When the form user exits an editable cell where Data Validation is defined (for example, by clicking out of the cell, or by pressing the Enter or Tab keys), the validation rules are applied to the cell contents. If the contents are invalid, the custom error message displays in a message box. The user can click OK in the message box or press the Esc key to dismiss the message and be returned to the cell. The user can then change the value to a valid value, or they can press the Esc key to revert to the previous cell value.

NOTES:  

  • Validation is only performed when the cell is edited. If the cell already contains an invalid value, it is not validated until you attempt to edit that value.
  • When using tablets, there is no way to revert to the previous cell value once it has been edited.
  • Custom save validation can be used as an alternative to Data Validation. Using custom save validation, you define conditions to be checked as part of the save-to-database process. If a value does not meet the defined condition, then the save is stopped and an error displays to the user. For more information, see Using custom save validation.