Mostly ORACLE APEX and Windsurfing stuff

Tuesday, 24 July 2012

Apex Item Select List Alternative


I've had a couple of projects now where there has been a requirement to produce a select list matrix to update results for people attending a course or event.  These have proved very popular with the users, but are a bit slow to run when there are large volumes of data involved, so I’ve been looking at a few different ways to speed things up.  The solution I’ve come with is a bit ‘off the wall’ but appears to do the trick nicely.  Let me know what you think!

This uses the standard APEX_ITEM.SELECT_LIST_FROM_LOV(1,null,'RESULT') to generate the Select Lists.  As you can see from the footer the region takes around 0.6 seconds for a dataset of 60 rows and 12 columns.  Scale this up a bit and it takes significantly longer.

This is a copy of the previous example, but instead of using the APEX_ITEM.SELECT_LIST_FROM_LOV I’m using the APEX_ITEM.TEXT function.  The region now takes < 0.1 seconds to generate, which is a significant improvement on performance.  So how do I convert the APEX_ITEM.TEXT into a Select List.  Well it’s “smoke and mirrors” and a bit of javascript/JQuery.  Here’s the code:

Function and Global Variable Declaration:

var gThis

function selectList(pThis) {
    gThis = pThis;
    var sel = $("#select");
    var pos = pThis.offset();
    var lTop = pos.top + 20;
    sel.attr('style', 'position:absolute;left:' + pos.left + 'px;top:' + lTop + 'px;border:2px groove;background:#ffffff;width:70px;');
    sel.show();
}

function selectClose(pVal) {
    gThis.val(pVal);
    $("#select").hide();
    disp_note(gThis);
}

// function to close select list
// when the user clicks off the div
$(document).mouseup(function (e) {
    var container = $("#select");
    if (container.has(e.target).length === 0) {
        container.hide();
    }
});

HTML Header:
<style type="text/css">

td.menuon {
  background-color: #000066;
  color: #FFFFFF;cursor:default;
}

td.menuoff {
  background-color: #FFFFFF;
  color: #000000;
}

#select {
  position:absolute;
  display:none;
}

.selectList {
  cursor:default;
  background-image:url(#WORKSPACE_IMAGES#lov.gif);
  background-repeat:no-repeat;
  background-position:right center;
}

</style>

<div id="select">
<table border="0" margin="0">
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Distinction');">Distinction</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Merit');">Merit</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Pass');">Pass</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Fail');">Fail</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Incomplete');">Incomplete</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" width="70px" onClick="selectClose('');"> </td></tr>
</table>
</div>


Apex Item used in sql query:

APEX_ITEM.TEXT(1,null,12,12,'readonly class="selectList" onclick="selectList($(this));"')

3 comments:

Anonymous said...

Nice example. Works very good. Now I have to find out how I save a different value in the database, i.e. to show the value "Germany" but to save "DE".
Thank you.
Helmut

Rabin said...

How about apex 5.0.4 with classic report to particular column Add to Cart ?

shunt said...

Hi Robin, can you add some more details.