Cascade Lists of Values

Environment (JDeveloper 11.1.1.4.0, ADF BC, HR schema)

A dependent list of values (also known as cascade LOV ) is one where the rows in the list of values are dependent on some factor. For example, The list of  state province is limited to the current selected country (for example, When the user select United Kingdom from the Country LOV, only the states of  United Kingdom should appear on States LOV).

How To Implement This Use Case:

The implementation of this example based on HR schema, mainly on LOCATIONS and COUNTRIES tables. The LOCATION table has two columns Country_ID which is a FK from COUNTRY table, and State_Province which its values are filled from the current available State_province in the LOCATIONS table. To implement this use case (assumed that you already built your business components for LOCATIONS and COUNTRIES tables):

1. Open your Locations view object, select the attribute nodes, then select CountryId attribute.
2. In the List of  values: CountryId section click the + green symbol to create LOV for this attribute.
3. In the Create List of Values window, click the green + symbol to add List data Source or select one if already exists. In our case click the green + symbol and  select the CountriesView object  from the Available View Objects area and shuttle it to the View Accessors area, then press OK. After return to Create List of Values window, select List attribute which maps to the CountryId attribute. Once you select the list attribute, the List return Values will be populated automatically.
   

   Create List of Values

4. Now, you need to set the display values for this LOV, within the same window, click UI Hints tab, then select choice list as Default list type and move the CountryName to the selected area as shown in figure below.
   

   Create List of Values

5. Now, we need to create a new view object as a data source for StateProvince attribute. Since the state province data source is the LOACATIONS view object we can create a view criteria or we can create a new read only view object. Suppose we chose the second choice (i.e. create a new read only view object), suppose the view object name was StateProvince. The query is:                                                                                       SELECT
   Distinct LOCATIONS.STATE_PROVINCE STATE_PROVINCE
   FROM
   LOCATIONS
   WHERE COUNTRY_ID=:currentCountry                                                                as you see, you need to create a bind variable for your view object.
6. Repeat steps from 1 to 4 for StateProvince attribute. However in step 3 we need to add one extra step. When you add Data source, and after you move the StateProvince view object to selected area, click the Edit button as shown in figure below.
   

   Add Data Source with Bind Variable

    

7. Now you can see Edit View Accessors: StateProvince1 window. in Bind Parameters Values section, you need to set our predefined currentCountry bind variable. set the value to CountryId, this is a groovy expression which represents the currently selected CountryId in current row.
   

   Set the Bind Variable

    

8. Now you can test your cascade LOV by run your ApplicationModule and ensure that it works as you expect.

If you need to enable this functionality in your page, you need to add two more steps.

1. Set autoSubmit property for CountryID list to true.
2. set the partial trigger for the StateProvince refer to the ID property for CountryId list.

(i.e. your page source should be something like this)

<af:selectOneChoice value=”#{bindings.CountryId.inputValue}”
label=”#{bindings.CountryId.label}”
required=”#{bindings.CountryId.hints.mandatory}”
shortDesc=”#{bindings.CountryId.hints.tooltip}”
id=”soc1″ autoSubmit=”true”>
<f:selectItems value=”#{bindings.CountryId.items}” id=”si1″/>
</af:selectOneChoice>

<af:selectOneChoice value=”#{bindings.StateProvince.inputValue}”
label=”#{bindings.StateProvince.label}”
required=”#{bindings.StateProvince.hints.mandatory}”
shortDesc=”#{bindings.StateProvince.hints.tooltip}”
id=”soc2″ partialTriggers=”soc1″>
<f:selectItems value=”#{bindings.StateProvince.items}” id=”si2″/>
</af:selectOneChoice>

Enjoy