This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jul 22, 2013 12:18 AM by joels RSS

Set Cascading LOV Item using Dynamic Action

joels Newbie
Currently Being Moderated

Hi all,

 

I have the following page item:

P1_USERNAME

P1_COMPANY  (LOV)

P1_DIVISION (LOV)

P1_DEPT (LOV)

 

Item P1_COMPANY, P1_DIVISION, and P1_DEPT are cascading LOV on my page.

I also have a table that contains the user and his/her company, division, and department.

 

So I have a dynamic action that when P1_USERNAME is entered. The dynamic action will pull the COMPANY, DIVISION, and DEPT information from the table and populate the page item accordingly.

I got the dynamic action working, I can confirm this by checking the Session State of the items.

BUT I cannot get the LOVs to display the selected value from the session state. They always reset back to "- Please Select -", which is my display value when NULL.

 

For example:

I have a record like in the table:

Username :alex

Company : Finance Inc.

Division : Admin

Dept : HR

 

When I entered the username into P1_USERNAME, this is what I see in the screen.

Username :alex

Company : - Please Select -

Division : - Please Select -

Dept : - Please Select -

 

While the session states of the items have been set correctly as per the record in the database.

 

Has anyone experienced this before?

I think it's because the parent LOV (company), reset the child LOVs (division and dept), when the dynamic action kicks in.

 

