8 Working with Lists of Values

Describes how to create dropdown lists of values (including dependent lists of values) in integrated Excel workbooks, in tables within workbooks, and how to display Search and Select list picker dialogs from Fusion web applications that users can invoke from workbooks.

This chapter includes the following sections:

8.1 About List of Values in an Integrated Excel Workbook

Consider implementing list of values in your integrated Excel workbooks for scenarios where you want to offer end users the ability to choose from a range of values or you want to constrain the values that end users can enter in the integrated Excel workbook.

ADF Desktop Integration provides a number of ways to address these use cases. You can, for example, configure:

  • A dropdown list of values in an Excel worksheet's cell

  • A dependent list of values where the values displayed in one list (the child list of values) depends on the selected value in another list (the parent list of values)

  • Configure the display of a Search and Select list picker dialog that provides advanced functionality for selecting values from lists

Consider configuring lists of values in the integrated Excel workbook in scenarios where the full set of values that end users can choose is relatively small (for example, numbers less than 30 values. The use of a Search and Select list picker dialog in a page from the Fusion web application may offer a better user experience when you have lists of values with more than 30 values.

8.1.1 Adding Lists of Values to Integrated Excel Workbooks Use Cases and Examples

Using the ADF List of Values component and other subcomponents from ADF Desktop Integration, you can create a variety of interfaces that present end users with data to view and select. Figure 8-1 shows a number of examples from the Summit sample application workbooks that subsequent sections in this chapter discuss in more detail.

Figure 8-1 List of Values Implementations in Summit Sample Application Workbooks

Description of Figure 8-1 follows
Description of "Figure 8-1 List of Values Implementations in Summit Sample Application Workbooks"

8.1.2 Additional Functionality for Adding List of Values to an Integrated Excel Workbook

After you have added lists of values to your integrated Excel workbook, you may find that you need to add additional functionality to configure your workbook. The following sections describe other functionality that you can use:

8.2 Creating a List of Values in an Excel Worksheet

Use the ADF List of Values component when you want to create a dropdown list of values in an Excel worksheet cell at runtime.

The ADF List of Values component is intended for a short choice list, for example 20 or 30 items at most, but can display a maximum of 250 values at runtime. You can insert the ADF List of Values component into a cell in the Excel worksheet. Figure 8-2 shows an implementation from the Summit sample application's EditWarehouseInventory-DT.xlsx where the user is constrained to picking one of the valid values for a list of regions.

Figure 8-2 Runtime List of Values in an Excel Worksheet

Description of Figure 8-2 follows
Description of "Figure 8-2 Runtime List of Values in an Excel Worksheet"

You must specify a value for the ListID property. The ListID property references the list binding which populates the dropdown menu with a list of values at runtime after you invoke the worksheet DownSync action.

Figure 8-3 shows an ADF List of Values component with its property inspector in the foreground. The ADF List of Values component references a list binding (RegionId) that populates a dropdown menu in the Excel worksheet at runtime.

Note:

  • You can display a dropdown menu in an ADF Table component's column. See Creating a List of Values in an ADF Table Component Column.

  • ADF List of Values components are intended for use with textual items; using date and numeric values is not supported.

  • ADF List of Values does not support configurations where multiple display attributes are selected at design-time.

Figure 8-3 ADF List of Values Component

This image is described in the surrounding text

To insert an ADF List of Values component:

  1. Open the integrated Excel workbook.
  2. Select the cell in the Excel worksheet where you want to anchor the component.
  3. In the components palette, select ADF List of Values and click Insert Component. Alternatively, in the Oracle ADF tab, select ADF List of Values from the Insert Component dropdown list
  4. Invoke the binding ID picker by clicking the browse (...) icon beside the input field for the ListID property and select a list binding that the page definition file exposes.
  5. Configure other properties in the property inspector to determine the appearance, design, and layout of the component. For information about ADF List of Values component properties, see ADF List of Values Component Properties.
  6. Click OK.

    Note:

    • You can modify the properties of the component at a later time by selecting the cell in the worksheet that anchors the component and then displaying the property inspector. You can also right-click in the cell and choose Edit ADF Component Properties to open the property inspector.

      To remove the component, use the Delete ribbon command, as described in Removing ADF Desktop Integration Components.

    • An Excel form cannot be configured to use ADF List of Values components that use model-driven list bindings if the form's bound iterator is expected to contain zero rows. As a workaround, you may configure the ADF List of Values component to use a dynamic list binding instead.

8.3 Creating a List of Values in an ADF Table Component Column

Use the ModelDrivenColumnComponent subcomponent when you want to render a dropdown list of values in an ADF Table component column.

The list of values component is intended for a short choice list, for example 20 or 30 items at most, but can display a maximum of 250 values at runtime. Unlike other ADF Desktop Integration components, the ModelDrivenColumnComponent subcomponent does not appear in the components palette described in Using the Components Palette. Instead, you select it as a subcomponent when you specify values for the UpdateComponent properties of an ADF Table component column. For information about the properties of an ADF Table component column, see ADF Table Component Column Properties. For information about creating a model-driven list, see Adding a ModelDrivenColumnComponent Subcomponent to Your ADF Table Component.

After you specify the ModelDrivenColumnComponent subcomponent, you must specify a tree binding attribute associated with a model-driven list as a value for the ModelDrivenColumnComponent subcomponent's Value property. The model-driven list of the tree binding attribute populates the dropdown menu in the ADF Table component's column with a list of values at runtime. For information about creating a model-driven list, see How to Create a Model-Driven List of Developing Fusion Web Applications with Oracle Application Development Framework.

8.3.1 How to Create a List of Values in an ADF Table Component Column

You add a column to the ADF Table component column and select ModelDrivenColumnComponent as the subcomponent. You then specify a tree binding attribute as the value for the ModelDrivenColumnComponent subcomponent's Value property. A model-driven list must be associated with the tree binding attribute that you specify.

Note:

  • The ModelDrivenColumnComponent subcomponent does not support a model-driven list whose control type is combo_lov.

  • Tree attributes with a control type of input_text_lov will not render as ADF List of Values components. Instead, they expose model-driven list picker functionality, as described in Adding a Model-Driven List Picker to an ADF Table Component.

  • ADF List of Values components using date values are not supported.

  • The ModelDrivenColumnComponent subcomponent may not support model-driven lists for EJB-based data controls in all cases.

Before you begin:

It may be helpful to have an understanding of how to create a list of values in ADF Table component. See Creating a List of Values in an ADF Table Component Column.

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. See Additional Functionality for Adding List of Values to an Integrated Excel Workbook.

To create a list of values in an ADF Table component column:

  1. Open the integrated Excel workbook.
  2. Select the cell in the Excel worksheet that references the ADF Table component and click the Edit Properties button in the Oracle ADF tab.
  3. In the Edit Component: ADF Table dialog, click the browse (...) icon beside the input field for Columns.

    The Edit Columns dialog appears, listing all the columns of the selected ADF Table component.

  4. Click Add to add a new column.
  5. Choose the appropriate option for the newly created column:

    • Click the browse (...) icon beside the input field for UpdateComponent to configure the runtime list of values for update and download operations.

    • (Optional) Click the browse (...) icon beside the input field for InsertComponent to configure the runtime list of values for insert operations. This is rare.

    In both options, the Select subcomponent to create dialog appears.

  6. Select ModelDrivenColumnComponent and click OK.
  7. Expand the property that you selected in Step 5 and select a binding attribute associated with a model-driven list for the Value property.

    Set the ReadOnly property to False if you do want users to edit the values in the column, set to True otherwise.

    Figure 8-4 shows the property inspector for the Credit Rating column that renders in the Summit sample application's EditCustomers-DT.xlsx workbook.

    Figure 8-4 ADF Table Component Column Configured to Display a List of Values

    This image is described in the surrounding text
  8. Click OK.

8.3.2 What Happens at Runtime: How the ADF Table Column Renders a List of Values

At runtime, the ADF Table component invokes the Download action and populates each column. This action also populates the list of values in the column that you configure to render a list of values. Figure 8-5 shows an example from EditCustomers-DT.xlsx workbook in the Summit sample application, where Credit Rating is the column configured to display a list of values.

Figure 8-5 Runtime View of an ADF Table Component Column Displaying a List of Values

Description of Figure 8-5 follows
Description of "Figure 8-5 Runtime View of an ADF Table Component Column Displaying a List of Values"

8.4 Adding a Model-Driven List Picker to an ADF Table Component

You can configure an ADF Table component and use the existing model-layer metadata of your Fusion web application to provide a Search and Select list picker dialog in the integrated Excel workbook.

The Search and Select list picker dialog is similar to that seen when you click the search icon or button to open the Search and Select popup of the af:inputListOfValues component on an ADF Faces page. Figure 8-6 shows an example from the EditCustomers-DT.xlsx workbook where an end user double-clicks the cell where they want to input a new data value. They search and select the new value in the popup that appears.

Tip:

Consider adding a column header tooltip that instructs users to double-click column cells in order to pick a value.

Figure 8-6 Model-Driven List Picker Invoked from Table Column Cell

This image is described in the surrounding text

To add a model-driven list picker to an ADF Table component:

  1. Open your Fusion web application in JDeveloper.

  2. Configure your view object in the same way as you would to use an af:inputListOfValues component.

    1. Add a view accessor.

      For information about creating a view accessor, see How to Create a View Accessor for an Entity Object or View Object in Developing Fusion Web Applications with Oracle Application Development Framework.

    2. Create a List-of-Values (LOV) for the attribute.

      For information about creating a List of Values component, see Creating List of Values (LOV) in Developing Fusion Web Applications with Oracle Application Development Framework

    3. Set the UI Hints for the LOV. Ensure that Default List Type is set to Input Text with List of Values.

      For information about setting UI Hints, see How to Set User Interface Hints on a View Object LOV-Enabled Attribute in Developing Fusion Web Applications with Oracle Application Development Framework.

    4. Expose the view object as a tree binding in the page definition used by your worksheet.

  3. Verify that your application's web.xml file configures the filter for ADF Library Web Application Support (<filter-name>ADFLibraryFilter</filter-name>).

    For information, see Configuring the ADF Library Filter for ADF Desktop Integration.

  4. Open the integrated Excel workbook.

  5. For any table columns bound to LOV-backed attributes, be sure to use the ModelDrivenColumnComponent component type in the column configuration. Note that for table columns configured in this way, the DoubleClickActionSet property of the UpdateComponent and InsertComponent will be ignored at runtime.

    Figure 8-7 shows the type of component of the UpdateComponent property set to ModelDrivenColumnComponent.

    Figure 8-7 UpdateComponent Property of the ADF Table Component

    Description of Figure 8-7 follows
    Description of "Figure 8-7 UpdateComponent Property of the ADF Table Component"

  6. If not set already, set the Workbook.Compatibility.TableComponents.ModelDrivenColumns.InputListOfValuesPickerEnabled property to True.

    For information about the InputListOfValuesPickerEnabled property, see ADF Desktop Integration Compatibility Properties.

  7. (Optional) Configure RowData.CachedAttributes for the ADF Table component when a different attribute on the underlying iterator should be set by the action of the model-driven list picker. For example, in the EditCustomers-DT.xlsx workbook, the Sales Rep. column exposes a ModelDrivenColumnComponent subcomponent, but also defines SalesRepId as a value for RowData.CachedAttribute.

After configuring the Fusion web application, integrated Excel workbook, and table columns, run the workbook and double-click the table columns that expose LOV-backed attributes to open the model-driven list picker dialog.

8.4.1 What You May Need to Know About Model-Driven List Pickers in ADF Table Components

By default, all columns in a table are configured to use the ModelDrivenColumnComponent subcomponent when you create an ADF Table component by double-clicking a tree binding in the bindings palette. Any tree attributes bound to model-driven lists with a control type of input_text_lov automatically support the rendering of a Search and Select list picker dialog at runtime. That is, no special configuration is needed.

If the tree attributes are not bound to model-driven lists or if you need a custom picker user-interface, see Displaying Web Pages from a Fusion Web Application.

Note:

End users may initially see an empty model-driven list picker dialog if the ADF Table component’s ModelDrivenColumnComponent subcomponent exposes a view object attribute that has multiple list of values (LOV) defined on it through an LOV switcher. If an empty model-driven list picker dialog appears, end users should cancel and re-invoke the dialog to render the expected content in the dialog. See Creating List of Values (LOV) in Developing Fusion Web Applications with Oracle Application Development Framework.

Ensure that a row exists in the tree node, especially on child tree nodes, for any attribute configured to show a model-driven list picker dialog.

8.5 Creating Dependent Lists of Values in an Integrated Excel Workbook

You can create a dependent list of values in an integrated Excel workbook using the list of values components that ADF Desktop Integration provides and by configuring the server-side list bindings appropriately.

ADF Desktop Integration provides the following components that you use to create lists of values in an integrated Excel workbook:

  • ADF List of Values

    You configure properties for this component when you want to create a list of values in the Excel worksheet.

  • ModelDrivenColumnComponent subcomponent

    You configure properties for this subcomponent when you want to create a list of values in an ADF Table component column.

Using these two components, you can create a dependent list of values in your integrated Excel workbook. A dependent list of values is a list of values component (referred to as a child list of values) whose values are determined by another list of values component (referred to as a parent list of values).

The server-side list bindings must be defined such that when the selected item of the parent list of values is changed, the available child list of values items are updated properly. Figure 8-8 shows an example with two illustrations from the EditWarehouseInventory-DT.xlsx workbook, where the Country field (child list of values) changes when the value in the Region field (parent list of values) changes.

Figure 8-8 List of Values and Dependent List of Values

This image is described in the surrounding text

Table 8-1 describes the dependent list of values implementations you can create using the previously listed components and the requirements to achieve each implementation.

Some of the implementations described in Table 8-1 require model-driven lists. For information about creating a model-driven list, see How to Create a Model-Driven List in Developing Fusion Web Applications with Oracle Application Development Framework.

Table 8-1 Dependent List of Values Configuration Options

Configuration Requirements

Render both the parent and child list of values in the Excel worksheet using ADF List of Values components.

Both instances of the ADF List of Values component must reference a list binding. One or both of the list bindings that you reference can be model-driven lists.

Both list bindings can reference model-driven lists only if the underlying iterator has at least one row of data. At runtime, if the underlying iterator has zero rows of data and the end user selects a value from the parent list of values (list binding referenced by the ADF List of Values component's DependsOnListID property), the child list of values (list binding referenced by the ADF List of Values component's ListID property) does not get filtered based on the value the end user selects.

To work around this scenario, choose one of the following options:

  • Ensure that the underlying iterator has at least one row of data

  • Use an alternative list binding configuration where you expose multiple iterators and all necessary iterators get refreshed

See How to Create Dependent Lists of Values in Excel Worksheets.

Render both the parent and child list of values in ADF Table component columns using ModelDrivenColumnComponent subcomponents.

Both the parent and child list of values (ModelDrivenColumnComponent subcomponents) must reference tree binding attributes associated with model-driven lists.

Both columns (parent and child list of values) must use the same value for the InsertUsesUpdate column property.

As server-side list binding dependencies are determined only for lists in the same tree node, the following tree node list bindings are not supported:

  • A binding that depends on a list binding in a different tree or tree node

  • A binding that depends on a list binding in the page definition file

See How to Create Dependent Lists of Values in ADF Table Component Columns.

Note the following points if you plan to create a dependent list of values:

  • If the selection in the parent list of values changes, the child list of values is reset without warning the user.

  • The dependent list of values does not work unless the list specified in the DependsOnList (or DependsOnListID) property is referenced by a component in the Excel worksheet.

  • If a circular dependency is defined (List A depends on List B, and List B depends on List A), the first dependency (List A depends on List B) triggers the expected behavior. ADF Desktop Integration considers other dependencies to be misconfigurations.

  • You can create a chain of dependencies as follows:

    • List A depends on List B

    • List B depends on List C

    In this scenario, a change in List C (grandparent list of values) updates both Lists A (grandchild list of values) and B (child list of values). If you create a similar scenario, you must ensure that both the grandchild list of values and the child list of values, get refreshed whenever the parent list of values selection is changed. You can do this by specifying the two bind variables on the grandchild list of values to set up an implicit dependency between the view attributes. Another way is to declare explicit attribute dependencies between each of the view attributes that have model-driven lists configured. For example, specify that attribute A depends on attribute B and attribute C, and attribute B depends on attribute C.

  • Caching in a dependent list of values is discussed in Caching Lists of Values for Use Across Multiple Web Sessions.

  • ADF Desktop Integration caches the values that appear in a dependent list of values. Hence, the dependent list item values for a given parent list selection must remain constant across all rows of an ADF Table component.

  • ADF List of Values components using date values are not supported.

8.5.1 How to Create Dependent Lists of Values in Excel Worksheets

Use two instances of the ADF List of Values component to create a dependent list of values in an Excel worksheet.

Specify the list binding referenced by the parent ADF List of Values component as a value for the child ADF List of Values component's ListOfValues.DependsOnListID property.

For information about ADF List of Values, see ADF List of Values Component Properties.

Before you begin:

It may be helpful to have an understanding of dependent list of values. See Creating Dependent Lists of Values in an Integrated Excel Workbook.

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. See Additional Functionality for Adding List of Values to an Integrated Excel Workbook.

To create a dependent list of values in an Excel worksheet:

  1. If not present, add the required list bindings to your page definition file.

    For information about adding bindings to page definition files, see Working with Page Definition Files for an Integrated Excel Workbook.

  2. Open the integrated Excel workbook.
  3. Insert two ADF List of Values components into your integrated Excel workbook, as described in Creating a List of Values in an Excel Worksheet.
  4. In the property inspector for the ADF List of Values component that is to serve as the parent in the dependent list of values, set the value of the ListOfValues.ListID property to the list binding that is the parent.
  5. In the property inspector for the ADF List of Values component that is to serve as the child in the dependent list of values, set the following properties:

    • ListOfValues.ListID

      Specify the list binding that is the child in the dependent list of values.

    • ListOfValues.DependsOnListID

      Select the list binding that you specified for the ADF List of Values component that serves as a parent in Step 4.

      Figure 8-9 shows the property inspector for the child ADF List of Values where the RegionId list binding is specified as the parent list of values (DependsOnListID property) and CountryId list is the dependent list of values (ListID property).

      Figure 8-9 Design Time Dependent List of Values in an Excel Worksheet

      This image is described in the surrounding text
  6. Click OK.

8.5.2 What Happens at Runtime: How an Excel Worksheet Renders a Dependent List of Values

At runtime, ADF Desktop Integration renders both instances of the ADF List of Values component. When the end user selects a value from the parent list of values, the selected value determines the list of values in the child list.

Figure 8-10 shows an example where Country, a dependent list value, displays only the states from the selected Region list value.

Figure 8-10 Runtime Dependent List of Values in an Excel Worksheet

Description of Figure 8-10 follows
Description of "Figure 8-10 Runtime Dependent List of Values in an Excel Worksheet"

8.5.3 How to Create Dependent Lists of Values in ADF Table Component Columns

Use instances of the ModelDrivenColumnComponent subcomponent to render both lists of values in a dependent list of values in ADF Table component columns at runtime.

Specify a tree binding attribute for the parent ModelDrivenColumnComponent subcomponent's Value property. Also specify a tree binding attribute for the child ModelDrivenColumnComponent subcomponent's Value property. Ensure that both tree binding attributes are associated with model-driven lists before you add the tree binding to your page definition file. Ensure also that the dependency between the parent and child model-driven lists is configured on the server.

The Region and Country columns in the Summit sample application's EditCustomers-DT.xlsx workbook demonstrate an implementation of a dependent list of values in an ADF Table component.

The following links provide information about:

Before you begin:

It may be helpful to have an understanding of dependent list of values. See Creating Dependent Lists of Values in an Integrated Excel Workbook.

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. See Additional Functionality for Adding List of Values to an Integrated Excel Workbook.

To create a dependent list of values in an ADF Table component:

  1. Open the integrated Excel workbook.
  2. If not present, insert an ADF Table component, as described in Inserting an ADF Table Component into an Excel Worksheet.
  3. In the property inspector for the ADF Table component, invoke the Edit Columns dialog by clicking the browse (...) icon beside the input field for Columns.
  4. Add a new column (or modify an existing column) to serve as the parent list of values. Specify ModelDrivenColumnComponent as the column's subcomponent type. For more information about creating a list of values, see Creating a List of Values in an ADF Table Component Column.
  5. Add a new column (or modify an existing column) to serve as the child list of values. Specify ModelDrivenColumnComponent as the column's subcomponent type and specify the same value for the InsertUsesUpdate column property as the column in the parent list of values. Both columns (parent and child list of values) must use the same value for the InsertUsesUpdate column property. For more information about creating a list of values, see Creating a List of Values in an ADF Table Component Column.
  6. Click OK.

8.5.4 What Happens at Runtime: ADF Table Component Column Renders a Dependent List of Values

At runtime, the ADF Table component renders both instances of the ModelDrivenColumnComponent subcomponent in the columns that you configured to display these instances. When the end user selects a value from the parent list of values, the selected value determines the list of values in the child list.

Figure 8-11 shows an example from the Summit sample application's EditCustomers-DT.xlsx workbook where the value that the end user selects in the Region column list of values (Europe) results in the corresponding values for sub-category appearing in the Country column list of values (France, Germany, and so on).

Figure 8-11 Runtime Dependent List of Values in an ADF Table Component's Columns

This image is described in the surrounding text

Note:

When the end user changes the parent list selection, the child list items are changed for the current row only.