Thursday, July 5, 2012

Multi-Select Dependent Drop-Down Lists

I was recently at a customer's site where I had an interesting requirement so I have decided to blog about it. The requirement was to build a master/detail drop-down list. The challenging part was that they wanted the master drop-down list to be a multi-select drop-down list and the detail drop-down list should populate its details based on the master’s selection.

Since ADF does not support master/detail lists for multiple selections i had to follow a different approach. I used the HR schema, with the Departments table as the master drop-down list and the Employees table as the child detail drop-down list. So i created default business component objects (entities and views) based on the Departments and Employees tables.

The trick here is on the detail view object that will serve as the dependent drop-down list. I have created a string bind variable to pass the selected department Ids as a comma separated string of department ids. As you may know, the View Object SQL Query, if used as such, will interpret the whole comma separated value as a single string and of course the query will fail. So to overcome this issue, i have changed the SQL Query using the regexp_substr regular expression function to split the comma separated string and return them as rows (special thanks to Arun).

Employees.DEPARTMENT_ID in (select regexp_substr(:DeptId,'[^,]+', 1, level) from dual connect by regexp_substr(:DeptId, '[^,]+', 1, level) is not null)

In the Application Module, make sure that you add the Departments view object and Employees view object as separate view objects and not dependent. The master/detail dependence will be implemented in a managed bean.

So, create a page and drag on the page the Departments view as a Select Many Choice component and the Employees view object as a Select One Choice component.Make sure that you set the AutoSubmit attribute of the Departments Select Many Choice component to true and in the PartialTriggers attribute of the Employees Select One Choice you have a reference to the Departments Select Many Choice component so that the employees can listen for changes in the departments component.

Go to  the page bindings and create an action binding to the ExecuteWithParams operation of the Employees view object.Your page definition should resemble as below.

The last thing that needs to be done is get the selected department ids, construct a comma separated list of department ids, pass it to the DeptId bind variable of the Employees view and execute the query so that the Employees Select One Choice component gets populated with all employees that belong to all the above selected departments. To do so, edit the ValueChangeListener of the Department Select Many Choice Component and add the following java code to the exposed value change listener method.

public void onDepartmentChange(ValueChangeEvent valueChangeEvent) {
    BindingContainer bc = BindingContext.getCurrent().getCurrentBindingsEntry();

    BindingContext bctx = BindingContext.getCurrent();
    BindingContainer bindings = bctx.getCurrentBindingsEntry();
    DCIteratorBinding departmentIterator = (DCIteratorBinding)bindings.get("DepartmentsVOIterator");

    String deptId = null;
    // retrieve selected departments
    if (this.getDepartmentId().getValue() != null) {
        try {
            Integer[] departmentIds = (Integer[])this.getDepartmentId().getValue();

            if (departmentIds.length != 0) {
                for (int x = 0; x < departmentIds.length; x++) {
                    int departmentId = departmentIds[x];
                    Row department = departmentIterator.getRowAtRangeIndex(departmentId);
                    System.out.println("Department Id Index: " + departmentId + " - Department Id: " +
                                       department.getAttribute("DepartmentId"));
                    if (x == 0) {
                        deptId = ((Number)department.getAttribute("DepartmentId")).toString();
                    } else {
                        deptId =
                                deptId + "," + (String)((Number)department.getAttribute("DepartmentId")).toString();
                    }
                }
            }
        } catch (ClassCastException e) {
        }
    }

    OperationBinding queryEmps = bc.getOperationBinding("ExecuteWithParams");
    Map queryEmpsMap = queryEmps.getParamsMap();
    queryEmpsMap.put("DeptId", deptId);
    queryEmps.execute();
}

As you can see, all employees belonging to the selected departments (Administration, Shipping and IT) are populated in the employees Select One component.

Download Sample Application - MultiSelectDependentComboBox