dinsdag 27 november 2012

AJAX inside a tabular form

Ok, this I found to be a nice APEX puzzle. Consider the following use-case:


An order input form is considered. It is a tabular form containing two functionally related colums. One is a selectlist, listing available articles, the other is showing the default price of the item, as defined in a lookup table.

The goal is, when the user selects an article, the price is updated via an AJAX call, hence without a complete page refresh. Sounds like a fair requirement!


here you'll find a working demo.


The problem is, that one cannot simply define a dynamic action solving the above(that is, I could'nt think of one). To solve it however, it is really a matter of identifying the source element on the page (the article) and the target element (the price) which should be updated. When working on a `static' page this is not too hard, but a tabular form should be considered more dynamic.

In APEX tabular-form input fields have the following format:

<input type="text" name="f04" size="16" 
       maxlength="2000" value="30"  id="f04_0001" autocomplete="off">

One could imagine a tabular form to be like a mathematical matrix, that is an ordered object with rows and colums that can be numbered in a countable fashion. On the DOM the columns are identified by the name attribute, and the rows are numbered in order of appearance. The element of interest is, ofcourse, identified by the value of the id attribute and is situated at the intersection of row and column. In the above example id = f04_0001, hence column 4 row 1. The id attribute is exactly in this format:

 fxx_yyyy, that is column_row
Now that we can identify the source element, we should identify the target element. Remeber that like the source element, the target element is also of the format fxx_yyyyy, where at most the x's differ in value. What we can do is add the html attribute class with value `price' (report attributes > column attributes) to the elements concerned, giving means to determine the column value fxx of the target element.

<input type="text" name="f06" size="16" class="price"
       maxlength="2000" value="30"  id="f06_0001" autocomplete="off">

Next, onchange of the source element, we want to update the target element. This is easy. Go to: Column attributes > element attributes of the atricle field in the tabular form and type:

onchange="updatePrice(this.id)"

So, onchange we call the JavaScript function updatePrice with argument this.id. `this' is the object in context, hence, here the element that is changed. Now we almost have all the ingredients we need. Next is defining an AJAX call, taking the id as input, and returning the price. In APEX we define two objects. The first is application item g_art_id, the second is an application process getPrice.


getPrice has the following listing:
declare 
 l_prijs artikelen.prijs%type;
begin
 select 
       art.prijs into l_prijs 
 from  artikelen art 
 where art.id = :g_art_id;
 --
 sys.htp.p( l_prijs );
exception when no_data_found then
 sys.htp.p( 'Article not found' );
end;

Please observe that the name of the application process is case sensitive and do take care of trailing blanks... The following JavaScript should be placed in the header of the page:
<script type="text/javascript">
//
// updatePrice takes a source_id (article), executes an AJAX call and 
// updates the DOM.
//
function updatePrice(source_id)
{
//
var c_target_class = 'price';
var l_target_id    = getTargetRowId(source_id, c_target_class);
var l_art_id       = getValue(source_id); 
//
setPrice(l_art_id, l_target_id);
//
return null;
};
//
// getTargetRowId determines the target_row_id 
//
function getTargetRowId(source_row_id, target_class){
//
var l_name;
var l_regexp;
var l_targetrowid;
//
// given the class attribute, determine which fxx column to use
// Result of the query below is the name of the inputfield in 
// the target class
//
l_name        = $('.' + target_class).first().attr('name');
//
// source_row_id has the format fxx_yyyyy, where xx refers to the column.
// The target_id is computed by replacing the xx with the relevant value.
// We use a regular expression
//
l_regexp      = /f[0-9]+_/;
l_targetrowid = source_row_id.replace(l_regexp, l_name + '_');
//
// Result is the id of the target input-field
//
return l_targetrowid;
//
};
//
function getValue(id){
return $('#' + id ).val();
};
//
// setPrice takes a primary key value of an article
// and determines the price via an AJAX call.
//
// Second argument is the id on the DOM of the inputfield which should 
// be updated.
//
function setPrice(art_id, target_el_id){
// 
// First initialize the object which set's up the AJAX call
//
var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
                         'APPLICATION_PROCESS=getPrice',0);

//
// Add an attribute and value to the call.
//
get.add('G_ART_ID',art_id);
//
// Use the get method to execute the AJAX call
//
var gReturn = get.get();
//
// Update the DOM. $x is an APEX javascript API.
//
$x(target_el_id).value = gReturn;
//
// clean up...
//
get     = null;
gReturn = null;
//
return null;
};
</script>

Done! Give it a try here . To sum up: it all boils down to identifying a source element, give it a triggering condition, do something, identifying and finallly updating the target element.

Wow, sounds almost like a dynamic action!

vrijdag 23 november 2012

How to list the last N years?

Sometimes we are required to facilitate a select list, from which a user can select the last, say 25 years (including the current year). Instead of defining a reference-table `YEARS', we have to maintain, we can define a SQL query using the CONNECT BY function. Here is how:
 
select to_char(sysdate,'YYYY') - level + 1 
from dual 
connect by level <= 25

Ofcourse the 25 in the above example is arbitrary, one could just instead use a package constant or some function.

In Oracle APEX, when defining this in a select list, it is tempting to write the following code:

 
select 
  to_char(sysdate,'YYYY') - level + 1 display
, to_char(sysdate,'YYYY') - level + 1 return
from dual 
connect by level <= apex_constants.g_list_years

From a maintainability perspective one should consider implementing a shared component LOV based on the following query:
 
select 
   years display
 , years return
from (
      select 
       to_char(sysdate,'YYYY') - level + 1 years
      from dual 
      connect by level <= apex_helper_functions.list_number_of_years
     )

The `list_number_of_years' function would preferably make use of a APEX session-state value - when defined -, to give maximal flexibility in a given page.

Once defined in this manner, you will hopefully agree the list is easy to maintain.

[update 26-11-2012]
A carefull reader noticed (thanks JL!) that using the Oracle EXTRACT function would shave off one implicit conversion, resulting in a better performance (EXTRACT returns a NUMBER instead of VARCHAR2, so when doing - LEVEL + 1 Oracle doesn't have to convert to NUMBER). Here is the code:

 
select 
   years display
 , years return
from (
      select 
       extract(year from sysdate) - level + 1 years
      from dual 
      connect by level <= apex_helper_functions.list_number_of_years
     )
Always open for suggestions! Thanks JL!