Cascade LOV based on Multiselect LOV inside view criteria

Problem: Creating a cascade LOV based on single select drop down in af:query component (based on view criteria) works fine but ADF does not have out of the box support to create a LOV based on multi-select LOV i.e. values of the dependent LOV are not filtered based on the list selected in the parent LOV and it is even more difficult to make it work in af:query component i.e. using view criteria.

Solution: Vanilla creation of cascade LOV requires to create a bind variable in the dependent LOV view object which will accept the attribute value (bound to master LOV) from the main view object to filter the list. Main view object here refers to the VO which will have view criteria and LOVs defined. Master attribute's autoSubmit property(in the main view object) should be set to true so that the dependent LOV is filtered each time the value is changed in parent LOV. If we try the same thing by marking master LOV as multiselect , this arrangement won't work because the viewCriteriaItem for the master attribute will always return first value from the list i.e. value returned by expression

viewCriteriaItem.getValues().get(0).getValue().

but what we want is a comma separated string of the list of values selected in parent LOV which can then be fed to the query of the child LOV view object in the “IN clause”. This should fix our problem.

In our use case here we want the employees LOV to be filtered based on the multi-select Department LOV. For each department selected , employee drop down should be refreshed with employees in that department and we want this to happen inside af:query component created using view criteria.

Here is a view object for the departments LOV. 

Employees LOV view object defined with a bind variable which will get the list of departments 

selected in department LOV. This bind variable needs to be marked as required  and will be supplied to in_list_char  function which takes comma separated string and returns a list which can be supplited to "IN" clause in the query. in_list_char function has been taken from one of Jobinesh's blog . 

Now we will create a view object for Job History which will be used to view the job history and

will have lov's for departments and employees.

Define a LOV on DepartmentID using the DepartmentsLOV.

Set the autoSubmit property of DepartmentID to TRUE

Create a transient attribute in the view object for DeptIdList and set its autosumbit property to true .

Now define a LOV for EmployeeId using EmployeesLOV

In view object accessor for EmployeesLov , pass the DeptIdList for the bind variable vDeptId.

Create a view criteria in JobHistory view object.

Now from UI , drag & drop named criteria from data controls to create Query Panel with Table. Override the queryOperationListener in af:query component and bind it to a backing bean method.

Now inside backing bean method we need to check when ever the values for deparmtent id LOV is changed , we need to get all the departmentId values that were selected/de-selected by the user and set them into the transient attribute (DeptIdList) as a comma separated string. To get currently selected values in DepartmentsLOV we need to get the view criteria row and iterate through viewCriteria.getValues() which returns an arraylist of ViewCriteriaItemValue. 

ViewCriteria selectedVc = vo.getViewCriteriaManager().getViewCriteria("JobHistoryVOCriteria");

ViewCriteriaRow vcRow = (ViewCriteriaRow)selectedVc.getRows().get(0);

StringBuilder sb = new StringBuilder();

if ("DepartmentId".equals(attrName)) {

 ViewCriteriaItem vcItem = vcRow.getCriteriaItem("DepartmentId");

 ArrayList itemValList = vcItem.getValues();

   for (ViewCriteriaItemValue itemValue : itemValList) {

         if (!sb.toString().isEmpty()) {

                 sb.append(",");

         }

System.err.println("Deartment id --> " + itemValue.getValue());

                    sb.append(itemValue.getValue());

                }

    }

System.err.println("DeptIdList --> " + sb.toString());

ViewCriteriaItem jobItem = vcRow.ensureCriteriaItem("DeptIdList");

jobItem.setOperator("=");

jobItem.getValues().get(0).setValue(sb.toString());

Please download the attached application to try out for yourself . Cheers :) .Click here to download application