12 Data Validation
When a business user works with data in a workbook, the add-in validates data in new and updated fields and raises a data entry error if it detects an invalid value. Business users will need to fix all data entry errors before they can upload successfully.
Cells that fail validation are marked with a red border. Rows that contain validation errors are also flagged as Invalid in the Status column. The add-in displays an error message in a popup if the business user selects the cell with the error.
The add-in validates data at these key points when the business user is working in a layout:
- On required fields in a row when the row is added to the form or table of a layout
- When the business user completes an edit on a field. At this point, the add-in validates all editable fields in that row.
- At the beginning of an upload
Local field properties are used for validation. See Configure Business Object Fields.
Note:
Validation that is enforced by the REST service is not triggered at the points mentioned here. REST service validation is generally triggered by the requests sent during upload. For details on upload failure handling, see Upload Changes.When a row is marked for a custom action, the custom action's payload fields also receive the same validation. See Custom Actions.
Here are the validation conditions the add-in checks for:
| Condition | Description |
|---|---|
| The entered value matches the data type of the field. |
If a value doesn't match the data type, the add-in displays a popup with the validation failure message. For example, if the business user enters a string, such as one thousand), in a field with a Data types include |
| A value has been entered for a field that is required for update or create. | If no value is entered, the add-in displays a popup with the validation failure message. During table or form row creation, the add-in automatically flags all required fields. Whether a field is required for update or create depends on the Required for update and Required for create check boxes on the Constraints tab of the Business Object Field Editor. See Configure Business Object Fields. |
| A value entered for a string-based field meets the minimum and maximum lengths configured for the field |
If a value is entered that has too few or too many characters, the add-in displays a popup with the validation failure message. Minimum Length and Maximum Lengths are set on the Constraints tab of the Business Object Field Editor. See Configure Business Object Fields. Note: When comparing the length of the field value with the minimum and maximum lengths, the add-in counts Unicode code units (encoded as UTF-16). Such a count usually matches what the business user expects. However, in cases involving Unicode extended characters (graphemes and supplementary code points), a single text element can require multiple code points. In these cases, a field value might be considered invalid when it appears to have fewer visible or voiced characters than the configured maximum length. |
| The value of a field with a list of values is a value available from that list. | If, rather than selecting a value from the list, the business user types in a value that is not in the list, the add-in displays a validation failure message. |
| The value of the field matches the criteria configured for the field in a custom field validation rule. | A custom field validation rule is an expression that restricts the range of allowable values during data entry. When a business user enters a value, the add-in evaluates the expression based on the value and, if the expression evaluates to true, the value is judged to be valid. If the expression evaluates to false, the value is invalid, and the add-in displays the validation failure message configured for the rule. See About Custom Field Validation Rules. |
A Note on Data Validation Interactions
Note that a field may have two or more data validation constraints set that affect how the add-in validates a cell during data entry. Suppose you have a field that is required for update and also has a minimum length of 10. If a business user clears the value, the add-in first evaluates the cell based on the Required for update setting. In this case, the cell is marked Invalid and a popup validation message is displayed (A value is required). The minimum length setting is not considered.
If a value is not required for update, a null value is now considered valid. However, when it is evaluated based on the minimum length constraint, it is considered invalid and a different popup validation message is displayed (The value must have at least 10 characters).