Forum Stats

  • 3,751,615 Users
  • 2,250,379 Discussions
  • 7,867,484 Comments

Discussions

Tabular form - Different LOV for different rows

partlycloudy
partlycloudy Member Posts: 8,052 Silver Trophy
edited Aug 30, 2013 4:35AM in APEX Discussions
In a tabular form column, is it possible to have a select list whose values depend on some other column in the row? There doesn't appear to be a way to use #COL# notation to refer to current row values in the LOV query definition.

Ideas? Thanks

Best Answer

  • Sc0tt
    Sc0tt Member Posts: 1,708
    Accepted Answer
    Try the apex_util.savekey_num/vc2 and keyval_num/vc2 API's. Basically you call the savekey_num in your query and for each row it sets a package variable which can be retrieved by the keyval_num/vc2 call in your LOV query.

Answers

  • Sc0tt
    Sc0tt Member Posts: 1,708
    Accepted Answer
    Try the apex_util.savekey_num/vc2 and keyval_num/vc2 API's. Basically you call the savekey_num in your query and for each row it sets a package variable which can be retrieved by the keyval_num/vc2 call in your LOV query.
  • 744367
    744367 Member Posts: 200
    Hi,

    I think, you are aware of Manual Tabular Form.

    You may want to check this:-
    http://apex.oracle.com/pls/otn/f?p=31517:176:2252546497434138:::::
    744367
  • Sc0tt
    Sc0tt Member Posts: 1,708
    Depends on what he is looking for - that link shows cascading selects, and while a valid solution is not the type of problem I ran into.

    I had a tabular form where I needed to reference one of the column values as a bind variable in the LOV - it was about giving completely different choices in the LOV based on the first column (which is just a display text). You unfortunately cant say something like this in the LOV
    where some_column = #FIRSTCOL#
    Now you could always just concatenate in the value, but that will blow away the shared pool and that's never good. The savekey procedure allows a workaround to that. If you don't have cascading selects or something to "change" then your solution won't necessarily work.
  • partlycloudy
    partlycloudy Member Posts: 8,052 Silver Trophy
    Scott - My situation is exactly the same as yours i.e. I just need a different select list on each row, not cascading lists. I will try out your suggestion. Thanks
  • Sc0tt
    Sc0tt Member Posts: 1,708
    It's pretty easy but if you need a hand let me know and I can post some sample code for you.
  • 744367
    744367 Member Posts: 200
    Hi Scott,

    Thanks ..I formed some idea about APEX_UTIL.KEYVAL_NUM. I have a problem where I am not able to decide if I can make use of this package and solve..

    Can you please have a look at this thread.. 4222311

    I have a SQL which populates a tabular form on the page.Coulmn1 some text, Column 2 is to show a drop down as YES and NO. i am trying to freeze the option for rows 2,3,4 as NO, if the Option choosen in first is NO.

    So, what I am trying to ask is..can I save my first selection in a global variable and use it in where clause of [select list query] the second drop down..Not sure how I can do this..Appreciate if you can show me a direction.

    Thanks,
    Chaitu.
  • Binepal
    Binepal Member Posts: 335 Bronze Badge
    Sc0tt
    can you please give query example for APEX_UTIL.SAVEKEY_NUM and APEX_UTIL.KEYVAL_NUM how to use it
  • Binepal
    Binepal Member Posts: 335 Bronze Badge
    never mind i managed it, this is what i did hope it helps some one

    select
    "ID",
    "ID" ID_DISPLAY,
    "CITY_ID",
    (SELECT CITY_NAME FROM CITIES WHERE ID = CITY_ID) CITY_NAME,
    APEX_UTIL.SAVEKEY_NUM(CITY_ID) cid,
    "NETWORK_ID"
    from "#OWNER#"."M_LOCATIONS"

    then created a lov NETWORK

    select NETWORK_TYPE display_value, ID return_value
    from NETWORKS
    where city_id = APEX_UTIL.KEYVAL_NUM
    order by 1

    Then attached this lov to "NETWORK_ID"
  • 985780
    985780 Member Posts: 1
    Sorry I realize this is an old thread so maybe Apex 4.1 does things different but I was trying Binepal suggestion and so think this could be useful to others.

    I seem to only be able to get this to work if I change my tabular form to "Use Generic Column Names (parse query at runtime only) " and then I switch back to "to Use Query-Specific Column Names and Validate Query" . However then I lose the [row selector] column which I think is required because any updates i now try to make give me "Column must have a value" on any changes I submit.

    Any ideas on what I may be doing wrong?

    Thanks!

    Saul
  • Dip
    Dip Member Posts: 659 Bronze Badge

    Hello.

    I have exactly the same problem.

    It doesn't work until i change my tabular form to "Use Generic Column Names (parse query at runtime only) ".

    When i change back to "Use Query-Specific Column Names and Validate Query", it works. But when i after that change something in report attributes, it stops working.

    Did you solve the problem?

    Thanks.

    Regards,

    Dejan

This discussion has been closed.