How to implement cascading LOV's in search criteria

Cascading LOV is a common requirement. It is dependencies of an LOV on the selected value of another LOV. In this post, I will explain how cascade LOV can be implemented in a search criteria form.

Use Case:

Searching all the employees reporting to a manager in a department. For this, cascading LOV has to be used to define the dependencies between departments and the list of all employees in that department. i.e one LOV on DepartmentId and another LOV on ManagerId to retrieve all the employees in the selected DepartmentId LOV.

Steps:

1. Create Read Only view Objects on Employees, Departments table. (hr schema is used)

     Say EmployeesVO and DepartmentsVO

2. Define a view criteria (findByDepartmentId) in EmployeesVO to filter all the employees based on a DepartmentId.

 

3. Add DepartmentsVO and EmployeesVO as view accessors to EmployeesVO. In the added Employees view accessor, shuttle the view criteria(created in step #2) to the right. Provide the value for the bind variable as DepartmentId.

4. Define LOV's on DepartmentId (wrt Departments view accessor) and ManagerId (wrt Employees view accessor). Once a Department is selected in Department LOV, DepartmentId attribute will hold the value of selected department. This selected value is passed to the findByDepartmentId view criteria as defined in the above steps.

5. Define a view criteria (findByDepartmentMgrId) in EmployeesVO, which will be dragged and dropped as a query component on jspx page.

6. In the view controller project, create a jspx page. Drag the view criteria defined in the above step as query component. 

7. Run the jspx page. Dept Id LOV will show all the departments, while Mgr Id LOV will show all the employees for the selected Department. After selecting the Dept Id and Mgr Id values, click on Search button. The resultant table will show all the employees reporting to the selected manager of the selected department.

Sample Workspace:
Download the sample workspace from here.