This discussion is archived
6 Replies Latest reply: Feb 11, 2013 12:46 AM by kvlek RSS

APEX_ITEM Cascading LOV/SELECT_LIST in APEX 4.2

988139 Newbie
Currently Being Moderated
Hello,

I have this requirement to create cascading LOVs but I'm creating the select_lists dynamically with APEX_ITEM in a PLSQL region. Can someone point me in the right direction as for how would I do that since I don't see how can it be archived.

I use this code to generate the select_lists
DECLARE
 CURSOR c_filter IS
 SELECT id_filter, filter_name, filter_query, filter_type FROM SEG_FILTER_TEST@dbexterna ORDER BY 1;
BEGIN
 htp.p('<table width="300">');
 FOR a IN c_filter LOOP
 htp.p('<tr><td style="padding="10px">' || a.filter_name || '</td>');
  IF a.filter_type = 'LIST' THEN
    htp.p('<td style="padding="10px">' ||
    apex_item.select_list_from_query(
        2
      , '%'
      , a.filter_query
       , 'style=""'
       , 'YES'
       , '%'
       , ' '
       , null
       , 'label')
       || '</td></tr>'    
    ); 
  ELSIF a.filter_type = 'DATEPICKER' THEN
    htp.p('<td style="padding="10px">' ||
    apex_item.date_popup2(
      2,
      null,
      'DD-MON-RR',
      12,
      12,
      NULL,
      a.id_filter
   )
      || '</td></tr>' 
   ); 
  END IF;
 END LOOP;
END;
{code}
It generates 4 select lists. And each one of them should cascade according to the one created before them.

Anyone has an idea of how can I archive this?

Thanks in advance.

Edited by: MrCapuchino on Feb 7, 2013 1:35 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 1. Re: APEX_ITEM Cascading LOV/SELECT_LIST in APEX 4.2
    kvlek Journeyer
    Currently Being Moderated
    Hi,

    There is an undocumented feature which can probable help you. In the attribute part of the item you create you can add the following
    'onchange="f_set_casc_sel_list_item_port(this,f2_'||LPAD (seq_id, 4,'0')||')"'
    In your loop (not the if else construction) you set every item id to 2, this is not correct the id should be unique. This id (the one you refer to) you have to use in the "f2_" part in the code above.

    Kees Vlek
  • 2. Re: APEX_ITEM Cascading LOV/SELECT_LIST in APEX 4.2
    988139 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your help that is useful information, the truth is that I don't understand the ID part, I mean I want to put a unique Id but how do I put it, I think you can only put numbers that will become F01, F02 and so on, how do you put an specific id? what is LDAP?

    I think what you wrote is what I want to do but I still don't quiet understand how to give the select list ids and how to grab their values through their id.

    Thanks in advance.
  • 3. Re: APEX_ITEM Cascading LOV/SELECT_LIST in APEX 4.2
    Tom Petrus Expert
    Currently Being Moderated
    Documentation:
    APEX_ITEM.SELECT_LIST_FROM_QUERY(
        p_idx           IN    NUMBER,
        p_value         IN    VARCHAR2 DEFAULT NULL,
        p_query         IN    VARCHAR2,
        p_attributes    IN    VARCHAR2 DEFAULT NULL,
        p_show_null     IN    VARCHAR2 DEFAULT 'YES',
        p_null_value    IN    VARCHAR2 DEFAULT '%NULL%',
        p_null_text     IN    VARCHAR2 DEFAULT '%',
        p_item_id       IN    VARCHAR2 DEFAULT NULL,
        p_item_label    IN    VARCHAR2 DEFAULT NULL,
        p_show_extra    IN    VARCHAR2 DEFAULT 'YES')
        RETURN VARCHAR2;
    Sometimes it is a good idea to provide named parameters to functions you call instead of by position. If you don't use a function regulary and has lots of parameters (which you then all fill out even though they have defaults) that might help.
    I say this because there is a difference between the p_idx and p_item_id parameters. p_idx provides the value for which f## array is used, and p_item_id is the html element id.
    Personally i would prefer my code to contain this, which allows me to quickly identify which parameter is what again when i next have to look at it. Otherwise you'll just have to open up the API documentation again.
       apex_item.select_list_from_query(
            p_idx        => 2
          , p_value      => '%'
          , p_query      => a.filter_query
          , p_attributes => 'style=""'
          , p_show_null  => 'YES'
          , p_null_value => '%'
          , p_null_text  => ' '
          , p_item_id    => null
          , p_item_label => 'label')
    LPAD documentation: http://www.techonthenet.com/oracle/functions/lpad.php
    In Oracle/PLSQL, the lpad function pads the left-side of a string with a specific set of characters (when string1 is not null).
    So taking what Kees said in mind, you'll have to provide a value to p_item_id and p_attributes. You do not have a seq_id though, but you can fix that by declaring a counter variable just for this.
    DECLARE
       l_counter BINARY_INTEGER := 1;
    BEGIN
    FOR a IN c_filter 
    LOOP
    ...
       apex_item.select_list_from_query(
            p_idx        => 2
          , p_value      => '%'
          , p_query      => a.filter_query
          , p_attributes => 'style="" onchange="f_set_casc_sel_list_item_port(this,f2_'||LPAD (l_counter, 4,'0')||')"'
          , p_show_null  => 'YES'
          , p_null_value => '%'
          , p_null_text  => ' '
          , p_item_id    => 'f2_'||l_counter
          , p_item_label => 'label');
    ...
    l_counter := l_counter + 1;
    END LOOP;
  • 4. Re: APEX_ITEM Cascading LOV/SELECT_LIST in APEX 4.2
    988139 Newbie
    Currently Being Moderated
    Hi Tom,

    Thank you for your help I was getting very frustrated but still I don't quiet understand everything.

    What does the onchange function do?

    How do I get the value of the first select list to put it in the condition of the query of the second select list.

    For instance my first select list will have this query:

    SELECT country_name, country_id FROM countries

    the second one should have

    SELECT state_name, state_id FROM states WHERE country_id = (What should I put here?)

    THanks in advance


    Thanks in advance
  • 5. Re: APEX_ITEM Cascading LOV/SELECT_LIST in APEX 4.2
    988139 Newbie
    Currently Being Moderated
    Hey,

    Please someone help me with that I think I'm very close

    Thanks!
  • 6. Re: APEX_ITEM Cascading LOV/SELECT_LIST in APEX 4.2
    kvlek Journeyer
    Currently Being Moderated
    Hi,

    The onchange is an javascript event which fires when the item value changes.
    In you case you should use in p_attributes item the counter value from first select list.
    The f2_ id should be the ID of that very same item.

    May be you can google on f_set_casc_sel_list_item_port that way you probably will find some examples.
    as said it is a "undocumented feature"

    Kees.

    Edited by: kvlek on 11-feb-2013 9:33

Legend

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