7 Use Lists of Values in an Excel Workbook

The Oracle Visual Builder Add-in for Excel enables you to associate a list of values with a business object field.

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. Each item in the list of values has a display value (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

When a business object field has a properly configured list of values, you can expect the following behavior in Table and Form-over-Table layouts in your Excel workbook:

• On download, the identity values are replaced by the display values
• On upload, the display values are replaced by the identity values
• When a user selects a cell that is not read-only, a search-and-select window shows a list of available display values; the user can then select one of these values. 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.

  The following 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.

  

The add-in supports multiple display fields for one list of values. In that case, values of all display fields are concatenated and the concatenated value is shown in a single Excel cell.

Note:

The list of values' data is cached in the workbook. 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 using the Business Object Field Editor.

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 in the Business Object Field Editor's List of Values tab.
2. Select the appropriate business object using the Edit icon next to the Referenced Business Object field.

   Tip:

   Pick a business object from the same catalog used to create the layout. 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).

3. Choose the appropriate identity field from the referenced business object.
4. Choose the desired display field from the referenced business object.
5. Configure Filter and Simple Search as desired (see subsequent sections for details).
6. Click Done.

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.

For more detail about using the add-in's editors to modify the business objects that your workbook uses, see Edit Service Descriptions and Business Objects.

Configure Filters for a List of Values

Define filters using name-value query parameters to let your users filter a field's list of values.

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.

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

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

Define search parameters to let your users enter a string to search for a value from a list of values.

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.

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*'.

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

When both Filter and Simple Search parameters are configured, Filter parameters are first applied, then Simple Search parameters. If multiple values are specified for one parameter, the last value takes effect.

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, you want to 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 Cascading List of Values in a Layout

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

For 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, 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.

When the service meets the following requirements, the add-in detects and wires up the cascading list of values automatically:

• The cascading list of values resource must be configured using static links with Finders. For example, for Countries, States, and Cities, the item links for the parent business object must be configured as follows:

  "links": {
    "CountryView1": {
      "operationRef": "http://servicehost/Countries/describe#/paths/~1Countries/get",
      "x-lov": {...}
    },
    "StateView1": {
      "operationRef": "http://servicehost/States/describe#/paths/~1States/get",
      "x-lov": {...},
      "parameters": {
        "finder": "ByCountryFinder%3BCurrentCountry%3D{CountryId}"
      }
    },
    "CityView1": {
      "operationRef": "http://servicehost/Cities/describe#/paths/~1Cities/get",
      "x-lov": {...},
      "parameters": {
        "finder": "ByCountryAndStateFinder%3BCurrentCountry%3D{CountryId}%2CCurrentState%3D{StateId}"
      }
    }

• For the POST/PATCH request to process attributes in order, the attributes must have dependencies configured properly. In our example, the State's dependencies array must be ["Country"] and City's dependencies array must be ["Country","State"].

In the Excel worksheet, the add-in exposes the corresponding business object fields as table columns (Countries, States, and Cities using our example). You can see and configure these columns much like other columns; no extra configuration is required. To implement a cascading list of values in your worksheet, do not remove any column from the cascading list of values columns.

The add-in also exposes each field's Finder parameters as part of the Filter section in the Business Object Field Editor. Based on our example metadata, where State depends on Country and City depends on Country and State, the following name-value parameters will show in the Filter section of the corresponding field's list of values:

Table 7-1 Example Name-Value Parameters in Filter

Field	Name	Value
StateId	finder	ByCountryFinder%3BCurrentCountry%3D{CountryId}
CityId	finder	ByCountryAndStateFinder%3BCurrentCountry%3D{CountryId}%2CCurrentState%3D{StateId}

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 a List of Values for a Business Object Field.

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 before you publish the workbook for data entry
• When cached data is not what the user expects.

Notes and Limitations for List 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.