9 Replies Latest reply on Feb 27, 2009 5:13 PM by 515357

    Tricky: Column sort with Popup-key LOV

    614097
      Hi everyone,

      I guess this is a tricky one:
      Column sorting doesn't seem to work if you have a Popupkey LOV in your tabular form.

      See the following example:
      http://quickimg.com/uploads/f6388ab3680bdeac63cb8795a98d81fe.png

      (The yellow fields you see are form detail information displayed by the Firefox Extension Web Developer Toolbar, highly recommendable, by the way)

      The form element names now are in the right order (00, 01, 02, 03 ...).

      However, if you sort the table by Column 1 (Source) this order gets tumbled:
      http://quickimg.com/uploads/93b976c603132db84fc8881211617718.png

      That's quite some problem because the Key returned by the popup refers to the ROWNUM which does not correlate with the correct row anymore.

      Anyone got an idea how to workaround this problem?

      cheers,

      Michael
        • 1. Re: Tricky: Column sort with Popup-key LOV
          CJ Bell
          This example may be useful (cheers Denes Kubicek!) , its about sorting on a hidden column when the user executes a sort on a different column

          http://htmldb.oracle.com/pls/otn/f?p=31517:153:3037400840650629::NO

          you could maybe create a hidden sort column , with a substr of the url field and use this technique

          Chris
          • 2. Re: Tricky: Column sort with Popup-key LOV
            614097
            Chris,

            that's an intersting example!
            However, the problem here actually is slightly different:

            I don't even want to sort for the 2nd column ("Target"), But sorting for the first one messes up the popupkey_from_query, since the form element names are not getting adjusted to the new sort order.

            I hope I could explain the problem clearly enough.

            Michael
            • 3. Re: Tricky: Column sort with Popup-key LOV
              CJ Bell
              Sorry Michael , gone a bit over my head now , although I do struggle to visualize stuff if I can't see it in front of me !

              Chris
              • 4. Re: Tricky: Column sort with Popup-key LOV
                614097
                Sure.

                Check the two images which I've posted above. That should help you visualize.
                On the first one (without any sort) the name of the form elements is ascending and according to the rownum (i.e. f10_00, f10_01, f10_02...).

                After applying sort to the first column the form names are NOT according to the rownum anymore. Nevertheless, they should be, for only then the popup key passes back the selected value to the form element at a certain ROWNUM position (here to the row at number 24, instead of the row with name f10_24, which is at ROWNUM position 1 after the sort):
                    function passBack(x,y)
                    {
                        if (opener.document.forms["wwv_flow"].f11.length > 1)
                        {
                             opener.document.forms["wwv_flow"].f11[24].value = x;
                             opener.document.forms["wwv_flow"].f10[24].value = y;
                        }
                        else
                        {
                             opener.document.forms["wwv_flow"].f11.value = x;
                             opener.document.forms["wwv_flow"].f10.value = y;
                        }
                        close();
                    }
                The only way I see to solve that problem is to alter the builtin JavaScript in the LOV popup. Does anyone know where to find the source of this Javascript?

                Michael
                • 5. Re: Tricky: Column sort with Popup-key LOV
                  Denes Kubicek
                  Michael,

                  I don't think this kind of sorting is easy (cheers Chris) since you are using the apex_item
                  syntax. The only way I could see this working is to build your own sorting but please, don't
                  ask me to try that for you.

                  Denes Kubicek
                  -------------------------------------------------------------------
                  http://deneskubicek.blogspot.com/
                  http://www.opal-consulting.de/training
                  http://htmldb.oracle.com/pls/otn/f?p=31517:1
                  -------------------------------------------------------------------
                  • 6. Re: Tricky: Column sort with Popup-key LOV
                    614097
                    Denes,

                    Thanks for your reply.
                    I am not intending to write that on my own, and believe me, that wouldn't be something I would ask anyone in this forum to just try that for me ;)

                    Again, I think it is most straightforward to slightly adjust the JavaScript source code in the popup, since all the required information is already visible to the popup.

                    Do you know where I can access that popup source code?

                    Cheers,

                    Michael
                    • 7. Re: Tricky: Column sort with Popup-key LOV
                      Denes Kubicek
                      Michele,

                      Bingo! I already forgot I had a solution for that kind of problem too. Just put input type
                      hidden in front of your apex_item like in this code and you will be able to sort your
                      column on whatever you want.
                      SELECT    '<INPUT TYPE="HIDDEN" VALUE="'
                             || LPAD (ROWNUM, 4, '0')
                             || '" />'
                             || apex_item.text (4,
                                                TO_CHAR (sal),
                                                7,
                                                6,
                                                'style="text-align:right" ',
                                                'f04_' || ROWNUM,
                                                NULL
                                               ) sal
                        FROM emp
                      See it woking in this example:

                      http://htmldb.oracle.com/pls/otn/f?p=31517:160

                      So, here we go. Another topic for my blog ;)

                      Denes Kubicek
                      -------------------------------------------------------------------
                      http://deneskubicek.blogspot.com/
                      http://www.opal-consulting.de/training
                      http://htmldb.oracle.com/pls/otn/f?p=31517:1
                      -------------------------------------------------------------------
                      • 8. Re: Tricky: Column sort with Popup-key LOV
                        614097
                        Wow, Denes, that's very helpful for most APEX_ITEM elements.

                        Unfortunately, that's not the case for POPUPKEY or DATE_PICKER elements, for they still lose their correlation between row number and form element.

                        Actually, all that should have been done was change the JavaScript from
                                     opener.document.forms["wwv_flow"].f11[24].value = x;
                                     opener.document.forms["wwv_flow"].f10[24].value = y;
                        to
                                     opener.document.forms["wwv_flow"].getElementById("f11_" + 24).value = x;
                                     opener.document.forms["wwv_flow"].getElementById("f10_" + 24).value = y;
                        Thus, the pass back function would pass back the value not to the element at number 24, but to the form element with id attribute f10_24.

                        Maybe someone of the APEX developers sees that and fixes that for a next release.

                        However, cheers for your support here. I'm sure I can use that somewhere else!

                        Danke

                        Michael
                        • 9. Re: Tricky: Column sort with Popup-key LOV
                          515357
                          I reported this problem in the following threads. Carl acknowledged it was a bug. It looks like it will be fixed in 4.0.

                          Bug in POPUPKEY_FROM_QUERY when columns are sort-enabled
                          Re: Enhancement Request Thread : Post 3.1

                          I was able to use text input type, and create my own popup field. It seems to be working great. It was not hard to create my own popup.

                          It involves creating a hidden field and a text field. The hidden field will receive return value, and text will hold display value.
                          APEX_ITEM.HIDDEN(6, popup_value, 'id="P160_F06_#ROWNUM#"') || 
                          APEX_ITEM.TEXT  (7, popup_name, 15, 30, 'readonly="TRUE"; style="color:gray"', 'P160_F07_#ROWNUM#') ||
                                                     '<a hre="javascript:callPopup(''' || 'P160_F06_#ROWNUM#' || ''',''' ||
                                                     'P160_F07_#ROWNUM#' || ''',''' || 'P160' || ''')";>' ||
                                                     '<img src="/i/list_gray.gif" width="13" height="13" alt="Popup"></img></a>' AS popup
                          Please replace hre above with href. P160 has the tabular form. P125 is the popup page. The code for callpopup in HTML Header section of P160 is below:
                          <script language="JavaScript" type="text/javascript">
                            function callPopup (formItem1,formItem2,formItem3) {
                              var url;
                              url = 'f?p=&APP_ID.:POPUP:&APP_SESSION.::NO:125:P125_OPENER_ITEM1,P125_OPENER_ITEM2,P125_SOURCE_ID:' + formItem1 + ',' + formItem2 + ',' + formItem3;
                              w = open(url,"winLov","Scrollbars=1,resizable=1,width=500,height=600");
                              if (w.opener == null)
                                 w.opener = self;
                              w.focus();
                            }
                          </script>
                          You need to create 3 hidden items on P125. They are P125_OPENER_ITEM1, P125_OPENER_ITEM2, P125_SOURCE_ID.
                          P125 has javascript in HTML header as below:
                          <script language="JavaScript">
                             function passBack(passVal1, passVal2)
                             {
                                 var opener_item1;
                                 var opener_item2;
                          
                                 opener_item1 = document.getElementById("P125_OPENER_ITEM1").value;
                                 opener_item2 = document.getElementById("P125_OPENER_ITEM2").value;
                          
                                 opener.document.getElementById( opener_item1 ).value = passVal1;
                                 opener.document.getElementById( opener_item2 ).value = passVal2;
                          
                                 close();
                             }
                          </script>
                          I have made P125 dynamic and generic enough by using P125_SOURCE_ID. I use this to create SQL statements in a before header/region process. I do not pass SQL thru javascript due to security issue. With this, I am able to display multiple columns in popup compared to single column report you get with POPUPKEY API.
                          IF :P125_SOURCE_ID = 'P160' THEN
                             :P125_SQL := 'select .....'
                          END IF;
                          The report region uses this for Region Source (PL/SQL function body returning SQL query)
                          BEGIN
                              RETURN REPLACE(:P125_SQL);
                          END;
                          The SQL in P125 should use passback java script to return the values. Please replace hre with href below. For example,
                          SELECT '<a hre="javascript:passBack(' || CHR(39) || emp_id || CHR(39) || ',' ||
                                        CHR(39) || REPLACE(full_name, CHR(39), '\' || CHR(39) ) ||
                                        CHR(39) || ');">Select</a>' AS link_text
                          Thanks.

                          Ravi