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.
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; andValue
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']
)
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}
.
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.
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 |
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'
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 isDate (no time)
, the result ofToday()
, 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.
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 converts these values into 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 |
Reserved Words Used in Expressions
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 |
Value |
Value of a parameter or a field |
SelectWindow |
Search-and-select window in a list of values |
Finder |
Represents the row finder to which the currently selected variable belongs |
RowVariables |
Represents a row variable configured for a business object |
Workbook |
Represents the integrated workbook |
Parameters |
Represents a workbook parameter stored in the workbook |
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
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):
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:
These symbols are not supported:
|
Date |
A date type literal value must start with a prefix letter
|
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 | Note |
---|---|
[ ] . |
Collection access, object member access |
( ) |
Grouping to change precedence |
- ! |
Unary minus, negation |
* / |
Math (multiplicative) |
+ - |
Math (additive), also + for string concatenation |
< > <= >= |
Relational |
== != |
Equality |
&& |
Logical AND |
|| |
Logical OR |
? : |
Ternary conditional |
Examples of Expressions
Here are some sample 'q'-type filter query parameters for use in list of values configurations.
Parameter Value | Use | Sample Value | Final Parameter 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.Parent.Parent.Fields['DepartmentId'].Value } |
This string sets the value of DepartmentId in the query to the department Id value in the current row item's "grandparent" layout.
|
Department Id is 101 |
DepartmentId=101 |
FirstName LIKE '{ SelectWindow.SearchTerm }*' |
This string matches employees whose first name begins with the user-provided search term entered in the Search-and-Select window. | Search term is Steve | FirstName LIKE 'Steve*' |
CountryId={ this.Finder.Variables['CountryId'].Value } |
This string sets the value of CountryId in the query to the value of the current row finder's CountryId variable.
|
Country Id is USA |
CountryId=USA |
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 |