9 Use Lists of Values in an Excel Workbook

Oracle Visual Builder Add-in for Excel enables you to associate a list of values with a business object field, a custom action payload field, or a row finder variable.

A list of values is the set of valid values for a business object field and represents a relationship between the field in one business object and another referenced business object.

When a user selects a cell with a properly configured list of values, a search-and-select window shows a list of values for the user to choose from. Alternatively, the user can enter some text and click the Search icon to filter and show values based on the user input, for example, to show values that start with the user-specified text. See Configure Search for a List of Values.

This image illustrates both these scenarios. For the latter scenario, the user entered S in the search box to filter values from the list that begin with S (Sales Manager, Sales Representative, and so on). Users can also enter the full value in the search box, assuming it's a valid value such as Sales Manager.

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, an Employee business object's Job attribute might contain these display and identity values:

Display value	Identity value
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 for a Business Object Field

You can use the Business Object Field Editor to add or modify the list of values configuration for a field.

To access this editor, click the Edit icon next to the Business Object field in the Layout Designer. Once you open the Business Object Editor, click the Fields tab and select the business object's field. Then, click the Edit icon in the Business Object Editor to open the Business Object Field Editor, as shown in the following image where the Business Object Field Editor is open for the Employee business object’s Job Title field.

Description of the illustration excel-addin_lov-editors.png

The List of Values tab in the Business Object Field Editor shows the following properties:

• Enabled: Select to enable list of value functionality for this field; deselect to disable
• Referenced Business Object: This business object provides the display values for the corresponding identity values
• Identity Field: The field in the referenced business object used to look up the display values for the identity values in the current field
• Display Fields: These fields come from the referenced business object and are shown instead of the identity values where this field is used in a layout

To configure a list of values for a business object field that does not have one yet:

1. Select the Enabled check box on the List of Values page.
2. Select the appropriate business object using the Edit icon next to the Referenced Business Object field. Pick a business object from the same catalog used to create the layout.
3. Choose the appropriate identity field from the referenced business object.
4. Choose the desired display field from the referenced business object.

   You can choose multiple display fields for one list of values.

5. Configure Filter and Simple Search as desired (see subsequent sections for details).
6. Click Done.

Tip:

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

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

Configure a List of Values for a Custom Action Field

Define a list of values for a custom action's payload field, just as you'd configure a list of values for a business object field.

Use the Business Object Editor to access a business object's custom actions and associated payload fields. You can then edit a payload field to define a list of values:

Description of the illustration custom_action_lov.png

The List of Values tab shows the following properties:

• Enabled: Select to enable list of value functionality for this field; deselect to disable
• Referenced Business Object: This business object provides the display values for the corresponding identity values
• Identity Field: The field in the referenced business object used to look up the display values for the identity values in the current field
• Display Fields: These fields come from the referenced business object and are shown instead of the identity values where this field is used in a layout

To configure a list of values for a payload field that does not have one yet:

1. Select the Enabled check box on the List of Values page.
2. Select the appropriate business object using the Edit icon next to the Referenced Business Object field. Pick a business object from the same catalog used to create the layout.
3. Choose the appropriate identity field from the referenced business object.
4. Choose the desired display field from the referenced business object.

   You can choose multiple display fields for one list of values.

5. Configure Filter and Simple Search as desired (see subsequent sections for details).
6. Click Done.

Tip:

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

Once you define a list of values, the choice list will appear wherever that payload field appears, as shown in the preceding image where the referenced business object's list of values appear for the Reason (Reject) column.

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

Configure a List of Values for a Row Finder Variable

Define a list of values for a row finder variable, just as you'd configure a list of values for a business object field.

Use the Business Object Editor to access a business object's row finder variables. You can then edit a variable to define a list of values:

Description of the illustration row-finder-lov-editor.png

The List of Values tab shows the following properties:

• Enabled: Select to enable list of value functionality for this field; deselect to disable
• Referenced Business Object: This business object provides the display values for the corresponding identity values
• Identity Field: The field in the referenced business object used to look up the display values for the identity values in the current field
• Display Fields: These fields come from the referenced business object and are shown instead of the identity values where this field is used in a layout

To configure a list of values for a row finder variable that does not have one yet:

1. Select the Enabled check box on the List of Values page.
2. Select the appropriate business object using the Edit icon next to the Referenced Business Object field. Pick a business object from the same catalog used to create the layout.
3. Choose the appropriate identity field from the referenced business object.
4. Choose the desired display field from the referenced business object.

   You can choose multiple display fields for one list of values.

5. Configure Filter and Simple Search as desired (see subsequent sections for details).
6. Click Done.

Tip:

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

Once you define a list of values, 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 Redraw Layout. Then, click Clear List of Values Cache from the Advanced menu in the Misc ribbon tab.

Configure Filters for a List of Values

You can configure a filter for a list of values so that users see only certain values in the list when they select the field in a layout. You can define these filters using name-value query parameters.

The query parameter's value is a string that could include the expression { $options.fieldValues['FieldId'] }, where FieldId is the ID property of another field in the current business object. The add-in 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.

Note:

Expressions like { $options.fieldValues['FieldId'] } cannot be used in filters for custom action fields or row finder variables.

For example, when you work with a Table layout for departments and want users to see only the current department's employees, you can specify a configuration to filter the Employees list with a Row Finder based on the current row's DepartmentId, if the REST service supports finders.

Note:

