11 Replies Latest reply: Jul 12, 2013 9:12 AM by CraigB RSS

    a dependent poplist requirement

    990187
      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
          Aditya_ranjan
          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
            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

              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

                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

                  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

                    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

                      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

                        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

                          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
                            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

                              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...