1. Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
  2. Data Validation
  3. About Field Validation Rules

About Field Validation Rules

A field validation rule is an expression you define for a field that restricts the range of allowable values during data entry. When a business user enters a value, 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.

Field validation rules are available for business object fields, custom action payload fields, and row finder variables.

These 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. When comparing values, make sure the data types match. 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).

Validation rules are supported on:

  • Business object fields
  • Row finder variables
  • Custom action payload fields
  • Row variables

Supported Expression Syntax

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

Expression Description Supported Field Types
this.Value Returns the current field value, where "this" is a supported field
  • Business object field
  • Custom action payload field
  • Row finder variable
  • Row variable
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. The field must be shown as a column in the layout.
  • Value refers to the value of the given field (<FieldID>)
  • Business object field
  • Custom action payload field
  • Row variable
this.CustomAction.PayloadFields['<FieldID>'].Value Retrieves a given payload field value (PayloadFields['<FieldID>'].Value) in the same custom action (this.CustomAction) Custom action payload field
this.Finder.Variables['<VariableID>'].Value Retrieves a given finder variable value (Variables['<VariableID>']) in the same row finder (this.Finder).

Note:

Make sure a validation rule for a variable in a basic search does not reference a variable configured for an advanced search since the rule would fail in basic mode. See Configure Row Finders for a Business Object.
Row finder variable

The 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

Field validation rules support the Today() and Now() functions.

Sample Validation Rule Expressions

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

Note:

When creating expressions for a field validation rule, keep in mind that empty cells may produce undesirable results. For information about handling null values in expressions, see Handling Null Values in Expressions.
Expression Use
{this.Value == null ? true : 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.

If the cell is empty, the validation rule will pass.

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 ?? 0) > 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.

If the Unpaid Amount field is empty, a default value of "0" is applied. Because this is less than "10000", the validation rule fails. "Group 2" is therefore expected in the cell.

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 == null ? false : 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) or the cell is empty.

You would use this rule for a Commission Percentage field to ensure the commission is larger than zero but less than 50%.
{this.Value == null ? false: 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.

A validation failure message is also shown if the cell is empty.

You would use this rule on the Hire Date field to ensure a future date isn't used by mistake.
{this.Value == null ? true : this.Value > Now()}

When the cell has a date-time value, this rule compares this value to the current moment and displays a validation failure message if the date and time provided is after the current moment.

If the cell is empty, no validation failure message is displayed.

You would use this rule on the Deadline field to ensure a future date is used.

For the Category variable:

{ this.Value != null || this.Finder.Variables['Item'].Value != null }

For the Item variable:

{ this.Value != null || this.Finder.Variables['Category'].Value != null }

Consider a row finder with two variables: "Category" and "Item", where a value is required for at least one of the two variables.

If both Category and Item are empty, download should be blocked from proceeding. If either variable has a value, the download can proceed.

To achieve this goal, you can configure a validation rule for each variable that references the other as shown here.

An appropriate validation failure message might be " Provide a value for either the Category or the Item finder variable".

Create Field Validation Rules

You can define 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 == null ? true : 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.

Note:

This example expression uses a conditional to set the value of the expression to "true" (this.Value == null ? true) if the selected cell is empty (null).

For help on the add-in expression language and null handling in expressions, see About Expressions and Handling Null Values in Expressions.

To create a 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 bo-editor-validation-rule.png follows
    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 == null ? true : 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.
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 validation-rule-error-popup.png follows
Description of the illustration validation-rule-error-popup.png

Notes on Field Validation Rules

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

Supported Fields

Fields with these data types are supported:

  • Boolean
  • Date (no time)
  • Date-time
  • Integer
  • Number
  • String

Unsupported Fields

These fields are not supported:

  • Fields with 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 or variable cannot refer to a field value from a different row, finder variable, or 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

  • Legacy null handling is not supported for validation rules in row finder variables.
  • 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.