The filter you define determines which items are fetched and displayed in the search-and-select window. This feature is different from the simple search query. When you configure a simple search for a list of values, the user can enter a search term in the search-and-select window and return values for the list that match the term. See Configure Search for a List of Values for more information on the simple search feature.

1. In the Business Object Field Editor's List of Values tab, click the Add Query Parameter icon in the Filter section.
2. In the Name list, select finder as the query parameter.
3. In the Value text box, enter an expression similar to:

   EmpsByDeptIdFinder;DeptId={ $options.fieldValues['DepartmentId'] }

   where EmpsByDeptIdFinder is the finder's name, DeptId is the finder's bind variable, and { $options.fieldValues['DepartmentId'] } is the expression for the current row's DepartmentId field value.

If the list of values' default order is unclear, you can add the orderBy parameter if the service supports it.

Configure Search for a List of Values

You can allow your users to enter a string to search for a value in a list of values by defining search parameters.

The query parameter value is a string expression that may include the expression { $options.simpleSearch }. The add-in replaces this expression with the input that the user enters in the search box within the search-and-search window. The add-in does not validate the values, so if the user enters invalid information, the add-in will make invalid requests that return errors.

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

Simple Search parameters are not applied when the add-in retrieves the initial set of values to show in the search-and-select window next to the cell in the worksheet. They are applied only when the user clicks the Search icon (after the initial set of values are already retrieved).

If your list of values includes more than one display field, you can configure a simple search query that returns matches from any of the display fields. For example, to match text to either an employee's first or last name, create a query with the "OR" operand like this: FIRST_NAME LIKE '{ $options.simpleSearch }*' OR LAST_NAME LIKE '{ $options.simpleSearch }*'. See Configure a List of Values with Multiple Display Fields.

Note:

When both filter and simple search parameters are configured, the filter parameters are applied first and then the simple search parameters. The filter determines which items are fetched and displayed in the search-and-select window. Simple search only searches within the filtered list. If multiple values are specified for one parameter, the last value takes effect. See Configure Filters for a List of Values for more information on the filter feature.

1. In the Business Object Field Editor's List of Values tab, click the Add Query Parameter icon in the Simple Search section.
2. Add values for the Name and Value properties based on what the service supports. Many REST services support a specific query parameter such as q for search, as in the following example:

   GET…/employees?q=LAST_NAME LIKE 'Jones*'

   Select the appropriate query parameter in the Name list; in our example, this is q. In the Value text box, provide a search expression that is appropriate for the service and include the { $options.simpleSearch } text placeholder where appropriate. In our example, the Value is:

   LAST_NAME LIKE '{ $options.simpleSearch }*'

   
   
   
   
   

Now, if the user enters Jo in the search box and clicks the Search icon, the resulting request to the referenced business object will be:

GET.../employees?q=LAST_NAME LIKE 'Jo*'.

Note:

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. Remember that the add-in uses REST framework version 6 when making requests to a business object REST API service.

Configure a List of Values with Multiple Display Fields

You can configure a list of values that shows more than one display field in the search-and-select window when the user selects a cell.

When you do so, Oracle Visual Builder Add-in for Excel shows the display fields in separate columns but concatenates these values when shown in the Excel cell.

For example, you may want to have a list of values showing managers by first and last name. Configure the list of values as you would normally but include both name fields (firstName and lastName) in the Display Fields table.

When a user selects a cell in the Manager column, the add-in displays a search-and-select window that the user can choose from, showing both first and last name in separate columns as shown here.

You can also configure a simple search query that returns matches from either of the display fields. For example, to match text to either the employee's first or last name, create a query with the "OR" operand like this:

Note:

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

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 matches the search string "Jo" to the first name "John" but also the last name "Jones".

Note:

For a list of values with multiple display fields, if you enter a value directly in the cell that is not yet cached, the value will be considered invalid initially. Use the search-and-select window instead to select the value. The default search is specific to the first display field. You can modify the search expression from the List of Values page.

Configure a Cascading List of Values in a Layout

You can configure a cascading list of values if the REST service for your Excel workbook supports it.

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 use the Filter section in the Business Object Field Editor to configure a list of values for Countries, States, and Cities. To filter the list of values by the current country:

To filter the list of values by Country and State:

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 in the Filter sections:

Table 8-1 Example Name-Value Parameters in Filter

Field	Name	Value
StateId	finder	ByCountryFinder;CurrentCountry={ $options.fieldValues['CountryId'] }
CityId	finder	ByCountryAndStateFinder;CurrentCountry={ $options.fieldValues['CountryId'] },CurrenState={ $options.fieldValues['StateId'] }

If the service has complete metadata for a cascading list of values, the Filter section 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 use the Filter to manually configure a list of values. See Configure Filters for a List of Values.

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

You can use the Business Object Field Editor to configure the bind parameters used by segments in a descriptive flexfield (DFF).

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 tab.

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:

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

• 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.
  • there must be a Form-over-Table layout configured for that business object
  • the field referred to must be exposed in the form of the Form-over-Table 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.
• When the value is a compound string such as { expr1 } is the capital of { expr2 }, and expr1 resolves to Paris and expr2 resolves to France, the resolved value of the whole string will be "Paris is the capital of France".

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 for the bind parameter can only refer to field value of a form field in Form-over-Table layout. Referring to field value of a table (table portion of Form-over-Table layout, or Table layout) is not supported.
• 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 contains 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:

• 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.
• 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.
• 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".