Monday, 12 October 2015

Create cascading list of values (LOVs) in ADF

Requirement: I have a demo.jspx page. In that page I have three af:selectOneChoice components. Each af:selectOneChoice component has list of values in it. In the first af:selectOneChoice component, we will have the CountriesId list of values. In the second af:selectOneChoice component, we will have the StateProvince list of values. In the third af:selectOneChoice component, we will have the City list of values.

Initially when demo.jspx page load, we will have values only in the first af:selectOneChoice component, that is we will have CountriesId list of values pre-populated. And the second and third af:selectOneChoice component will not have any values in it.

Once we select some value in the first af:selectOneChoice that is select CountriesId, then the corresponding StateProvince gets loaded in the second af:selectOneChoice component.

Similarly, when I select the StateProvince then the corresponding City gets loaded in the third af:selectOneChoice component.

Solution: For solution to the above requirement follow the steps as shown below: Here we will use the Locations and Countries table of the HR schema of the Oracle Database 11g XE.

Step 1: Create an Oracle ADF Fusion Web Application.

Step 2: Create an ADF Business Components from Tables for the Locations table.

Step 3: Create a CountriesVO from Custom SQL Query. The query used to create the CountriesVO is shown below:

select country_id, country_name from countries order by country_name

Create a StateProvinceVO from Custom SQL Query. The query used to create the StateProvinceVO is shown below:

select distinct state_province from locations where country_id=:bvCountryId order by state_province

Create a CityVO from Custom SQL Query. The query used to create the CityVO is shown below:

select distinct city from locations where state_province=:bvStateProvince order by city

Step 4: Create a List of Values on CountryId attribute of the LocationsVO. While creating the LOV the List Data Source should be CountriesVO1. Select CountryId as the List Attribute, and in the UI hint shuttle CountryName.

Create the List of Values for the StateProvince. In this case the List Data Source is StateProvinceVO1.

Similarly create the List of Values for the City. In this case the List Data Source is CityVO1.

Step 5: Now go to the Accessors tab and select StateProvinvceVO1 accessor to edit. Do the changes as shown below:

Similarly, select CityVO1 accessor to edit. Do the changes as shown below: 
Step 6: Save All and run the CascadingLovAM. The ran application is shown below:

Step 7: Create a demo.jspx in the ViewController project of our application. Select LocationsVO1 from the Data Control section and drag and drop it in the demo.jspx as an ADF Form.
Make the AutoSubmit=”true” for the CountryId and StateProvince components. Also make the PartialTriggers of the StateProvince field to point to soc1 (that is the ID of the CountryId component). Similarly, make the PartialTriggers of the City field to point to soc2  (that is the ID of the StateProvince component)

Step 8: Save all and run the application. Thus, the ran application is shown below:

Step 9: When I select the CountryId as “United States of America”, its corresponding StateProvince gets loaded in the StateProvince af:selectOneChoice component as shown below:

Similarly, when I select "Washington" as the StateProvince, then “Seattle”, gets loaded in the City af:selectOneChoice component as shown below:

Hence, the solution to our requirement.


Thanks & Regards,
Susanto Paul