1. Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
  2. Use Expressions in an Integrated Workbook

22 Use Expressions in an Integrated Workbook

You can use expressions in a number of places in your workbook configuration such as in search queries, REST request headers, lists of values, and so on.

A simple expression may compare a value in a field to a constant, like this: { this.Value <= 500 }. This expression might be used in a custom validation rule to ensure an entered value doesn't exceed the given threshold. See Create Field Validation Rules.

Here are some other use cases for expressions:

  • As a row finder variable's default value that references a workbook parameter. If a row finder is configured, then the user is presented with a Search prompt that displays the value of the referenced workbook parameter as the row finder variable value. See Use a Workbook Parameter Value for a Row Finder Variable.
  • As part of a REST request header to include a range start date stored in a row variable. This feature is intended to provide support for workbooks with date effective objects. If you create a layout for a date effective object, you may need to create a REST request header of type Effective-Of that includes a range start date. See Configure a Request Header.
  • As the default value for a search condition in the Search Editor or a download parameter in the Download Parameter Editor. See Configure a Search for a Layout and Use Download Parameters to Limit Downloaded Data.
  • In the filter query parameter for a list of values. You can use an expression to filter the list of values based on the value of another cell in the row. See Configure a Cascading List of Values.

About Expressions

An expression is a string enclosed in curly braces ({ }) that can be evaluated to a single value at runtime. Expressions can reference configuration properties and dynamic runtime data.

The value of an operand or an intermediate result in an expression can be a Boolean value, string, or integer. However, the results of expressions may be converted to strings and concatenated if needed when resolving the entire property value. See String Representations.

For any given configuration property that supports expressions, you must escape any curly braces (\{ or \}) that you wish to use literally.

Let's consider an example of a list of values for an employee JobId field that displays all job titles from the jobId field from the Jobs business object. To list only the job titles for a given department based on the DepartmentId of the current row, you could use a query parameter with the following expression: 

DepartmentId={ this.BusinessObject.Fields['DepartmentId'].Value }

where:

  • this represents the currently selected field;
  • BusinessObject represents the business object to which this field belongs;
  • Fields['DepartmentId'] is the field (DepartmentId) associated with the business object; and
  • Value is the value of the field.

You can also use Parent in an expression to refer to an ancestor business object ("parent" or higher) in a business object hierarchy. For example, to refer to a field in the parent business object you might use something like this: 

ProjectNumber={ this.BusinessObject.Parent.Fields['ProjectNumber'].Value }

Parent can appear multiple times in the expression depending on the level you want to refer to in your business object hierarchy. To refer to the current business object's great grandparent business object, you'd use it three times: 

ProjectNumber={ this.BusinessObject.Parent.Parent.Parent.Fields['ProjectNumber'].Value }

Expressions can refer to a:

  • Business object field (BusinessObject.Fields['DepartmentId'])
  • Row finder variable (Finder.Variables['CountryId'])
  • Workbook parameter (Workbook.Parameters['Dept'])
  • Row variable (BusinessObject.RowVariables['rangeStartDate'])

Please note when using a collection element like Fields['<fieldid>'] that if the field with the specified ID is not found the result is an error. The only exception is Workbook.Parameters. If the named workbook parameter is not found, the result is null.

Note:

Some workbook configuration properties support expressions and others do not. Those properties that support expressions may support all reserved words or only a subset of them. Consult the documentation for each property to determine what is, and is not, supported.

A Note on Spaces in Expressions

Keep in mind that spaces outside curly braces in an expression are included in the final string. So, for example, DepartmentId= { this.BusinessObject.Fields['DepartmentId'].Value } (space after the equals sign) would, given a department ID of 100, yield DepartmentId= 100.

The extra space may or may not be important to the service.

Spaces immediately inside curly braces are not significant. For example, { this.BusinessObject.Fields['DepartmentId'].Value } is equivalent to {this.BusinessObject.Fields['DepartmentId'].Value}.

Literal Values in Expressions

Literal values of certain data types are supported in expressions.

Support is included for these data types:

  • Boolean
  • String
  • Integer or floating-point number
  • Date
  • DateTime

