This discussion is archived
11 Replies Latest reply: Nov 19, 2013 6:12 AM by Kanishkaa RSS

Multiselect checkbox as cascaded item

Kanishkaa Newbie
Currently Being Moderated

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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated


    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Thank you soo muchh Joni

Legend

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