8 Use Lists of Values in an Excel Workbook

You can configure a list of values for a field in your workbook to allow business users to select a valid value from a drop down list. You can also allow users to enter a search term in a search box to filter this list to find the value they want.

Oracle Visual Builder Add-in for Excel supports lists of values on business object fields including custom action payload fields and descriptive flexfields with parameter and segment type client binds. The add-in also supports lists of values for row finder variables.

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 the display fields in separate columns in the search-and-select window but concatenates these values 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 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

Configure a list of values for a business object field, custom action payload field, or a row finder variable.

When you create a list of values, you 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 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. 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.

1. From the Layout Designer, click the Edit () icon next to the Business Object field.
2. From the Business Object Editor, click the Fields tab, then select the business object's field.
3. Click the Edit () icon to open the Business Object Field Editor, then click the List of Values tab.
4. Select the Enabled check box on the List of Values page.
5. 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.

   This business object provides the display values for the corresponding identity values.

6. Choose the appropriate identity field from the referenced business object.

   This is the field in the referenced business object used to look up the display values for the identity values in the current field.

7. Choose the desired display field from the referenced business object.

   These fields come from the referenced business object 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.

8. 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 no search term.

   Repeat this step to create additional parameters.

9. 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 Redraw Layout. Then, click Clear List of Values Cache from the Advanced menu in the Misc ribbon tab.

Configure a Filter for a List of Values

Configure a filter for your list of values to determine which items from the referenced business object 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.

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 no search term 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:

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:

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

Configure a filter that filters a list of values dynamically using a value from the current layout.

For example, you may have an Employees layout with a Job Title field and want users to see only job titles for the current employee's department. To do this, configure a parameter on the Job Title list that filters based on the current row's 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—in this case, DepartmentId. Note that, since this expression refers to a field in the currently selected row, you must make sure this field has been added to your layout.

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.

When the business user selects the field for an employee in the marketing department, the list includes only job titles associated with Marketing.

Include a search term parameter to further limit the list to jobs in the current department that start with the search term.

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:

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

• 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.
• 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 no search term 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.

  
  
  
  
  

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

You can use the Business Object Field Editor to configure parameter-type client binds 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:
   
   
   
   

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

  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 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".
• For a list of values configured on a read-only business object field, if the display field is the same as the identity field, the list of values configuration is ignored.