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.

You can define a rule that compares the value the business user enters with a constant or with the result of an expression involving other field values in the same row. Expressions must compare values that match the field's data type. For example, an expression on a integer field such as { this.Value <= 500 } compares an integer entered by the business user (this.Value) with an integer constant (500).

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 descendant 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 a 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["UnpaidAmount"].Value > 10000 ? this.Value == "Group 1" : this.Value == "Group 2" }	This rule checks the value in an Invoice Group cell (either this.Value == "Group 1" or this.Value == "Group 2") to ensure the correct value is provided based on the value in the Unpaid Amount cell ( this.BusinessObject.Fields["UnpaidAmount"].Value > 10000) in the same row. If the unpaid amount is over $10,000, then the invoice group should be "Group 1"; otherwise, the invoice group should be "Group 2". If the wrong group is selected, the add-in displays a validation failure message. You would use this rule for the Invoice Group field of an Invoices layout to require the business user to enter the correct invoice group based the unpaid amount of an invoice.
{ this.Value > 0 && this.Value < 50 }	This rule checks the value in the cell and displays a 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 a 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.
{ this.Value < Today() }	This rule compares the date value in the cell to today's date and displays a validation failure message if the date is after today. You would use this rule on the Hire Date field to ensure a future date isn't used by mistake.

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
• the minimum date (Jan 1, 0001) for date-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: Boolean, Date (no time), Integer, Number, and String.

Unsupported Fields

These fields are not supported:

• Fields with Date-time, Object, 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.