10 Replies Latest reply: Apr 30, 2013 9:38 AM by Patrick Wolf-Oracle RSS

    Cascading LOV - Keep Existing Value

    LT.ora
      Need to stop Cascading LOV to reset the values and keep existing selected options IF VALID
      please have a look at the scenario
      Col-1     Col-2     Col-3
      A     AA     1
      A     BB     2
      A     CC     3
      B     AA     1
      B     AAB     2
      B     AAV     3
      When Col-1 value is A the LOV of Col-2 has values AA BB CC and Col-3 has 1 2 3
      and if Col-2 value is B the LOV of Col-2 has values AA AAB AAV and Col-3 has 1 2 3

      What is happening :
      » when user changes Col-1 from A to B Col-2 & Col-3 resets the user selected value (I think the default behavior)

      what is needed if user has selected "3" in Col-3 and changes Col-1 from A to B Col-2 & Col-3 should have user selected values IF VALID
        • 1. Re: Cascading LOV - Keep Existing Value
          Howard (... in Training)
          Hello,

          Yes. cascading LOVs are designed to be dependent. For example, if one were selecting district (LOV1), city (LOV2) and street (LOV3) then one would want city and street to reset when the user selected a new district. It would seem the only way to have them NOT reset would be to make them independent -- not cascading -- LOVs.

          Here's what I'm thinking. Make them independent LOVs and write you own validations which reset Col-2 and Col-3 when their values go invalid for the choice in Col-1. And reset Col-3 when it's choice is invalid for Col-2.

          That might be easy or difficult depending on the complexity of your validations. Do you know how to link validations to changes in the LOV?

          Regards,
          Howard
          • 2. Re: Cascading LOV - Keep Existing Value
            LT.ora
            Thanks Howard,

            It would be a greate help of you help me with how to link validations to changes in the LOV,
            But still is there really no way to Keep Existing Value if valid ?
            • 3. Re: Cascading LOV - Keep Existing Value
              Denes Kubicek
              This example shows how to do that:

              https://apex.oracle.com/pls/apex/f?p=31517:292

              Denes Kubicek
              -------------------------------------------------------------------
              http://deneskubicek.blogspot.com/
              http://www.apress.com/9781430235125
              http://apex.oracle.com/pls/apex/f?p=31517:1
              http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
              -------------------------------------------------------------------
              • 4. Re: Cascading LOV - Keep Existing Value
                LT.ora
                You Made It Happen!

                That's Exactly What I Wanted... Exactly That
                • 5. Re: Cascading LOV - Keep Existing Value
                  Denes Kubicek
                  Yes, just to show it works. In your case, the second dynamic action has to be something like this:
                  DECLARE
                     v_empno   NUMBER;
                  BEGIN
                     SELECT empno
                       INTO v_empno
                       FROM emp
                      WHERE deptno = :p292_deptno AND empno = :p292_empno;
                  
                     RETURN v_empno;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        RETURN v_empno;
                  END;
                  since it needs to check if the value is in the range of alowed values. In my example I created a fake second entry which has the same ID and there it is enough to do RETURN item value session state.

                  Denes Kubicek
                  -------------------------------------------------------------------
                  http://deneskubicek.blogspot.com/
                  http://www.apress.com/9781430235125
                  http://apex.oracle.com/pls/apex/f?p=31517:1
                  http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
                  -------------------------------------------------------------------
                  • 6. Re: Cascading LOV - Keep Existing Value
                    LT.ora
                    Thanks Denes Kubicek

                    Really interesting,
                    Could you guide me the step to do this please...

                    I do have further queries but for that Queries I need to upload the stuffs on apex.oracle.com
                    I'll do that and get back to you shortly

                    Edited by: LTaura on Apr 29, 2013 6:15 PM
                    • 7. Re: Cascading LOV - Keep Existing Value
                      LT.ora
                      how would the DISPLAY_VALUE, RETURN_VALUE work here?
                      • 8. Re: Cascading LOV - Keep Existing Value
                        Patrick Wolf-Oracle
                        Hi Linto,

                        I think the dynamic action plug-in "Restores values for cascading LOVs" http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/skillbuilders-save-value_92.html
                        does exactly what you are looking for without having to do any additional development.

                        Regards
                        Patrick
                        -----------
                        Member of the APEX development team
                        My Blog: http://www.inside-oracle-apex.com
                        APEX Plug-Ins: http://apex.oracle.com/plugins
                        Twitter: http://www.twitter.com/patrickwolf
                        • 9. Re: Cascading LOV - Keep Existing Value
                          Denes Kubicek
                          Patric,

                          Are you sure? I had a look at the demo here:

                          http://apex.oracle.com/pls/apex/f?p=46685:SAVE_VALUE_ON_CASCADE:0:::::

                          and couldn't see it is doing that. Maybe an upgrade issue (again)?

                          It also says:

                          "The SkillBuilders Save Value on Cascade plug-in was designed to preserve the selected value of child item if the same value exists after refreshing."

                          and also, it shows orphaned values in the shuttle on the right side if you change and press F5.

                          Denes Kubicek
                          -------------------------------------------------------------------
                          http://deneskubicek.blogspot.com/
                          http://www.apress.com/9781430235125
                          http://apex.oracle.com/pls/apex/f?p=31517:1
                          http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
                          -------------------------------------------------------------------
                          • 10. Re: Cascading LOV - Keep Existing Value
                            Patrick Wolf-Oracle
                            Hi Denes,

                            I only remembered that this plug-in exists, but I haven't tested it myself. But I'm pretty sure Dan McGhan has an updated version if it really doesn't work with 4.2.

                            Regards
                            Patrick
                            -----------
                            Member of the APEX development team
                            My Blog: http://www.inside-oracle-apex.com
                            APEX Plug-Ins: http://apex.oracle.com/plugins
                            Twitter: http://www.twitter.com/patrickwolf