Mutually dependent dropdown lists in ADF Faces – leveraging ADF BC Bind Parameters and ADF Faces Partial Page Rendering

This week we were having a session in which we had to estimate the effort required for a new ADF project. The application we have to develop will support a large chain of shows specializing in interior decorating. One of the functions in the application is to create the plan for decorating the homes of customers. In this plan, for every room in the house the salesman has to create a list of actions – paneling the floor, laying the carpet, doing the wallpaper and the curtains – and for each action the customer and salesman have to select the materials: which wallpaper, which carpet and which curtain fabric. Choosing the materials is a somewhat intricate process, that includes selecting the manufacturer, the color, the quality, the size and type etc. These choices are mutually dependent: after selecting a manufacturer, the list of colors and types is limited. However, the customer can also start by selecting a color, and that should limit the list of manufacturers to choose from. And choosing a price range or quality level will limit both lists of manufacturers and colors. Etcetera.

The question we had to answer is whether this functionality of mutually dependent lists of values in a List of Values selection window is a complex one, requiring up to several days of programming effort, or a simple one that would take in the order of a couple of hours. This morning – it being a Saturday and all – I decided to give it a try. And, as the project manager will gladly and smilingly learn, it turns out to be incredily simple. Even a couple of hours is overstating the effort. Good news for us and for our customer!

In this article I will demonstrate how easy it is to implement such mutually dependent selection lists. The example is – of course – Employees! I have created a small application where I can manage projects and the employees allocated on that project:

From the EmpId field I can bring a popup List of Values window where I can select an employee to add to the project. This window allows me to search the employees using several criteria, including Job, Department and Country. These search criteria are not independent: when I have selected a Job, the list of department can be shrunk as only a limited number of departments will contain employees in any given job. At the same time, when I select a country, the list of departments can be filtered, as only some departments will be located in the selected country. And when I select a Department, the list of Countries as well as the list of Jobs can be adjusted. In general: multiple search items have mutual influences. The question now becomes: how can I quickly implement the functionality to automatically refresh Countries and Departments when Job is changed, Departments and Jobs when Country is changed and Countries and Jobs when Department is changed?

Model – ADF BC

The starting point is an Application Module with Entities based on the table in the standard HR schema complemented with a PROJECTS and a PROJECT_ALLOCATIONS table. I created the default ViewObjects for these entities. 

I will acknowledge the mutual dependencies of the Countries, Jobs and Departments by creating read only view objects that will retrieve the records from the underlying tables in the HR schema. The ViewObjects will each have two bind parameters, representing and implementing the dependencies on the two other elements. For example for Departments:

Create ViewObject DepartmentsDomainView with bind parameters countryId and countryId. Define the SQL Query for this ViewObject as follows:

select d.department_id
,      d.department_name
from   departments d
where  ( :countryId is null
         or     
         :countryId = ( select l.country_id
                        from   locations l
                        where  l.location_id = d.location_id
                      )
       )
and    ( :jobId is null
         or     
         exists ( select 'x'
                  from   employees e
                  where  e.department_id = d.department_id 
                  and    e.job_id = :jobId
                )
       ) 

Then add a usage for the ViewObject to the Application Module’s DataModel.

I create similar ViewObjects for Countries and Jobs, and add usages for those to the AppMod’s DataModel as well.

The EmployeesView is composed from the Employee Employee Entity joined to several lookups: JOBS, DEPARTMENTS, LOCATIONS and COUNTRIES. This will make it possible to search for employees on criteria like Job Title, Department Name and Country Name.

ViewController – ADF Faces, ADF Model and generation using JHeadstart

The application is to be generated using JHeadstart, giving a regular, manually maintainable ADF Application with great productivity. After Enabling JHeadstart on this project and creating a new JHeadstart Application Definition file, I quickly create a Master and a Detail Group for Projects and Project Allocations. Next I create a Group LovEmployees for selecting Employees in a List of Values Window. I will link this LOV to the EmpId item in the Project Allocations Group. Finally I create three Dynamic Domains, based on the three ViewObjects for Countries, Jobs and Departments. This takes me some 10 to maybe 15 minutes.

Now things will get a little more interesting. I have indicated which items in the LovEmployees Group should appear in the table layout – the list of employees to select from – and which are search items that must be part of the Advanced Search region. For example: JobTitle is displayed in the table layout – but is not one of the Advanced Search items – while the associated JobId is not shown in the table but is an Advanced Search Item. The same relationship exists between CountryName and LkpCountryId and between DepartmentId and DepartmentName.

The LkpCountryId, JobId and DepartmentId should be displayed in the Advanced Search form as dropdownlists. We set the appropriate display type for these items and select the domain to derive the values from.

Furthermore, whenever the LkpCountryId item changes, the JobId and DepartmentId items must be updated. The same applies to changes in DepartmentId that should result in updates of LkpCountryId and JobId and changes in JobId that….etc. These mutual dependencies are declared through the Depends on Item(s) property at item level:

There is only one thing left for us to do: providing the values for the bind-parameters, such as the Job and Country Id values for the list of Departments or the Job and Department Id values for the countries.

I first generate the application, to see what the value binding is for the search items. In the generated LovEmployees.jspx page, I find for example:

<af:selectOneChoice id="SearchLovEmployeesDepartmentId" label="DepartmentId"  autoSubmit="true" immediate="true" 
     valueChangeListener="#{jhsPageLifecycle.updateModelValue}"   partialTriggers = " SearchLovEmployeesLkpCountryId SearchLovE
mployeesJobId"
     value="#
{searchLovEmployees.criteria.LovEmployeesDepartmentId}" unselectedLabel="" >
<af:forEach var="row2"  items="#{bindings.DepartmentsDomain.rangeSet}" >
  <af:selectItem id="SiAsLovEmployeesDepartmentId" label="#{row2.DepartmentName}" value="#{row2.DepartmentId}"/>
</af:forEach>
</af:selectOneChoice>   
 

Note that this SelectOneChoice has its partialTrigger attribute referring to the LkpCountryId and the JobId items; this is generated because of the Depends On Item(s) property setting. The value binding expressions for the DepartmentId search item turns out to be #{searchLovEmployees.criteria.LovEmployeesDepartmentId}. This is the expression we will use to provide a bind parameter value.  Let’s take the JobsDomain. Remember that the ViewObject JobsDomainView underlying this Domain has two bind parameters: countryId and departmentId. We can provide the bind parameter values – or rather have JHeadstart take of providing the proper values – by setting the Query Bind Parameters property for the JobsDomain domain:

For the CountriesDomain and the DepartmentsDomain, we have similar settings for the Query Bind Parameters:

• DepartmentsDomain: countryId=#{searchLovEmployees.criteria.LovEmployeesLkpCountryId},jobId=#{searchLovEmployees.criteria.LovEmployeesJobId}
• CountriesDomain: departmentId=#{searchLovEmployees.criteria.LovEmployeesDepartmentId},jobId=#{searchLovEmployees.criteria.LovEmployeesJobId}

Now regenerate the application and run it. Now we have the functionality we were after:

If we select a Department – for example Marketing – the JobId and LkpCountryId lists are immediately updated – restricted as a consequence of that primary choice:

and at the same time:

Clearing the Search Form and selecting a Job, for example Human Resources Representative, will automatically cause the list of Departments to be restricted

as well as the list of Countries to choose from:

Concluding: without any programming – except for the SQL Queries in the ViewObjects – and just a little bit of declarative specification, ADF and JHeadstart make it quite straightforward to implement a series of mutually dependent selection lists.

Resources

Download tje JDeveloper 10.1.3.2 Application: LovWithAjaxRefreshInSearch.zip.