10 Data Validation

Oracle Visual Builder Add-in for Excel provides data validation during data entry based on the business object field definitions, custom action payload field definitions, and custom validation rules. Data validation catches errors during data entry when it's easy to correct a mistake rather than after a failed upload attempt.

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.

Description of the illustration data_validation.png

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 navigates away from 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. 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 Number data type, the add-in displays the message: "The value is not valid for the expected data type: Number" . Data types include String, Date-Time, Integer, Number, and Boolean. A field's data type is defined in the Business Object Field Editor for the field. See Configure Business Object Fields.
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.
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.

About Custom Field Validation Rules

A custom field validation rule is an expression you define for a business object or custom action payload field that restricts the range of allowable values during data entry. When a business user enters a value in a form field or table cell, Oracle Visual Builder Add-in for Excel checks the value against the configured rule and raises a data entry error if the value does not match the set criteria.

These rules can catch invalid values during data entry when it's easy to correct a mistake rather than having to find errors during upload.

Custom rules are expressions using the add-in's expression language that evaluate to true or false. 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 a popup with the validation failure message instead.

You can define a rule that compares the value the business user enters with:

• A constant for a number/Boolean/string/integer field
• Another field value in the same row
• The result of an expression involving other field values in the same row

Custom rules are supported on business object and custom action payload fields.

Supported Expressions

When constructing a custom field validation rule, these expressions are supported:

• this.Value returns the current field value, where "this" is a regular business object field or custom action payload field.
• this.BusinessObject.Fields['<FieldID>'].Value retrieves another field value in the same row as the current field, where:
  • this refers to the current field;
  • BusinessObject refers to the business object (the same row) of the current field;
  • Fields['<FieldID>'] refers to the ID of a field (<FieldID>) in the set of fields for the business object; and
  • Value refers to the value of the given field (<FieldID>).
• this.CustomAction.PayloadFields['<FieldID>'].Value gets a given payload field value (PayloadFields['<FieldID>'].Value) in the same custom action (this.CustomAction).

The custom field validation rule does not support:

• The Parent keyword (for example, this.BusinessObject.Parent.Fields['<FieldID>'].Value) to get a field value from a parent or other ancestor business object
• Accessing field values from child and other descendent business objects
• The CustomActions keyword (for example, this.BusinessObject.CustomActions['CustomActionID']) to access a custom action from a regular field

Sample Validation Rule Expressions

Custom rules use the add-in expression language. See About Expressions for more information.

Expression	Use
{ this.Value <= 500 }	This rule compares the value in the cell (this.Value) to a constant (500) and displays the validation failure message if the value is more than this amount. You would use this rule for the Amount field of an Expenses layout to limit the amount for each expense in an expense report to $500 or less.
{ this.BusinessObject.Fields['Amount'].Value > 1000 ? this.Value != '' : true }	This rule checks for a non-empty value in the cell (this.Value != '') and displays the validation failure message if the value in another field (this.BusinessObject.Fields['Amount'].Value) is greater than the set amount (1000). You would use this rule for the Justification field of an Expenses layout to require the business user to enter a justification if the amount of the expense is greater than $1000.
{ this.Value > 0 && this.Value < 50 }	This rule checks the value in the cell and displays the validation failure message if the value is outside a given range (between 0 and 50). You would use this rule for a Commission Percentage field to ensure the commission is larger than zero but less than 50%.
{ (this.Value - this.BusinessObject.Fields['OldSalary'].Value) / this.BusinessObject.Fields['OldSalary'].Value * 100 < 5 }	This rule compares the value in the cell (this.Value) to the value in another cell (this.BusinessObject.Fields['OldSalary'].Value) and displays the validation failure message if the increase is 5% or greater. You would use this rule on the New Salary field to ensure an employee's raise is within your company's salary cap.

Default Values

During the evaluation of rules, empty cells are treated as:

• zero (0) for numeric fields (Integer and Number data types)
• empty strings ('') for string type fields
• False for Boolean type fields

Note:

It is not possible to distinguish an empty cell from the default value for validation rules evaluation.

Create Field Validation Rules

You can define custom field validation rules to ensure your business users are entering valid values when they create or update a row or form in a layout.

For example, you may want to limit the amount for each expense in an expense report to $500 or less. To do this, you can enter a rule for the "Amount" field like this: { this.Value <= 500 } where this.Value refers to the value of the currently-selected cell. During data entry, the add-in marks the row as "Invalid" if the value entered exceeds $500.

If the business user selects the cell with the error, the add-in displays a popup with a description of the error. You can also provide a custom error message when you define a rule.

For help on the add-in expression language, see About Expressions.

To create a custom field validation rule:

1. Open the Business Object Field Editor of the field you want to set a validation rule for, then click the Constraints tab.
   
   

   
   Description of the illustration bo-editor-validation-rule.png

   
   

   Note:

   For a custom action payload field, navigate to the Custom Action Payload Field Editor instead.
2. Type in your validation rule in the Validation Rule field using the add-in expression language. The expression must comply with the add-in's expression language and evaluate to true or false.
   For example, to limit the value in an Amount field to $500 or less, type:

   { this.Value <= 500 }

3. To provide a custom error message, type your message in the Validation Failure Message field.
   Use this field to provide a brief explanation for your business users of what values are expected for this field. This value can be localized.
   
   

   
   Description of the illustration validation-rule-expense_amt.png

   
   

At runtime, if a business user enters a value that violates the validation rule, the add-in marks the row as invalid and displays a red outline around the invalid cell. If selected, the add-in displays an error popup with the validation failure message.

Description of the illustration validation-rule-error-popup.png

Notes on Custom Field Validation Rules

Here are some things to keep in mind when creating custom field validation rules.

Supported Fields

Fields with these data types are supported: String, Number, Integer, and Boolean.

Unsupported Fields

These fields are not supported:

• Fields with Object, Date (no time), Date-time, or Unsupported data types
• Fields with a configured list of values. The add-in already performs validation on these fields to ensure the value is a valid entry from the list.
• Discriminator fields from a polymorphic business object
• Ancestor fields

Limitations

• A rule expression for a field cannot refer to a field value from a different row or from a different layout.
• Multiple validation rules on one field are not allowed. However, you can use logical operators, && and ||, in one validation rule to the same purpose.

Validation Behavior

• Validation rules are not evaluated on download. So, if the downloaded data includes values that violate the rules, the violations are not highlighted after download completes.
• When a cell is modified, all editable cells in that row are validated.