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

    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

About these ads

13 Comments

Filed under ADF

13 responses to “Cascade Lists of Values

  1. Jiang

    It this way, you can get the cascade lov. But if you set the StateProvince select list’s required attribute to be true, then the cascade lov will not work(the cascaded list says it is required, and display nothing in its select list), do you have any idea or solution to solve this problem?

  2. Rakesh

    Does Cascading LOV work if CoulmnA and ColumnB are of string type rather than of integer type?

  3. I have cascading lovs in an updatable view object. The second one should be populated with the selection of first one. That is happening but the second lov is not getting any value for the updatable view object.
    System.out.println( row.getAttribute(“ColumnName”));
    this statement giving null and while commiting the updatable view object it is inserting no value for the particular field? Can you help me out…

  4. HI Mohammad the types are same

  5. Thomas

    i have 2 cascading LOVs. when in edit mode, they work fine, the dependent LOV changing upon changing the parent. when i go to add mode, the dependent LOV doesn’t change… it merely blanks out and says it needs a value. it’s almost like the first list changing doesn’t get sent to the server. any ideas why? i’m running JDeveloper 11.2.3.0

  6. Thomas

    i tried that but it did not solve the issue. but i solved my problem another way… by using a LOV choice list instead of an ADF select one choice. now i have another issue.
    upon selecting a parent LOV the dependent LOV populates correctly but the current value doesn’t null itself out. i tried putting a Value Change Listener in the bean to do so, but it does not work.
    any ideas?

  7. Thomas

    i have another issue as well! fun huh! i need to get data from another table based upon the values in 2 other LOVs. i put the field in as a LOV and set the PartialTriggers property to the other 2 LOVs, but it doesn’t seem to work.
    any ideas on this one?
    btw, this is a great blog. thanks!

  8. Thomas

    now upon running the app, the entire form below is read only!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s