13.1.3.4 Creating a Cascading List of Values

Create a cascading list of values locally with the definition of the item.

By creating a cascading list of values, one item on a page determines the list of values for another item. App Builder includes cascading list of values support for the following item types: Select List, Shuttle, Checkbox, Radio Group, Popup LOV, and List Manager.

You create a cascading list of values (LOV) by creating two items. To populate the second item, you specify a Cascading LOV Parent Item when running the Create Item Wizard or by editing the item's attributes.

The following example demonstrates how to create two related select lists where the value of the first list populates the second.

To create a cascading list of values:

1. View the page in Page Designer:
   1. On the Workspace home page, click the App Builder icon.
   2. Select an application.
   3. Select a page.
   Page Designer appears.
2. Create the first item, P1_DEPTNO:
   1. Create a new item from the Rendering tab or by adding it from the Gallery.

      The Property Editor displays the Page Item attributes. Attributes are organized in groups. To search for a group or an attribute, enter a keyword in the Filter Properties field. The Property Editor displays the group or attributes. Or, you can click Go to Group and select the group.

   2. In the Property Editor, edit the Identification and Label attributes:

      • Identification, Name - Enter P1_DEPTNO

      • Identification, Type - Select Select List.

      • Label, Label - Enter Department

   3. Under List of Values:

      • List of Values, Type - Select SQL Query.

      • List of Values, SQL Query - Enter:

        SELECT dname as d,
               deptno as r
          FROM dept
         ORDER BY dname
        

      • List of Values, Display Null Value - Select On.

      • List of Values, Null Display Value - Enter:

        - Select Department -
        

      • Accept the remaining defaults.

      • Click Save.

3. Create the second item, P1_EMPNO:
   1. Create a select list item.
   2. In the Property Editor, edit the Identification and Label attributes:

      • Identification, Name - Enter P1_EMPNO

      • Identification, Type - Select Select List.

      • Label, Label - Enter Employee

   3. Under List of Values:

      • List of Values, Type - Select SQL Query.

      • List of Values, SQL Query, enter:

        SELECT ename as d,
               empno as r
          FROM emp
         WHERE deptno = :P1_DEPTNO
        

      • List of Values, Display Null Value - Select On.

      • List of Values, Null Display Value - Enter:

        - Select Employee -

      • Accept the remaining defaults.

      • Click Save.

4. To save and run the page, click Save and Run Page.
   Two select lists appear. Making a selection in the Department select list, determines which individuals display in the Employees select list.