Boolean and number literal values must be in the form described here. For example, if you are in a country that uses a decimal comma (,), you must still use a decimal point or period in your expression.

Data Type Description
Boolean
Supported values (case-sensitive, no quotes):
  • TRUE
  • True
  • true
  • FALSE
  • False
  • false

Note:

It's recommended that you use capital case only (TRUE and FALSE).
String String literals inside expressions must be enclosed in single quotes ('). Single quotes inside string literals must be escaped ( \').
Integer or Floating-Point Number

Only the Western Arabic numerals (0-9) can be used. Other digits are not supported.

These symbols are supported:

  • Leading negative sign (-)
  • Decimal separator (.)
  • Exponent (E or e followed by an optional sign and exponential digits)

    Note:

    This is allowed only when a decimal separator is present; for example, when the value is a floating-point number.

These symbols are not supported:

  • Leading positive sign (+)

    Note:

    The plus sign when used as an operator is supported.
  • Thousand separator such as a space, period, comma, or underscore
  • No digit after the decimal separator
Date

A date type literal value must start with a prefix letter d followed by a single-quoted ISO 8601 date ('yyyy-MM-dd'), such as:

d'2020-10-24' or d'2000-01-01'
DateTime

A date-time type literal value must start with the prefix letters, dt, followed by a single quoted ISO 8601 date-time in UTC ('yyyy-MM-ddTHH:mm:ssZ'), such as:

dt'2020-10-24T12:34:56Z'

Note:

There is no millisecond component. Always include the T between date and time, and the Z at the end (indicates that it's UTC).

Note:

These rules only apply to literal values used in expressions. They do not apply to data formats used in an Excel cell. For example, you must write 135000 without thousand separator in this validation rule { this.Value > 135000 } but that validation rule can be used on an integer field that shows 150,000 or 150.000 in cell.

Operators in Expressions

The Oracle Visual Builder Add-in for Excel expression language supports a number of operators. Refer to this table for details.

Operator precedence is high to low.

Operator Use
[ ] . ?. Collection access, object member access, conditional object member access
( ) Grouping to change precedence
- ! Unary minus, negation
* / Math (multiplicative)
+ - Math (additive), also + for string concatenation
< > <= >= Relational
== != Equality
&& Logical AND
|| Logical OR
?? Null-coalescing
? : Ternary conditional

String Representations

When Oracle Visual Builder Add-in for Excel evaluates an expression that includes non-string values—such as Boolean values, dates, and numbers—it may convert these values to strings when, for example, the configuration property expects strings.

Review this table for information on how each data type value is represented as a string.

Data Type String Representation Example
Boolean true or false (note that Excel may capitalize the first letter or convert to other values) false
Date-time Full UTC string representation (ISO 8601) 2023-10-24T12:33:19Z
Date (no time) Date-only string representation (ISO 8601) 2023-10-24
Integer No thousand separator 12300000
Number No thousand separator; period as decimal separator 0.123

Numbers in Expressions

Some number formats are not supported in expressions. Refer to this table for supported and unsupported formats.

Here are some examples of how numbers are supported in expressions:

Supported Not Supported
0.123 0,123

.123
123

123.0

 123.
-456 +456 (part of literal value instead of doing addition)
1234 1,234

1 234
1234.567 1,234.567
3.14E2 03.14E2
1.0e10 1e10

Dates in Expressions

Oracle Visual Builder Add-in for Excel supports the use of date type values in expressions. So, for example, you can pass date values to lists of values filters or REST request header fields with this support.

Literals

A date type literal value must start with a prefix letter d followed by a single-quoted ISO 8601 date ('yyyy-MM-dd'), such as:

d'2020-10-24' or d'2000-01-01'

Operations

The following operators are supported: +, -, <, <=, >, >=, ==, and !=.

Supported +/- operations are:

  • Date + Integer
  • Integer + Date
  • Date - Date (returns the difference in days as an integer value)
  • Date - Integer

Here are some examples of expressions using supported operators. Resulting dates are in the default yyyy-MM-dd format.

Operation Expression Result
Date + Integer {d'2023-10-24' + 2} 2023-10-26
Integer + Date {-2 + d'2023-10-24'} 2023-10-22
Date - Date {d'2023-10-24' - d'2023-10-20'} 4
Date - Integer {d'2023-10-24' - 2} 2023-10-22
Date >= Date {d'2023-10-24' >= d'2023-10-22'} True
Date != Date {d'2023-10-24' != d'2023-10-22'} True

Functions in Expressions

The add-in supports a couple of functions:

  • Today ()
  • Format(object obj, string formatString)

Note:

Function names are case-sensitive.

The Today () function returns today's date.

Suppose you want to create an expression for a search parameter that returns rows with hire dates that are later than 90 days before today's date. You would create a search parameter with a parameter name "q" and a parameter value of: 

HireDate > '{ Today() - 90 }'

Note:

In this example, you would select the Allow expressions in Parameter Value check box.

The Format function returns the string representation of the given date in a given format using "invariant" culture (a culture that is culture-insensitive). See InvariantCulture.

It includes two arguments:

  • obj is a date. This can be a literal, a cell value from a field whose data type is Date (no time), the result of Today(), or the result of +/- operations (see the Operations section in this topic).
  • formatString is the date format. Supported formats are:
    • yyyy-MM-dd (default)
    • MM-dd-yyyy
    • dd-MM-yyyy

Take, for example, a service that requires a date in the dd-MM-yyyy format. You would use the Format function to provide this format rather than the default format, yyyy-MM-dd, like this: 

HireDate > { Format(this.BusinessObject.Fields['HireDate'].Value, 'dd-MM-yyyy') }

If the HireDate value for the current row is "2023-10-24", the final value of the line is: 

HireDate > 24-10-2023

Note:

The resulting string does not include quotation marks. If the service requires quotes, add them explicitly. For example, to return a value with quotes, like HireDate > '24-10-2023', use: 

HireDate > '{ Format(this.BusinessObject.Fields['HireDate'].Value, 'dd-MM-yyyy') }'

Notes on Dates in Expressions

  • Once you configure a property with an expression that relies on date values or date operations, the workbook is no longer compatible with add-in versions prior to 3.8.
  • Date values do not have a time part. Date values do not have a time zone.
  • Literal input, operations, and Format () are NOT supported for date-time values (from fields whose data type is Date-time).
  • There is no function that parses a string and returns a date.

Dates and Times in Expressions

Oracle Visual Builder Add-in for Excel supports the use of date-time type values in expressions. So, for example, you can pass date-time values to lists of values filters or REST request header fields with this support.

Time Values

The add-in doesn't store time zone information for date-time values. Time values are local. Note that:

  • The add-in converts Coordinated Universal Time (UTC) values from the literal value to the local time value.
  • During calculation, date-time values are processed as local values.
  • The add-in converts local date-time to UTC date-time in its canonical string form when it's included in REST requests.

Literals

A date-time type literal value must start with the prefix letters, dt, followed by a single quoted ISO 8601 date-time in UTC ('yyyy-MM-ddTHH:mm:ssZ'), such as:

dt'2020-10-24T12:34:56Z'

Note:

There is no millisecond component. Always include the T between date and time, and the Z at the end (indicates that it's UTC).

Operations

The following operators are supported: +, -, <, <=, >, >=, ==, and !=.

Supported +/- operations (order matters)

  • DateTime + Integer
  • Integer + DateTime
  • DateTime - Integer
  • DateTime - DateTime (returns the diff in seconds as an integer value)

Here are some examples of expressions using supported operators.

Operation Expression Result (local time is PDT or UTC-7:00) Notes
DateTime + Integer {dt'2023-10-24T12:34:56Z' + 2} 2023-10-24 05:34:58 (2023-10-24T12:34:58Z)

The integer (seconds) is added to the date-time value.

Internally evaluating 2023-10-24 05:34:56 + 2
Integer + DateTime {2 + dt'2023-10-24T12:34:56Z'} 2023-10-24 05:34:58 (2023-10-24T12:34:58Z) Internally evaluating 2 + 2023-10-24 05:34:56
DateTime - Integer {dt'2023-10-24T12:34:56Z' - 2} 2023-10-24 05:34:54 (2023-10-24T12:34:54Z)

The integer (seconds) is subtracted from the date-time value.

Internally evaluating 2023-10-24 05:34:56 - 2
DateTime - DateTime {dt'2023-10-24T12:34:56Z' - dt'2023-10-24T12:34:50Z'} 6 Internally evaluating 2023-10-24 05:34:56 - 2023-10-24 05:34:50
DateTime > Date {dt'2023-10-24T12:34:56Z' > d'2023-10-22'} true

Internally evaluating 2023-10-24 05:34:56 > 2023-10-22 00:00:00

Note:

When a date (no time) value is compared with a date-time value, it's first appended a time of day component of 00:00:00 (local).

Functions in Expressions

The add-in supports the Now function, Now (), that returns the current date and time as a date-time value. There are no parameters for this function.

Note:

Function names are case-sensitive.

Suppose you want to create an expression that displays a True or a False for an "overdue" field if a deadline has passed. In this case, you might want to compare a date-time value in the same row ("deadline" field) with the current date and time, like this: 

{ this.BusinessObject.Fields['deadline'].Value < Now () }

In this expression, if the date-time value in the "deadline" field is earlier than now, the expression evaluates to True. If the "deadline" field value is "2024-04-15 09:00:00" and the current date and time (provided by the Now () function) is "2024-04-15 14:00:00" (both using local date-time), the expression result is True and the item is overdue.

Limitations

Setting the time zone is not supported.

Reserved Words and Properties Used in Expressions

The Oracle Visual Builder Add-in for Excel expression language includes some reserved words and properties. Refer to this table for how these are used in add-in expressions. Please note that this list is not exhaustive.
Reserved Word Note
this

Represents the property owner depending on the configuration context. For example, when defining a field's configuration property, "this" represents the field.

See specific configuration properties for details.
BusinessObject Represents the business object to which the currently selected field belongs
Parent

Represents the parent business object of the currently selected field's business object in a business object hierarchy.

Use additional instances in your expression to refer to higher level business objects, such as Parent.Parent for the grandparent business object and so on.

Fields Represents fields of a business object
Value The value of the referenced business object field, workbook parameter, row variable, and so on
SelectWindow Search-and-select window in a list of values
Finder Represents the row finder to which the currently selected variable belongs
RowVariables Represents row variables configured for a business object
Workbook Represents the integrated workbook
Parameters Represents workbook parameters stored in the workbook

Handling Null Values in Expressions

Oracle Visual Builder Add-in for Excel supports the use of null in expressions to refer to null values. This allows you to write expressions that handle null values that may arise when the expression is evaluated.

You can use a number of approaches to handle a null value such as using a conditional statement to substitute a non-null value for the null. For more information on using null in expressions, see Null Values in Expressions.

Before version 4.1, the add-in automatically replaced null values with a fixed default value appropriate for the data type—a zero (0) for an integer field and a space (" ") for a string. By default, older workbooks keep this behavior. If you want to adopt the new behavior, you can turn off the legacy behavior from the Workbook Info window. See Disable the Legacy Null Handling Behavior.

Null Values in Expressions

The add-in includes null to refer to null values in fields and parameters referenced in expressions.

Literal

Use null in expressions to represent a null value, like this: 

{ this.Value != null }

Note:

Use lower case only without quotation marks.

Operations

Null values can be used as operands for the == and != operators, like this: { this.value == null } and { Workbook.Parameters['param1'] != null }.

Null handling supports the following additional operators:

Operator Use Description
?. Conditional object member access Performs member access only when the operand is non-null. Otherwise, it returns null.
?? Null-coalescing Returns the value of its left-hand operand if it isn't null. Otherwise, it evaluates the right-hand operand and returns its result.

Expression Examples

Expression Description
{ Workbook.Parameters['maxSalaryParam']?.Value } If the workbook parameter maxSalaryParam is missing, the expression returns null. Otherwise, it returns the value of maxSalaryParam.
{ this.Value ?? '' } If this field's value is null, the expression returns an empty string. Otherwise, it returns the field value.

Disable the Legacy Null Handling Behavior

If you have an old workbook that uses the add-in's "legacy" null handling behavior, you disable the behavior and use the new behavior instead.

There are two null handling behaviors the add-in supports when evaluating expressions:

  • Legacy (version 4.0 and earlier): The add-in automatically replaced null values with a fixed default value appropriate for the data type—a zero (0) for an integer field and a space (" ") for a string. By default, older workbooks use this legacy behavior when opened by later versions of the add-in.
  • New (version 4.1 and later): The add-in does not make this substitution by default. You must write expressions that handle null values that may arise.

    For new workbooks, the new behavior is enabled by default.

These properties are affected by this option:

  • REST header values
  • Field validation rules
  • Default values for Search Editor search condition
  • Default values for row finder variables
  • Download parameter values

WARNING:

Before you change this setting, review all your expressions and update them to handle null values as required. If you do not update your expressions, you may encounter failures.

To disable the legacy null handling behavior:

  1. Choose Workbook Info from the Advanced menu to open the Workbook Info window.
  2. Click Settings, then deselect Legacy null handling for expressions.
  3. Test your workbook thoroughly.

Workbook Parameters in Expressions

Oracle Visual Builder Add-in for Excel supports references to workbook parameter values in expressions to control search behavior during a download.

There are three cases where expressions can reference workbook parameter values:

Missing Workbook Parameters

The expression you write may refer to a workbook parameter that for whatever reason is not present in the workbook. You can write your expression to guard against such cases.

Let's suppose you have a search condition on a Salary field that filters for items less than a maximum value specified by a workbook parameter named maxSalaryParam.

Your search field is Salary, the operator is "less than" (<) and the expression is: 

{ Workbook.Parameters['maxSalaryParam']?.Value }

If maxSalaryParam is not present in the workbook, the expression evaluates to null, and the entire search condition will be omitted from the download request. See Null Values in Expressions for more information about the ?. operator.

Empty Values in Workbook Parameters

The expression you write may refer to a workbook parameter that is present in the workbook but has no value specified.

Building off of the previous example, let's say that you want to use 5000 as the default value for maxSalaryParam if it is missing, or if it is present but has no value specified. In this case, the expression is: 

{ Workbook.Parameters['maxSalaryParam']?.Value ?? '5000' }

See Null Values in Expressions for more information about the ?? operator.

Examples of Expressions

Here are some sample expressions and their uses.

Property Value Use Sample Value Final Property Value
DepartmentId={ this.BusinessObject.Fields['DepartmentId'].Value } This string sets the value of DepartmentId in the query to the current row item's department Id value. Department Id is 101 DepartmentId=101
DepartmentId={ this.BusinessObject.Fields['DepartmentId'].Value } { SelectWindow.SearchTerm == '' ? '' : 'AND FirstName LIKE \'' + SelectWindow.SearchTerm + '*\'' }

This string includes two expressions. The second expression uses the ternary operator. It returns results based on whether there is a search term in the search box.

If there is no search term, the parameter returns values matching the current row item's department Id value.

If there is a search term, the parameter returns results that match the department Id and the search term.

The quotes are all single quotation marks. Note also the enclosed empty strings and escaped single quotes.

 Department id is 101 and there is no search term DepartmentId=101
Department id is 101 and the search term is Steve DepartmentId=101 AND FirstName LIKE 'Steve*'
DepartmentId={ Workbook.Parameters['Dept'].Value } AND Salary >= { Workbook.Parameters['MinSal'].Value } This string sets the value of DepartmentId in the query to the value of the Dept workbook parameter and the value of Salary to the MinSal workbook parameter. The workbook parameter Dept is 80 and MinSal is 7000. DepartmentId=80 AND Salary >=7000
{ (this.Value ?? Today()) > d'2024-01-01' }

This expression represents a Boolean value that is dependent on this.Value.

It returns true if this.Value is later than date 2024-01-01 (ISO 8601 yyyy-MM-dd).

If this.Value is null, it uses Today() for comparison.

this.Value is null true