Use Lists of Values in an Excel Workbook

7 Use Lists of Values in an Excel Workbook

The Oracle Visual Builder Add-in for Excel provides support for a list of values for business object fields.

A list of values represents a relationship between a given field in one business object and a separate referenced business object. The list of values is the items that constitutes the valid set of values for a business object field. 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, a Job attribute for an Employee business object might contain the following display and identity values.

For display values, the add-in supports lists of values of string. For identity values, the add-in supports integer or string. Decimal numbers, dates, and date-time values are not supported for lists of 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 in a table column that is not read-only, a search box appears that displays available values. A user can select one of the displayed values. Alternatively, the user can type one or more of the starting characters for other values and then click the Search icon so that the search box filters the values it displays based on the user input. The following composite image displays both of the just-described scenarios. For the latter scenario, the user entered S to filter the search box to display values from the list that begin with S (Sales Manager, Sales Representative, and so on). Users can also type in the new value, assuming the value they type is 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:

When using a list of values with multiple display fields, if you enter a value directly in the cell that has not yet been cached, the value will be considered invalid initially. So, use the search box to select the value instead. 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 any eligible field.

You access this editor from the Edit icon beside the Business Object field in the Layout Designer, or by double-clicking a column or form field in the Layout Designer. Once you open the Business Object Editor for the business object, click the Fields tab and select the desired business object field. To open the Business Object Field Editor, click the Edit icon that appears in the upper right of the Business Object Editor, as shown in the following image where the user opened the Business Object Field Editor for the employee business object’s Job Title field.

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

Enabled: Selected if the business object field has a list of values
Referenced Business Object: This business object provides the display values for the matching 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 in the current field

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

Select the Enabled checkbox in the List of Values tab of the Business Object Field Editor.
Select the appropriate business object using the Edit icon beside the Referenced Business Object field.

Tip:
If your service definition is missing the desired business object, you can add a new business object description to an existing service description. Click Manage Services. Open the desired service and click the Import button.
Choose the appropriate identity field from the referenced business object.
Choose the desired display field from the reference business object.

The properties that appear under the Simple Search Transform section determine how the add-in retrieves the values to appear in the search box next to the cell in the worksheet. The query parameter value is a string expression that may include {$options.simpleSearch}. The add-in replaces this expression with the input the user types in the search box. The add-in does not perform any validation on the values that you enter, so the add-in will make invalid requests that return errors if you enter invalid information.

Add values for the Name and Value properties based on what the REST service you use supports. Many REST services support a specific query parameter like q for search, as in the following example:

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

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

LAST_NAME LIKE '{$options.simpleSearch}*'

If the user types Jo in the search box and clicks the Search icon, the resulting request to the referenced business object will be something like:

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

Note:

Consult the API documentation for the service you have chosen to use to determine the appropriate search syntax to use in the Value column. For example, if you use an Oracle RAMP REST service, you want to consult Understanding Framework Support for Query Syntax. Remember that the add-in uses REST framework version 6 when making requests to a RAMP service.

Description of excel-addin_lov-editors.png follows

Description of the illustration excel-addin_lov-editors.png

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 to redraw the layout and clear the cached data. Then, click the Clear List of Values Cache button that you access from the Advanced dropdown list in the Misc ribbon tab.

Edit Service Descriptions and Business Objects provides more detail about using the editors that the add-in provides to modify the service description that your workbook uses.

Configure a Cascading List of Values in a Layout

The add-in also includes support for cascading list of values if the REST service that the workbook uses is of type Oracle RAMP REST Service. That is, the value selected in one list determines the ranges of values that users can select from subsequent lists. For example, a table renders 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.

To implement this type of list, the REST service that the add-in connects to must meet the follow requirements:

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": {
      "definedOn": "$request.body#/CountryId",
      "valueField": "Id",
      "displayFields": [
        "Name"
      ]
    }
  },
  "StateView1": {
    "operationRef": "http://servicehost/States/describe#/paths/~1States/get",
    "x-lov": {
      "definedOn": "$request.body#/StateId",
      "valueField": "Id",
      "displayFields": [
        "Name"
      ]
    },
    "parameters": {
      "finder": "ByCountryFinder%3BCurrentCountry%3D{CountryId}"
    }
  },
  "CityView1": {
    "operationRef": "http://servicehost/Cities/describe#/paths/~1Cities/get",
    "x-lov": {
      "definedOn": "$request.body#/CityId",
      "valueField": "Id",
      "displayFields": [
        "Name"
      ]
    },
    "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. For example, a cascading list of values that includes Countries, States, and Cities, State's dependencies array should be ["Country"] and City's dependencies array should be ["Country","State"].

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

For any read-only field (column or form), the add-in still swaps identity values for display values and vice versa, as described above. However, the search box does not appear when the cell is selected.

Note the following limitations that apply to list of values and cascading list of values:

The add-in supports list of values that are defined with a single identity field.
Cascading list of values is only supported for Oracle RAMP REST services.
Cascading list of values behavior is not configurable in the Business Object Field Editor.
For Oracle RAMP REST services, "row context" list of values are not supported.