Tricky: Column sort with Popup-key LOV

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

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

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
-------------------------------------------------------------------

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
-------------------------------------------------------------------

I reported this problem in the following threads. Carl acknowledged it was a bug. It looks like it will be fixed in 4.0.

2730357
2823819

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