Basing a list of values (LOV) on another LOV within a form

You can create two lists of values, one dependent on the other.

Suppose you create a form to look up department and employee information, and want to create drop-down lists that display department and employee names. You can create one static LOV that lists a department, then a dynamic LOV listing the employees within the selected department. For example, if you choose the Sales department, the second LOV will display all the salespeople in that department.

To base a dynamic LOV on a static LOV within a form:

1. Create a Static List of Values that displays the primary categories in the table, such as the department number, that will control the second list of values.

   For example, create a list of values called LOV_DEPT with a combobox format. Use the following SQL statement:

   select dname, deptno from scott.dept

2. Create a Dynamic List of Values that displays the items within the categories displayed in the LOV you just created, such as employee names.

   Note: Make sure the same provider owns both these LOVs, and that both LOVs use the same schema.

   For example, create a dynamic list of values called LOV_DEPTNO_EMP with a combobox format. Use the following SQL statement:

   select ENAME, EMPNO

   from SCOTT.EMP where deptno = :dept

3. Create a form (any type of form) based on the same schema as the two LOVs.

4. On the Formatting and Validation Options tab, in the left pane, click the column name for the column used as a bind variable, for example DEPTNO.

5. From the Item Type drop-down list, choose the display method for the LOV.

6. Make any other adjustments to the appearance of the LOV.

7. From the List of Values drop-down list, choose the static LOV, for example LOV_DEPT.

8. In the left pane, click the column name for the column that contains the information you want to display in the second LOV.

9. From the Item Type drop-down list, choose the display method for the LOV.

10. Make any other adjustments to the appearance of the LOV.

11. From the List of Values drop-down list, choose the dynamic LOV you created, for example LOV_DEPT_EMP.

12. Next to the bind variable (for example, DEPT), choose the column on which the second LOV is based (for example, DEPTNO).

13. Finish creating the form.

14. Run the form. The two LOVs display on the form. Choose a value from the static list (such as a department number). The second LOV then displays the values based on the value you chose.

Notes

• After choosing a value in the form, you can only select a blank value again if you click Refresh.

• When you create an LOV that depends on another LOV, you must create a bind variable in its SQL statement. For more information about bind variables, see your SQL documentation.

Back to top