This discussion is archived
11 Replies Latest reply: Jul 12, 2013 7:12 AM by CraigB RSS

a dependent poplist requirement

990187 Newbie
Currently Being Moderated
i have two poplist names: country and city. countries:india,pakistan,srilanka if i choose india or pakistan or srilanka in the cities poplist column automatically displays the related cities which belong to the countries.Can anyone help it out?
note: i know to create a poplist
  • 1. Re: a dependent poplist requirement
    1000233 Newbie
    Currently Being Moderated
    Hi,

    Why cant you associate you cities item with and LOV insteadt of list item.

    you keep you country box as list value. then you created LOVs for all the countries and respective record groups. and assoiate it dynamically in runtime in when-list-changed trigger or any package.

    using
    app_item_property.set_property
    ('BLOCK_NAME.item_name',
    lov_name,
    'LOV_country1');

    Hope this helps. :)
  • 2. Re: a dependent poplist requirement
    IK Newbie
    Currently Being Moderated
    Hi,

    if you have country_id column in city table, when select country using country lov assign its ID(country_id) to country_ID text item in controls block, and change the query of city lov as


    select city_id,city from city where city.country_id=:controls.country_id;

    hope this may also helps...
  • 3. Re: a dependent poplist requirement
    CraigB Guru
    Currently Being Moderated

    As *IK* indicated, your City Table will need to have a country identifier in order for this to work.  Making this work is not too difficult.  You're going to use the CREATE_GROUP_FROM_QUERY() and POPULATE_GROUP() built-in's to populate your Poplist groups.  Dependent PopList's are typically populated at the time you load your form (eg: When-New-Form-Instance [WNFI] trigger) and when you change the selected item in the master list (eg; When-List-Changed [WLC] trigger on the poplist item).  With your example of Country and City poplists, the Country is the Master poplist and the City is the Dependent poplist.  Therefore, you would populate the Country Poplist in your WNFI trigger.  For example:

     

     

    /* Sample When-New-Form-Instance trigger */
    /* Code not tested... */
    DECLARE
      rg_name VARCHAR2(7) := 'COUNTRY';
      rg_id   RECORDGROUP;
      list_id ITEM;
      v_sql   VARCHAR2(1000);
      n_dummy NUMBER;
    BEGIN
      v_sql := 'SELECT country_id, country_name FROM country';
      list_id := Find_Item('YOUR_BLOCK.COUNTRY_ID');
      rg_id := CREATE_GROUP_FROM_QUERY(rg_name, v_sql);
      n_dummy := POPULATE_GROUP(rg_name);
      -- Clear List of any Design-Time values.
      CLEAR_LIST(list_id);
      POPULATE_LIST(list_id, rg_name);
    END;

     

    Now, you have a list of countries in your country Poplist and your City poplist is blank because a country hasn't been selected yet.  When a user selects a country from the country Poplist, you will populate the City Poplist using the Country_ID WLC trigger.  For example:

     

     

    /* Sample When-List-Changed trigger */
    /* Code not tested */
    DECLARE
      rn_name    VARCHAR2(4) := 'CITY';
      rg_id      RECORDGROUP;
      list_id    ITEM;
      v_sql      VARCHAR2(1000);
      n_dummy    NUMBER;
    BEGIN
      v_sql := 'SELECT city_id, city_name FROM city WHERE country_id = :YOUR_BLOCK.country_id';
      list_id := FIND_ITEM('YOUR_BLOCK.CITY_ID');
      rg_id := CREATE_GROUP_FROM_QUERY(rg_name,v_sql);
      n_dummy := POPULATE_GROUP(rg_name);
      --Clear the City_ID List first
      CLEAR_LIST(list_id);
      POPULATE_LIST(list_id, rg_name);
    END;

    Hope this helps.

    Craig...

  • 4. Re: a dependent poplist requirement
    990187 Newbie
    Currently Being Moderated

    hii craig,

    ur code is good.but iam confused as to what columns should city and countries have . can u just brief abt it?

  • 5. Re: a dependent poplist requirement
    CraigB Guru
    Currently Being Moderated

    Well, I don't know your table/database design, so I just made up some names.  You'll need to refer to your table to get the right names.  Basically, the Poplist Record Group requires the data to be in the following format:  "ID, DESCRIPTION".  The ID is stored value and the Description is what is displayed in the PopList set of values.  The ID and DESCRIPTION can be the same value - they do not have to be different.

     

    Craig...

  • 6. Re: a dependent poplist requirement
    990187 Newbie
    Currently Being Moderated

    hii craig,

    plz help me out . . .i have done the dependent poplist i this way

    i have created two tables country and city

    country                                      city

    cid cname cityid ,cityid         cityid,cityn,cid

     

    and a record group as  select cid,cityn from city

     

    and the  fired the triggers u specified above . . . .  and iam not getting the requirement.is my specification correct. . . .

  • 7. Re: a dependent poplist requirement
    CraigB Guru
    Currently Being Moderated

    I don't understand your table layout.  I don't see a dependency between the country and the city!  I'm not sure why you would have the CITYID in the country table twice and I don't understand why you have CID in both tables.  If I were designing these tables, I would have gone with something like the following:

    Country                            
    country_id    NUMBERPK 
    country_nameVARCHAR2(150)

     

    CITY
    city_idNUMBERPK
    city_nameVARCHAR2(150)

     

    COUNTRY_CITY_XREF
    country_idNUMBERPK/FK
    city_idNUMBERPK/FK

     

    The COUNTRY_CITY_XREF (Cross-reference) table ties your CITY to your COUNTRY.  This enables you to tie a city to more than one country - since is it reasonable to expect that more than one country could have a city with the same name.  You could make this relationship in the CITY or COUNTRY tables, but then you would have duplicate entries for either the COUNTRY or the CITY depending on where you made the relationship.  Using the cross-reference table eliminates the duplicates.

    Your Poplist Record Group queries would then be:

    Country:  SELECT country_id, country_name from country;
    City:  SELECT city_id, city_name
             FROM city a, country_city_xref b
            WHERE a.city_id = b.city_id
              AND b.country_id = :YOUR_BLOCK.COUNTRY_ID;

     

    Now, after you have selected a country, your When-List-Changed trigger will populate the CITY PopList with only cities that are in that country.

    If you have the dependency between your tables, then you shouldn't have to modify your table design.  The key is to play with your query in SQL Developer/TOAD/SQL*Plus until you get the results you want.  Then use this query to create your record group in forms.

     

    Craig...

  • 8. Re: a dependent poplist requirement
    990187 Newbie
    Currently Being Moderated

    Hii Craig,

              Nice logic but  i have a small doubt.how many record groups should i create? and u have specified :your_block . .  what table should i create for :your_block. and list changed trigger should be fired on country only right . . . . .

                                                                                                                         regards,

                                                                                                                       pridhvi raj

  • 9. Re: a dependent poplist requirement
    CraigB Guru
    Currently Being Moderated

    990187 wrote:

     

    ...what table should i create for :your_block. and list changed trigger should be fired on country only right . . . . .

    I'm a little confused by this question...you already have this data block created in your form.  What table will you save the Country and City ID's in?  That is the table you would base your Forms data block on.


    Craig...

  • 10. Re: a dependent poplist requirement
    990187 Newbie
    Currently Being Moderated
    Country                           
    country_id   NUMBERPK
    country_nameVARCHAR2(150)

     

    CITY
    city_idNUMBERPK
    city_nameVARCHAR2(150)

     

    COUNTRY_CITY_XREF
    country_idNUMBERPK/FK
    city_idNUMBERPK/FK

     

    hiiii craig,

        u said me 2 create these tables.after creating if i choose the country the city has to be be populated and i do not have cityname  in my country table . how can i do it?

     

    2)how many record groups should i create? PLZ REPLY

  • 11. Re: a dependent poplist requirement
    CraigB Guru
    Currently Being Moderated

    990187 wrote:

        u said me 2 create these tables.after creating if i choose the country the city has to be be populated and i do not have cityname  in my country table . how can i do it?

    Correct, you won't have the city name in your country table because the suggested tables are fully normalized.  You have to join the tables to get the city name.  For example; assuming your list of countries have the following IDs: INDIA = 1, PAKISTAN = 2, SRILANKA = 3.  To display a list of cities in INDIA your query would look like this:

     

     

    SELECT c.CITY_NAME
    FROM CITY c, COUNTRY_CITY_XREF ccx
    WHERE c.city_id = ccx.city_id
    AND ccx.country_id = 1;

    990187 wrote:

     

    2)how many record groups should i create? PLZ REPLY

    You're only going to have two record groups.  The first, country_rg, will be defined during design because you know what this list will be.  The second, city_rg, will be created during runtime (through code) because you don't know the values for the city_rg until a user has selected a country.  Assuming your Country and City Poplists are in a control block (a block not based on a table) named CONTROL, your code in the COUNTRY poplist When-List-Changed trigger will look like this:

     

     

    -- Sample When-List-Changed trigger
     DECLARE
      rg_name VARCHAR2(7) := 'city_rg';
      rg_id RECORDGROUP;
      list_id ITEM;
      v_sql VARCHAR2(1000);
      n_dummy NUMBER;
     BEGIN
      v_sql := 'SELECT c.city_id, c_city_name FROM city c, country_city_xref ccx WHERE c.city_id = ccx.city_id and ccx.country_id = :CONTROL.country_id';
      list_id := FIND_ITEM('CONTROL.city_id');
      rg_id := CREATE_GROUP_FROM_QUERY(rg_name, v_sql);
      --Clear the CITY_ID Poplist first...
      CLEAR_LIST(list_id);
      --Now, load the CIty Poplist with data
      POPULATE_LIST(list_id, rg_name);
     END;

     

    I hope this clears things up for you.

     

    Craig...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points