Use Lists of Values in an Excel Workbook

About Lists of Values

When you configure a list of values in your workbook, Oracle Visual Builder Add-in for Excel displays a drop down list of values when a business user selects the field.

If a filter with a search term parameter is configured, a business user can also search for values in the list by typing a search term in the search-and-select window and clicking the Search icon.

In this image, the search-and-select window on the left is populated with a list of values drawn from a Jobs business object. The search-and-select window on the right shows the result of entering S in the search box and clicking the Search icon.

When a business user clicks the Search icon, the list of values displays only job titles that begin with S such as "Sales Manager", "Sales Representative", and so on. Business users can also enter the full value in the search box, assuming it's a valid value such as "Sales Manager".

Lists of values can also be configured to show more than one display field in the search-and-select window. The add-in shows all the display fields, in separate columns, in the search-and-select window but concatenates those values configured for display when shown in the Excel cell.

In this image, the search-and-select window on the left shows separate columns for first and last names. The search-and-select window on the right shows the result of entering Jo in the search box and clicking the Search icon.

The search-and-select window showing two columns

The list of values displays all first or last names that match the string. In this example, the add-in matches the search string "Jo" to the first name "Joshua" but also the last name "Johnson".

Each item in the list of values has a display value (which appears in the Excel workbook) and an identity value that is retrieved and posted to the business object field. For example, the Jobs business object used for the list of values might contain these display and identity values:

Display value (jobTitle)	Identity value (jobId)
President	PRES
Finance Manager	FIN_MGR
Sales Manager	SAL_MGR

On download, the identity values are replaced by the display values; On upload, the display values are replaced by the identity values.

Configure a List of Values with a Business Object

Configure a list of values that uses values from another business object. List of values are supported for business object fields, custom action payload fields, and row finder variables.

When you create a list of values, you can associate the selected field with the values from another business object. For example, you may have two business objects: Employees and Jobs. If the Employees layout includes a JobId column, you may want to add a list of values that references the jobId field from the Jobs business object.

When a business user selects a cell from the Job Title column in the Employees layout, a search-and-select window shows a list of values drawn from the Jobs business object for the user to choose from.

You can configure a list of values to show more than one display field in the search-and-select window. The add-in shows all the display fields, in separate columns, in the search-and-select window but only those you configure are shown in the Excel cell. If you choose to show more than one field in the cell, the values are concatenated.

For business object sources, you can configure a filter to restrict the results to a given subset of values. You can also configure it to let business users filter the list based on a search term they type in a search box. See Configure a Filter for a List of Values.

If the catalog is missing the desired business object, you can add a new business object to an existing catalog (see Add a Business Object to an Existing Catalog).