Thanks.

  • 1. Re: Set Cascading LOV Item using Dynamic Action
    jrimblas Expert
    Currently Being Moderated

    Basically Session State is what's in stored back int he DB and what the screen displays is client side.

    As you figure out your values you need to put them on the page and that requires javascript.

    Try using the APEX js API $s to set the values.

    $s("P1_COMPANY", "new value")

     

    You basically want to fetch back Company, Div and Dept when you enter a user, right?

    So, thinking about it, if you use the Set Value actions in the DA, those should populate the drop downs correctly.

     

    Your DA should be on Change of P1_USERNAME

    Then you need ONE Set Value true action for each your drop downs (P1_COMPNY, P1_DIVISION,P1_DEPT).  Use something like SQL Statement or PL/SQL Function to populate them and use "Page Items to Submit" field in the Set Value so that info is available to the SQL Statement or PL/SQL

     

    Hope this all makes sense.

    If not, perhaps an example on apex.oracle.com would be ideal and then we can troubleshoot it from there.

     

    Thanks

    -Jorge

    http://rimblas.com/blog/

  • 2. Re: Set Cascading LOV Item using Dynamic Action
    joels Newbie
    Currently Being Moderated

    Hi Jorge,

     

    Thank you for taking the time to reply me.

     

    What you said make sense for normal LOV, but with Cascading LOV when the page load. the child LOVs (DIVISION & DEPT) are empty, so the SET VALUE action doesn't have any effect on the page because it can't find the value option.

     

    When i said empty i mean, there's no option:

    {code}

    <select id="P12_TO_FLOOR_ID" class="selectlist" size="1" name="p_t14">

     

     

    <option selected="selected" value="">- Please Select -</option>

     

    </select>

    {code}


    I think what I need is how to trigger the child LOVs to refresh it's options when I use SET VALUE action on the parent LOV.


    BTW, here's my DA actions:


    1. EXECUTE PLSQL: to get the company, division, and dept from the table

    2. SET VALUE: COMPANY

    3. SET VALUE: DIV

    4. SET VALUE: DEPT


    Thanks.

    -Joel

  • 3. Re: Set Cascading LOV Item using Dynamic Action
    jrimblas Expert
    Currently Being Moderated

    Ok, I'm pretty sure we can make this work. One way or another...

     

    So, I'm thinking that your LOVs, at least the first one (COMPANY?) is based on the value of USERNAME?  Is this correct?

    Before we try something overly complicated, have you just tried a DA Refresh of the items? That would populate them and then you can set the values.

     

    You may need to perform a "Execute PL/SQL Code" step with just "null;" in the code but you pass the driving field in the "Page Items to Submit" so that the value is set in Session State and the refresh has access the correct value to run the LOV SQL.  Hope this makes sense.

     

    Thanks

    -Jorge

  • 4. Re: Set Cascading LOV Item using Dynamic Action
    joels Newbie
    Currently Being Moderated

    Hi Jorge,

     

    I have created the sample application at http://apex.oracle.com/pls/apex/f?p=71244:1

    WORKSPACE : YOELORACLE
    USERNAME: GUESTDEV

    PASSWORD : jrimblas

     

    My apex version is still 4.0.1, so it might not have some the option available in apex.oracle.com

    To clear the page item cache. you can press the "Start Over" button on the page.

     

    Thanks for taking a look.

     

    Joel

  • 5. Re: Set Cascading LOV Item using Dynamic Action
    joels Newbie
    Currently Being Moderated

    I have tried the refresh action. I actually found this thread https://forums.oracle.com/thread/1132537 and gave Patrick suggestion a try.

    And using the SET VALUE action still doesn't work so I change the set value action to "Execute Javascript Code" action

     

    $("#P1_COMPANY").val('1');  --- Hardcoding the value first for testing.

     

    I did the above for DIVISION and DEPT too, with relevant value of course.

     

    Now my true actions are:

    1. Execute PLSQL -- to get the data from table and set the value into the page item.

    2. Execute javascript : set company lov --> this one works because company lov is always populated (parent lov)

    3. Refresh DIVISION LOV -- this one works, the lov gets populated

    4. Execute javascript : set value for division lov --> doesn't work UNLESS i put some delay. So i have to use setTimeout javascript function.

     

    setTimeout(function (){

     

    $("#P1_DIVISION").val('2');

     

             }, 100);

     

    5. Refresh DEPT LOV --> doesn't work

    6. Execute javascript : set value for dept lov -- > doesn't work too eventhough i put the same delay.

     

    Hope what i wrote is clear to you.

  • 6. Re: Set Cascading LOV Item using Dynamic Action
    jrimblas Expert
    Currently Being Moderated

    Ok, interesting problem.  I poked around a little but it's eluding me. I'll take a look in the morning.

  • 7. Re: Set Cascading LOV Item using Dynamic Action
    jrimblas Expert
    Currently Being Moderated

    Hi Joel,

    Sorry I couldn't get to this before.  This is an interesting problem. As you've seen is a timing problem.  You change the parent and that changes the child LOV THEN you can set the value on that LOV. So you have this multiple cascading thing going on.

    First thing I would say is, could the page simply submit when you change the first value?  That would solve all this as the page would load with the new values that you retrieved from Session State and with the fully populated LOVs.  So something to ponder....

     

    If you need it to be an AJAX call, then what I've done work on Firefox, but not in Chrome.  Which baffles me.

    So... you may need to serialize the refresh of the LOV and setting the value by coding the cascading yourself.  Before DA, the cascading was coded by hand with a code structure like the one here: http://apex.oracle.com/pls/otn/f?p=11933:37

    Now, this is old code and today you don't need to create a TEMPORARY_ITEM. What you do is that instead of creating a global item and calling get.add('TEMPORARY_ITEM',pThis.value); you just call

    get.addAparam('x01',pThis.value); There are 10 x parameters (x01,x02,x03).  THen in your PL/SQL process, instead of :TEMPORARY_ITEM you simply reference apex_application.g_x01

     

    Hope this makes sense.

     

    I may think about it some more if I have time.

    Thanks

    -Jorge

     

    PS.I don't like the timer solution, it's just not right, but if you must... 

  • 8. Re: Set Cascading LOV Item using Dynamic Action
    joels Newbie
    Currently Being Moderated

    Hi Jorge,

     

    Thank for taking the time to try solving this for me.

    I might be going with doing the cascading code my self but I am still hoping for a simpler approach

     

    I don't like the timer solution myself because i think it's unreliable .

     

    Thanks again!

    -Joel

  • 9. Re: Set Cascading LOV Item using Dynamic Action
    jrimblas Expert
    Currently Being Moderated

    Joel, this is frustrating, not sure what we're missing.  It still a timing problem.  We need everything serialized.

    I tried a version 2 (page 2) where the LOV are not cascading just to troubleshoot the DA.

    It's still not quite there.

     

    I'm starting to suspect you're going to need the code.

     

    There is the page submit option too. Take a look at p3

     

    We basically set the values Before the submit so that when the page renders again it's all ready to display.  I didn't turn on the Cascade again on this page, but you'll want it probably, and that's ok.

    https://apex.oracle.com/pls/apex/f?p=71244:3

     

    What do you think?

     

    Thanks

    -Jorge

  • 10. Re: Set Cascading LOV Item using Dynamic Action
    joels Newbie
    Currently Being Moderated

    Hi Jorge,

     

    I've looked at your page 2, though it's not cascading you didn't remove the variable from the LOV query, so the LOV is always populated (no cascading).

    I think if you do that,it'll work. But this is not what I am looking for

     

    Page 3 with submit can work but I really don't like it.

    This feature that I am trying to get it working it's just a small part of the information on the entire page in my actual application.

    So having to submit the page for this is not so elegant .

    Sorry I am not trying to be difficult here.


    I was wondering if the the "Wait For Result" in the DA action is a way to serialized the DA action.

    I tried it and it's still the same..

     

    I guess the only way it's coding your own AJAX and javascript.

     

    -Joel

  • 11. Re: Set Cascading LOV Item using Dynamic Action
    jrimblas Expert
    Currently Being Moderated

    joels wrote:

     

    Page 3 with submit can work but I really don't like it.

    This feature that I am trying to get it working it's just a small part of the information on the entire page in my actual application.

    So having to submit the page for this is not so elegant .

    Sorry I am not trying to be difficult here.

     

    Nah, perfectly okay, it makes sense. I suspected that was the case.

     

    I guess the only way it's coding your own AJAX and javascript.

     

    It's looking like it. 

     

    dmcghan do you have any tips here? I wonder if I'm just missing something obvious.

     

    Thanks

    -Jorge

  • 12. Re: Set Cascading LOV Item using Dynamic Action
    Tom Petrus Expert
    Currently Being Moderated

    I've just taken a look at your sample page and don't think this issue is actually too complicated to solve. (Actually, it was I who was mucking around in page 1, sorry for that. I was looking at it and then had to go. Picked it up again later on and just made a copy this time)

     

     

    You change the employee. The dynamic action fires, executes plsql and sets the values in the company2/division2/dept2 fields

    Company 1 is a cascading lov. Empo has changed? Company refreshes. Cascading for division and dept as well.

     

     

    You can see this happening in sequence as well when looking at the page. Select emp, it'll hang for a second. You then get the select list refresh icons.

    The sequence in which this happens is of course a bit wrong. You set the values for the items on the client side first, but then their refresh is kicked in, and so the value setting is simply lost again.

     

     

    What makes this annoying is how P1_EMPNO is a cascading lov parent of P1_COMPANY. Changing P1_EMPNO will refresh the lovs beneath it. However, you can't simply set their values after you've retrieved the user's company/division/dept, because each lov is dependent on the one above it. Eg you change the employee. The company lov is refreshed. Because the it is refreshed, the division lov will refresh. Now you can set the value of the company lov, but not that of the division one. Why is this? The lov for division requires the value of company in the session state. If you'd set the value for company after it has finished refreshing, it will already be too late for division to pick up the value set. .

     

     

    I made a copy of your page to page 5, “tom”.

    The first I did was removing the cascade from the company lov. You don’t need this. There are simply no parent lovs for this lov, as is evident from the sql for it. The companies always remain the same companies even when you select another person; it is not dependent on another item for its values.

    Then the timing issues come in play, and with them there is also the issue of the session state.

    So now companies always show. You pick a user, it runs the PLSQL block and put values in the P5_xxx2 items. P5_COMPANY can be set now, and this is in the set value of the dynamic action. By not suppressing the change event, a refresh will be triggered on its cascading lov, P5_DIVISION.

    Refreshing P5_DIVISION will only get the new lov values (constrained within the chosen company) for this item though, and not set the value. We already have the values in P5_DIVISION2 however, and thus can use the “After Refresh” dynamic action on P5_DIVISION to do this. In short: company gets set, divisions refresh to show the correct divisions, and then the selected value is set. Again, by not suppressing the change event on this item the depending lov on DEPT will be refreshed.

    Then by using another “After Refresh” dynamic action on P5_DEPT we can set the selected value once more.

    Also important is that I changed P5_DIVISION to submit P5_COMPANY when it is refreshed, and P5_DIVISION when P5_DEPT is refreshed. This makes sure that the correct set of values is retrieved and probably why you were struggling aswell. After company has changed and division has refreshed, the value on division is set. But the correct divisions are shown only because the chosen company has been set in the session state.

    The same for division - dept. After the refresh of division, the chosen value for division is set. This change will trigger the refresh on dept, and because dept has the division in its page items to submit list, the correct values will be retrieved.

    Take note that the “debug items” are required in this solution though. Just set them as hidden and unprotect their value. Your goal should be to reduce the amount of roundtrips to the server. Why do a plsql (ajax) call just to retrieve a session state, when the value is already on the page and all in one action? Three hidden items is just so much easier than 3 extra ajax calls, the tradeoff is plenty.

     

     

    There is one more noteworthy point, and that is the fact that dept is being retrieved twice. That makes sense because of the cascading lovs. Initially company is changed, and this will refresh division and this in turn will trigger a refresh on dept. At this point the lov for dept will be empty, because division will be empty. Because we set the value of division again after it has refreshed, this will trigger a second refresh on dept, though now the correct list of values is retrieved because division has a value set. Not that big an issue, but good to point out. If you’d have another cascading lov beneath dept, it would refresh three times.

  • 13. Re: Set Cascading LOV Item using Dynamic Action
    jrimblas Expert
    Currently Being Moderated

    Nicely done Tom.  For some reason I just didn't think about the afterrefresh event.  That was the key here I think.  I knew we needed the placeholder elements COMPANY2, DEPT2, ... to hold on to the values while the dropdowns are being refreshed, so Joel I don't think those will be optional in the solution.

     

    Glad you cracked this.

    Thanks

    -Jorge

  • 14. Re: Set Cascading LOV Item using Dynamic Action
    joels Newbie
    Currently Being Moderated

    Yes, nicely done Tom. I wouldn't think of trying it that way.

    Thank you so much for getting this solved for me.

     

    Awesome! I am going to have a good day on Monday.

1 2 Previous Next

Legend

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