11 Replies Latest reply: Nov 19, 2013 8:12 AM by Kanishkaa RSS

    Multiselect checkbox as cascaded item

    Kanishkaa

      Hi,

           I have a check box in which if i select more than one check box, based on that multi-select values, i wanted the next item values to get displayed.

       

      I have set up an example at http://apex.oracle.com/pls/apex/f?p=71510:14 (test/apex14gm)

      In this page, if i multi-select "place" check box i wanted the "departments"  to get displayed according to the above multi-select.

      If i do a single select in check box, i am getting the corresponding departments but i am not getting it while i select multiple check boxes.

       

      How can I achieve this ?

       

      Pls help me out...

       

      Thanks in advance

        • 1. Re: Multiselect checkbox as cascaded item
          Joni Vandenberghe

          Hi Kanishkaa,


          Ok I assume your department table is looking like this:

          id_city

          department

           

          So your query is

          SELECT ..

          FROM departmens

          WHERE id_city = : PX_MY_CHECKBOX

           

          The values of your checkbox, when you select multiple look like this within an APEX session:

          1:5:6

           

          So obviously your query will fail.

           

          First there is a way in oracle you can treat a comma separated list as a table. So we need to convert your string to that.

          So add a variable:

          v_MyVals := REPLACE(: PX_MY_CHECKBOX,':',',');

          This way you get a comma separeted list.

          Then you need to rewrite the where of your original query to

          IF v_MyVals IS NOT NULL THEN

          ...

          SELECT ..
          FROM ..

          WHERE id_city IN (SELECT TO_NUMBER(xt.column_value)

          FROM XMLTABLE(v_MyVals ) xt);

           

          END IF;

           

          Regards,

          Joni

          • 2. Re: Multiselect checkbox as cascaded item
            Kanishkaa

            Hi Joni,

                         I tried using like the code as you gave, but I dono where to place it exactly.

            Because,

            a)  the departments is again a checkbox so its asking for LOV values .

            b)  i wanted these details to get recorded in the database, so in the "source", database column has been used.

            c)  If i try creating a process after header also, it is not working.

             

            http://apex.oracle.com/pls/apex/f?p=71510:14 (workspace: test_me, username:test, password: apex14gm)

             

            Thanks

            • 3. Re: Multiselect checkbox as cascaded item
              Joni Vandenberghe

              Hi Kanishkaa,

               

              The reason you can't use my solution is because your data model is screwed up. I assume this is not how you really want it but quickly made some test version? It's better to make a solid and realistic datamodel, it doesn't take much time and at least you can develop in a realistic environment.

              Here is what is wrong with it:

               

              1. Your table "items_table" has no primary key

              2. If you put departmens together in the same column at least link them with a master detail foreign key and not a bit of text

              3. Its better not to put them together into one table, better is to have one departments and one places table linked with a foreign key

              If you changed this then you can return an id in your checkbox P14_PLACE instead of just plain text like now

              and then you can modify the query in P14_DEPARTMENTS to this:

               

              where id_place IN (SELECT TO_NUMBER(xt.column_value)

              FROM XMLTABLE(NVL(:P14_PLACE,-1)) xt)

               

              After that just add an onclick Dynamic action to submit P14_PLACE item and refresh the P14_DEPARTMENTS item

               

              Regards,

              Joni

              • 4. Re: Multiselect checkbox as cascaded item
                Kanishkaa

                Hi,

                I tried but it throws an error as, ORA-01722: invalid number ORA-01722: invalid number.

                 

                I am quite new to apex, so could not get you.

                Sorry for the trouble

                http://apex.oracle.com/pls/apex/f?p=71510:14 (workspace: test_me, username:test, password: apex14gm)

                 

                Pls help me out....

                • 5. Re: Multiselect checkbox as cascaded item
                  Joni Vandenberghe

                  Hi Kanishkaa,

                   

                  Yes it won't work and I told you why: your datamodel. Is there a reason why you are holding on so much on this datamodel? This is a really bad model, so unless it is already running in production I suggest you change it.

                   

                  Regards,

                  Joni

                  • 6. Re: Multiselect checkbox as cascaded item
                    Kanishkaa


                    yes Joni, its already in production.. now its a modification to be done.

                    is there any other way to achieve this??

                    instead of checkbox, a list manager or shuttle can be used for 1st item.?

                    any apex_util packages can be used or so ??

                    • 7. Re: Multiselect checkbox as cascaded item
                      backintheday

                      Kanishkaa,

                       

                      Joni makes an incredibly valid point regarding the data model you are using.  Is it possible to discuss with your colleagues the idea of adding a Primary Key to the ITEMS_TABLE?

                       

                      -- assuming your database is 11g and understanding that this is not tested, just from my memory

                      -- add new column

                      ALTER TABLE items_table ADD (item_id NUMBER);

                      -- create sequence

                      CREATE SEQUENCE seq_pk_items_table;

                      -- update new column with new keys, assuming no particular order

                      UPDATE items_table SET item_id = seq_pk_items_table.NEXTVAL;

                      COMMIT;

                      -- set new column as primary key

                      ALTER TABLE items_table ADD CONSTRAINT pk_items_table PRIMARY KEY (item_id); 

                      -- set a trigger for future updates

                      CREATE OR REPLACE TRIGGER pk_items_table_bi

                        BEFORE INSERT ON items_table FOR EACH ROW

                        BEGIN

                          :NEW.item_id := seq_pk_items_table.NEXTVAL;

                        END;

                      /

                       

                      Than you can establish a Foreign Key relationship as Joni as suggested.

                       

                      -Seth.

                      • 8. Re: Multiselect checkbox as cascaded item
                        Kanishkaa

                        Hi Joni,

                             I have created 2 tables (place_table, dept_table) linked with foreign key Joni as you said.

                        But then, now also its not working.

                        If I select one checkbox, 2nd item gets displayed correctly. But not working for multi-select.

                         

                        As you said, if I create dynamic actions for submitting and refreshing items, multi-select checking is not shown visible.

                        Each time if i check the box, its getting refreshed and works as a single select checkbox.

                         

                        http://apex.oracle.com/pls/apex/f?p=71510:14 (workspace: test_me, username:test, password: apex14gm)

                         

                        Thanks...

                        • 9. Re: Multiselect checkbox as cascaded item
                          backintheday

                          Kanishkaa,

                           

                          Is this similar to what you are trying to do (MultiSelectList tab)?

                           

                          http://apex.oracle.com/pls/apex/f?p=61075 ... demo/demo

                           

                          If so, have a look at this thread where I received help on how to do it:

                           

                          Multiple Select List does not Refresh with Dynamic Action

                           

                          -Seth.

                          • 10. Re: Multiselect checkbox as cascaded item
                            Joni Vandenberghe

                            Hi Kanishkaa,

                             

                            I made a copy of your application and made the folowing changes:

                            1. You made ID columns but you are not using them. I changed the :P14_PLACE item so it returns an id

                            2. I rewrote the query of your departments item to my original suggestion

                            3. Your dept item is returning the name, I suggest you return the id instead but this is a different matter

                             

                            Regards,

                            Joni

                            • 11. Re: Multiselect checkbox as cascaded item
                              Kanishkaa

                              Thank you soo muchh Joni