If you want to reference a business object with a different base path, you can create or import a business object into the current catalog and then override the base path (see Override a Business Object's Base Path).

Note:

This procedure takes you through the steps to create a list of values for a business object field used in a form, table, or search. The steps are the same for custom action payload fields and row finder variables except for the navigation. To open the List of Values page for a custom action payload field or row finder variable, open the Business Object Editor and go to the Custom Action or Row Finder page. From there, select the appropriate field or variable.

To create a list of values based on a local data source instead, see Configure a List of Values with a Local Data Source.

To create a list of values:

From the Layout Designer, click the Edit () icon next to the Business Object field.
From the Business Object Editor, click the Fields tab, then select the business object's field.
Click the Edit () icon to open the Business Object Field Editor, then click the List of Values tab.

Description of the illustration excel-lov-config.png
Select the Enabled check box on the List of Values page.
Click the Edit () icon next to the Data Source field, then pick an appropriate data source. If using a business object , make sure it is from the same catalog used to create the layout.
This data source provides the display values for the corresponding identity values.
Click the Edit () icon next to the Identity Field field, then choose the appropriate identity field from the data source.
This is the field used to look up the display values for the identity values in the current field.
Click the Add Field () icon to open the Available Business Object Fields Editor, then choose the desired display field.
These fields come from the data source and are shown instead of the identity values where this field is used in a layout.

You can choose multiple display fields for one list of values. Repeat this step to add additional display values.
For each display field, select either Picker and cell or Picker only from the Display Type list.
If you configured only one display field, use Picker and cell to display the value in both the Excel cell and the search-and-select window. For additional display fields, use Picker only if you don't want to display the value in the Excel cell.

Note:
When configuring the display values, make sure the information in the cell is unique and meaningful for your business users. Take for example a Contact field in your layout. To ensure your business users have enough information to determine the right contact for a purchase order, you may want to include the contact name, company, and email as display fields in the Excel cell. In this case, ensure that the display type for these display fields are set to Picker and cell.
To configure a filter, click the Add Query Parameter () icon next to Filter Query Parameters, then set a name and parameter value.
If the parameter requires a search term, click Omit if SearchTerm is empty.

Repeat this step to create additional parameters.
Click Done.

Once you define a list of values, the choice list will appear wherever that business object or payload field appears. For the row finder variable, the choice list will appear wherever that row finder variable appears during download.

The add-in caches the data of list of values in the workbook. After you modify the configuration of any list of values, click Clear List of Values Cache from the Advanced menu.

Configure a Filter for a List of Values

Configure a filter for your list of values to determine which items from the business object used as the data source are included in the list. A filter is a set of one or more URL query parameters that are appended to a REST request to the referenced business object.

Filters are not available for lists of values that use a local data source.

The filter query parameters are added to all requests to the referenced business object. These include the request to fetch the initial set of values as well as the one sent when the business user clicks the Search icon after entering a search term.

You should always configure a filter with a search term parameter since the search option is always available to the business user.

There are a few basic filter scenarios you may want to consider when configuring a list of values:

Search term parameter only: You don't need to use a filter parameter for the initial set of values if you want your business users to access all values from the referenced business object. In this case, just configure a search term parameter and let your business user filter the list based on a search term. See Configure a Filter for a Search Term Only.
Filter and search term parameters: To limit the choices available from the referenced business object, consider using a filter parameter such as a finder. Also, add a search term parameter to let your business user search from within the list. See Configure a Filter to Limit Available Choices.
Dynamic and search term parameters: A dynamic filter parameter limits the number of values in a list of values based on a field value in the current layout. For example, you may filter a Job Title list of values based on the selected employee's department so that only job titles for this department are displayed. See Configure a Filter with a Dynamic Parameter.
Cascading lists of values: A cascading list of values uses dynamic filters where the value selected in one list determines the range of values that users can select from subsequent lists. See Configure a Cascading List of Values in a Layout.

See About Expressions for more information about expressions in lists of values.

Consult the API documentation for your service to determine the appropriate search syntax to use in the Value column. For example, if you use an Oracle business object REST API service, consult Understanding Framework Support for Query Syntax in Accessing Business Objects Using REST APIs.

Configure a Filter for a Search Term Only

Configure a filter with a search term parameter to allow your business users to search for values using a search term.

For example, let's say you have a list of values that displays job titles for your company. To allow the business user to filter this list, you can use a "q" parameter value to return job titles that start with the business user's search term:

Note:

Select Omit if SearchTerm is empty to ensure this parameter is only applied when there is a value in the search box.

In this example, a business user can type "ma" in the search box and click the Search icon to display job titles such as "Manager" and "Marketing Specialist".

You should always configure a search term parameter for your filter since the search option is always available to your business users.

If you have more than one display field in your list of values, you can use the "OR" operand to search on each of the fields. For example, to match text to either the employee's first or last name, create a parameter like this:

A search term parameter that matches a search term to either of two fields

When a user types in the search box and clicks the Search icon, the add-in displays all first or last names that match the string. In this example, the add-in would match the search string "Jo" to the first name "Joshua" but also the last name "Johnson".

Note:

The syntax for the filter query varies based on the REST service type and expected query syntax for a business object.

Configure a Filter to Limit Available Choices

Configure a filter with multiple query parameters to limit the choices that are fetched from the referenced business object.

For example, you may have a referenced business object that stores values for a number of different lists of values and you need a way to retrieve only the values for the current field.

Let's consider a business object, StandardLookupsLOV, that stores immigration details for a company's employees such as immigration status (I_Status) and type (I_Type).

You may want to use a finder, for example, to return just the immigration status (I_Status) values, such as "Not Applicable", "Pending", "Accepted" and so on, for your list of values.

You'll also want to create a search term parameter to allow your business users to find the value they are looking for.

To do this, configure your filter as shown here:
Filter query parameters showing a finder and a search term filter

In this case, the initial set of values is determined by the finder configuration. If the business user types a search term and clicks the Search icon, the list is further limited to status values whose DisplayValue starts with the search term.

Configure a Filter with a Dynamic Parameter

You can configure a list of values that is dynamically filtered based on another value in a layout or search field. For example, you may want your business users to see only those job titles in a list of values that are relevant for the department that they have selected.

When used in a layout as shown on the left, the list of values (in this example, "Job Title") is based on the value of another field ("Department"). When used in a search as shown on the right, the Job Title list is filtered based on the value in the first search field ("Department").

Description of dynamic-filter-example.png follows

Description of the illustration dynamic-filter-example.png

To configure a dynamic filter for the Job Title list, configure a parameter that filters based on the department Id like this:

This parameter includes an expression of the form, { this.BusinessObject.Fields['FieldId'].Value }, where FieldId is the ID property of another field in the current business object or search—in this case, DepartmentId.

Note:

Since this expression refers to another field in the currently selected row or search, make sure this field ("DepartmentId", in this case) has been added to your layout or added before the field with the dynamic filter when it is used in a search. See Use Search to Limit Downloaded Data for information about how to use these lists of values in a search.

The Oracle Visual Builder Add-in for Excel replaces this expression with the corresponding field value (specifically, the identity value) when sending the request for the referenced business object's list of values. When business users select the Job Title field for an employee in the marketing department, the list includes only job titles associated with Marketing.

Likewise when using a dynamic list of values in a search. When business users select the Job Title field, the list includes only job titles associated with the department they first chose from the Department field.

If the corresponding field value is missing or invalid, the expression evaluation fails and an error is reported.

Remember to include a search term parameter, such as jobTitle LIKE '{ SelectWindow.SearchTerm }*', to allow your business users to filter the list to jobs based on a search term they provide.

Referencing Ancestor Business Objects in a Dynamic Filter

For a list of values that you plan to use in a layout, you can also refer to a parent or higher ("ancestor") business object in your expression using one or more Parent terms. For example, to create a list of values that refers to a field in the parent business object, you would use ByDeptFinder;DepartmentId={ this.BusinessObject.Parent.Fields['DepartmentId'].Value }.

To refer to a grandparent business object, use two Parent terms instead: this.BusinessObject.Parent.Parent.Fields['DepartmentId'].Value.

Note:

Do not refer to an ancestor business object if you plan to use your list of values in a search. Such a configuration will result in an error at runtime.

Tip:

When using a dynamic filter in a layout, it is recommended that you add an ancestor column for the field ("DepartmentId", in this case) to the current layout. The ancestor field must be positioned before the column ("Job Title") that references it. This makes it easier for the add-in to find the correct value during runtime and improves performance.

If there is no matching ancestor column in the current layout, the add-in uses other ancestor columns to map the current row to its parent row. It repeats this process until it finds the ancestor row and reads the field value. This can take some time if there are a large number of parent rows. See Add Ancestor Columns to a Dependent Layout.

Configure a Cascading List of Values in a Layout

Configure a cascading list of values using dynamic query parameters.

In a cascading list of values, the value selected in one list determines the range of values that users can select from subsequent lists. For example, suppose a table displays columns with lists of values for Countries, States, and Cities. The value that a user chooses in the Countries list determines the values that appear in the list for States, and so on.

Here's how you configure the filter query parameters in the Business Object Field Editor to create a list of values for Countries, States, and Cities. To filter the list of values by the current country:
This image shows the filter query parameters in the Business Object Field Editor's List of Values tab. The Name parameter is set to

To filter the list of values by Country and State:
This image shows the filter query parameters in the Business Object Field Editor's List of Values tab. The Name parameter is set to

In a scenario like this with three fields ("fieldA", "fieldB", and "fieldC"), remember that fieldC depends on both fieldB and fieldA. So when you create the filter for fieldC's list of values, also include fieldA's value expression. In general, all of fieldB's dependencies must also be fieldC's dependencies.

Note the following name-value parameters under Filter Query Parameters:

Table 8-1 Example Name-Value Parameters in Filter

Field	Name	Value
StateId	finder	`ByCountryFinder;CurrentCountry={ this.BusinessObject.Fields['CountryId'].Value }`
CityId	finder	`ByCountryAndStateFinder;CurrentCountry={ this.BusinessObject.Fields['CountryId'].Value },CurrentState={ this.BusinessObject.Fields['StateId'].Value }`

If the service has complete metadata for a cascading list of values, the filter query parameters of the corresponding field's list of values is automatically configured.

If the service does not have complete metadata for a list of values but you know it supports search syntax that makes for a cascading list of values, you can manually configure a list of values by adding filter query parameters. See Configure a Filter for a List of Values.

Notes on Filters

Refer to these notes when configuring filters for lists of values:

Oracle Visual Builder Add-in for Excel does not validate your filter configuration. If you provide invalid information, the add-in will make invalid requests that return errors.
The query parameters are applied in the order that they are configured.
The add-in applies URL encoding to the final resolved value of each filter query parameter before sending the request.
Expressions like { this.BusinessObject.Fields['FieldId'].Value } can't be used in the filter for:
- Row finder variable's list of values
- Custom action payload fields if the field referred to is not added to the layout
If your list of values includes more than one display field, you can configure a search query that returns matches from any of the display fields. See Configure a Filter for a Search Term Only for an example of a search term parameter that includes the OR operand.
If you configure multiple values with the same parameter name, the last value that is not "omitted" (Omit if SearchTerm is empty is enabled) is used by default.
For example, in this filter configuration, the first query parameter is used if there is no search term; the second parameter is used if there is a search term.
Filters are not available for list of values based on local data sources

Create a Local Data Source for a List of Values

You can create local data source that provides the values for the list of values (LOV). This data source stores these values in your integrated workbook as a set of name-value pairs.

Local data sources have a couple of key benefits. They convert field codes to user-friendly display values automatically to improve the user experience. They also provide the business user with a drop-down list that restricts the choices to a fixed set of appropriate values.

Let's consider an example. You may have a business object, Computer, with a string field, personal, that captures whether the device is a business or personal one. In this case, the field expects only "Y" and "N" values.

You may want to create an LOV on the personal field that displays "Yes" and "No" values in the list. To do this, create a new local data source in the catalog that includes these values, then configure an LOV for the personal field that uses this data source.

To create a local data source for a list of values:

From the Oracle Visual Builder tab, click Manage Catalogs.
In the Manage Business Object Catalogs window, select the catalog where you want to define the data source and click the Edit Business Object Catalog icon.
From the Business Object Catalog Editor, click Local Data Sources.
From the Local Data Sources tab, create a new data source:
1. Click the Add icon () to create a new data source.
2. Select the new data source from the list, then click the Edit icon () to open the new data source in the Local Data Source Editor.
3. From the General tab, enter a title, description, and key field ID for the data source.
  Description of the illustration local-data-source-general.png
  - Title: Identifies the local data source when creating a list of values. The title only appears in designer windows, such as the List of Values tab in the Business Object Field Editor.
  - Description (optional): Helps workbook developers understand the purpose and use of the data source. The description only appears in the designer.
  - Key Field ID: The unique key for identifying the data in the local data source. In this example, the key field ID is code. You'll create this key field from the Field tab, as described in the next steps.
    
    When exporting strings for translation, rows are identified by their key field values. Key field values themselves are not translated.
From the Fields tab, create fields for the data source.
In this example, you will create two fields of string data-type: code and display. code is the key field ID in our example. display stores the values that appear in the list.

Note:
In some cases, you may want to show just the identity values ("Y" and "N" instead of "Yes" and "No") to your business users. In this situation, create one field and use it later as both the identity field and display field in your LOV.
1. Click the Add icon ().
2. Select the new field from the list, then click the Edit icon () to open the field in the Local Data Source Field Editor.
3. Provide details for the field, then click Done.
  
  Description of the illustration local-data-source-field-editor.png
  
  The value you type into the ID field should match the value you entered into the Key Field ID field previously. In this example, type in code for the ID field and choose "String" from the "Data Type" list.
  
  Also, provide a title and description for the field. These values help workbook developers identify the field when displayed in designer windows.
  
  Note:
  The ID, Title, and Data Type fields are required. The Description field is optional.
4. Repeat these steps for the next field. In this example, create the display field for your data source.
Note:
Make sure all the fields are configured properly before opening the Data tab, as described in the next step. If you make any changes in the Fields tab, any data entered in the Data tab is cleared.
From the Data tab, add values to your data source in the newly-created fields.
In this example, you'll enter the expected value (for example, "Y") in the Code (key ID) field, and the user-friendly value ("Yes") in the Display field, as shown in this image.

Description of the illustration local-data-source-data.png

To add a row, select the last row and press Enter. To delete a row, select it and press Delete. To select more than one row, use the Shift key.
Click Done.

Now that you have created a local data source, you are ready to use it as the data source for an LOV. See Configure a List of Values with a Local Data Source.

Configure a List of Values with a Local Data Source

Configure a list of values that references a local data source. List of values are supported for business object fields, custom action payload fields, and row finder variables.

A local data source is a set of name-value pairs stored in the workbook that can be used in a list of values. These local data sources can be created by Oracle Visual Builder Add-in for Excel to store enums, if the service includes these. You can also create your own local data sources if required. See Create a Local Data Source for a List of Values.

Let's consider an example. Suppose you have a layout for a computer business object that includes a "personal" column for indicating whether the device is the employee's personal property. You may want to create an LOV on this field that displays "Yes" and "No" values in the list. To do this, create a new local data source in the catalog that includes these values, then configure an LOV for the personal field that uses this data source.

When a business user selects a cell from the personal column in the Computer layout, a popup window shows a list of values drawn from your local data source for the user to choose from.

You can configure a list of values to show more than one display field in the popup window. The add-in shows all the display fields, in separate columns, in the popup window but only those you configure are shown in the Excel cell. If you choose to show more than one field in the cell, the values are concatenated.

Note:

Lists of values based on local data sources do not support filters or search in the drop-down list.

To create a list of values with a local data source:

From the Layout Designer, click the Edit () icon next to the Business Object field.
From the Business Object Editor, click the Fields tab, then select the business object's field.
Click the Edit () icon to open the Business Object Field Editor, then click the List of Values tab.
Select the Enabled check box on the List of Values page.
Click the Edit () icon next to the Data Source field, then pick an appropriate local data source.

Description of the illustration lov-data-source.png

This data source provides the display values for the corresponding identity values.
Click the Edit () icon next to the Identity Field field, then choose the appropriate identity field from the local data source.
This is the field used to look up the display values for the identity values in the current field.
Click the Add Field () icon to open the Available Business Object Fields Editor, then choose the desired display field.

These fields come from the data source and are shown instead of the identity values where this field is used in a layout.

You can choose multiple display fields for one list of values. Repeat this step to add additional display values.
For each display field, select either Picker and cell or Picker only from the Display Type list.

If you configured only one display field, use Picker and cell to display the value in both the Excel cell and the popup window. For additional display fields, use Picker only if you don't want to display the value in the Excel cell.

Note:
When configuring the display values, make sure the information in the cell is unique and meaningful for your business users. Take for example a Contact field in your layout. To ensure your business users have enough information to determine the right contact for a purchase order, you may want to include the contact name, company, and email as display fields in the Excel cell. In this case, ensure that the display type for these display fields are set to Picker and cell.
Click Done.

During runtime, the business user sees the user-friendly values from the display field of the data source in the LOV, as shown here:

Description of local-data-source-lov.png follows

Description of the illustration local-data-source-lov.png

The choice list will appear wherever that business object or payload field appears. For the row finder variable, the choice list will appear wherever that row finder variable appears during download.

The add-in caches the data of list of values in the workbook. After you modify the configuration of any list of values, click Clear List of Values Cache from the Advanced menu.

List of Values for Descriptive Flexfields

Oracle Visual Builder Add-in for Excel supports lists of values for context-sensitive descriptive flexfields (DFF) with "parameter" and "segment" client binds.

Parameter type client binds must be configured from the Polymorphic Information tab of the Business Object Field editor before they become available. See Configure the Bind Parameters for a Descriptive Flexfield's List of Values.

Segment type client binds are supported automatically as long as the service's OpenAPI 3 document provides all the proper metadata. If the metadata is present, the add-in configures the list of values at runtime.

Limitations

Client binds are not supported on "global" DFF fields.
Only binds of data type string and number are supported. Binds of date type are not supported.

Configure the Bind Parameters for a Descriptive Flexfield's List of Values

Configure parameter-type client binds used by segments in a descriptive flexfield (DFF) using the Business Object Field Editor.

Oracle Visual Builder Add-in for Excel currently only supports configuring the polymorphic bind parameters. Other details of the list of values for DFFs must come from the metadata and are not configurable.

To configure polymorphic bind parameters (also known as "flex bind variables"), open the Business Object Field Editor for a DFF from the Layout Designer, and then configure the bind parameters in the Polymorphic Bind Parameters area of the Polymorphic Information page.

For example, suppose you want to configure the bind parameter, BusinessUnit, for a list of values on a polymorphic field set (Context Value column in the Layout Designer). To configure the polymorphic bind parameters in this case:

From the Layout Designer, click the Columns tab and then select the Context Value column.
Click the Edit icon () to open the Business Object Field Editor.
From the Polymorphic Information tab, configure the bind parameters, as shown in the following image:

The value of a bind parameter can be a literal value like 100001, Requisition001, or a string that contains one or more expressions surrounded by curly braces { }
An expression can refer to the value of a field in any business object in the current business object hierarchy. The field referred to must be exposed in the in an existing layout.
In this example, the value is a single expression, where
- this is the polymorphic field set
- this.BusinessObject is the business object that owns the polymorphic field set (the DFF business object)
- Parent gets the parent business object in the hierarchy (bottom-up: RequisitionDistributions/projectDFF, RequisitionDistributions, RequisitionLines, PurchaseRequisitions)
- this.BusinessObject.Parent.Parent.Parent is the PurchaseRequisitions business object that owns the field. RequisitioningBUId
- Fields['<field ID>'].Value gets the current field value of the field with the given field ID. Note the single quotes (required) around the ID.
See About Expressions for more information.

Limitations

This feature only supports lists of values in context-sensitive DFF fields and not "global" DFF fields.
Only binds of data type "string" and "number" are supported.
The add-in uses client binds if and only if the OpenAPI 3 document provides proper metadata.
Expression syntax is limited to what's shown in the example (key parts: this, BusinessObject, Parent, Fields[<field ID>].Value).

Clear Cache for a List of Values

A list of values' choices are always cached in the workbook.

The cache for a list of values based on a business object can contain up to 300 items, plus all used items. It is populated during the first download or the first time the search-and-select window is used. The search-and-select window shows the cached list of values, if available. An upload also uses cached data.

As a workbook developer, remember to click Clear List of Values Cache from the Advanced menu:

Whenever you change any list of values configuration, and always when you publish the workbook
When the service host is changed.
When cached data is not what the user expects.

Notes and Limitations for Lists of Values

Review the notes and limitations listed here when planning to use lists of values (LOV):

For Oracle business object REST API services, "row context" list of values is not supported.
Only one identity field is supported in a list of values.
Display values support strings; identity values support integers or strings. Decimal numbers, dates, and date-time values are not supported.
Excel drop-down lists using data validation may not be compatible with the add-in.
For Oracle business object REST API services, if the catalog includes metadata for an LOV, the add-in reads the metadata and configures the LOV automatically. Supported ControlTypeHints: "combo_lov", "choice", and "input_text_lov".
For any read-only field (column or form), the add-in still swaps identity values for display values. However, the search-and-select window does not appear when the cell is selected.
When using an expression that refers to a field in an ancestor layout, ensure:
- The referenced field is present in the ancestor layout.
- The ancestor column in the current layout is configured properly so that the add-in can map a row to its ancestor row.
A field's LOV can be dependent on an ancestor field as long as that ancestor field does not have an LOV that is dependent on another field.
When creating a cascading set of LOVs, you can use a "local" LOV (one based on a local data source) as the initial list, but not as one of the subsequent lists. A "remote" LOV (one based on a business object as the data source), however, can depend on a value in a local LOV.
In a cascading list of values, the value selected in one list determines the range of values that users can select from subsequent lists.