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));"')

1 comment:

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