Friday, 2 December 2011

Re-Sequence Tabular Form


Well, a while back I was looking at what's provided in the application builder. On a page with a number of application items - in tree view, if you right click and select Drag & Drop Layout, and then switch tabs to Reorder Items, you are presented with a tabular form. On the far right you'll will see little up and down arrow image icons, and after selecting, allows you to easily move items up or down. This is not something that comes standard, and in my opinion, something that is very useful.

Once again, if we look closely, we should be able to easily replicate that functionality.

Step one, I will create a table - a common one of the top of my head is statuses, so i will create a status table with 3 columns: status_id, name, sequence:


CREATE table "STATUS" (
    "STATUS_ID"  NUMBER,
    "NAME"       VARCHAR2(20),
    "SEQUENCE"   NUMBER,
    constraint  "STATUS_PK" primary key ("STATUS_ID")
)
/

CREATE sequence "STATUS_SEQ"
/

CREATE trigger "BI_STATUS"
  before insert on "STATUS"
  for each row
begin
  if :NEW."STATUS_ID" is null then
    select "STATUS_SEQ".nextval into :NEW."STATUS_ID" from dual;
  end if;
end;
/


Then a create a new page with a tabular form - all pretty standard stuff.

Since the up and down arrows are kind of links, i'm going to add a column link column type. If you go into the report attributes, on the right hand side of the page, you will see a little box with the heading Tasks. In there are four options:

  • Show Query Source
  • Add Column Link
  • Add Derived Column
  • Unsubscribe Templates

As you can see, the second one is the one we want. So click that, specify Display as: Standard Report Column; in the Column Attributes section (this is so the img tags are rendered); and in the HTML Expression add the following:


<img class="pb" src="/i/htmldb/icons/up_arrow.gif" alt="" /><img class="pb" src="/i/htmldb/icons/down_arrow.gif" alt="" />


Which is just what we copied from inspecting the re-order items page.

Save the changes, move that column to the last item in the report columns, and run the page, you should see the two arrows in your report.

Next step, is to make them actually re-order the rows. If you inspect the arrows in the re-order items page, you will be able to see that they have a click event attached to them. Which you can see has the following code:


b.row.down(this)

And that it is in the script apex_4_1.js. This particular script has been minified, so it will be very hard to look at the code to see what is actually going on here. Fortunately, when you install apex, you get the uncompressed version of the code. So if you go into the images folder there is one with exactly the same filename. If we look in this and search for the text b.row.down - you won't find anything, most likely because the b variable is just one that has been minified to make the script smaller. SO instead, just search for row.down. Before long, you will find the actual function that re-orders rows. Without going into the specifics of that function, we can build the following two functions:


function moveRowDown(row,idx){
 var lastRow = false;
 currRow = $x_UpTill(row, 'TR');
 currTable = currRow.parentNode;
 ie_RowFixStart(currRow);
 nextRow = currRow.nextSibling;

 while(nextRow != null){
  if(nextRow.nodeType ==1){break;}
  nextRow = nextRow.nextSibling;
 }

 var currRowSeqEle = $('input[name="' + idx + '"]', currRow)[0];
 var nextRowSeqEle =$('input[name="' + idx + '"]', nextRow)[0];
 var currValue = currRowSeqEle.value;
 var nextValue = nextRowSeqEle.value;

 if(nextRow ){
  currRowSeqEle.value = nextValue;
  nextRowSeqEle.value = currValue;
  currTable.insertBefore(currRow, nextRow.nextSibling);
 }else {
  //if you want to implement that it goes back to top. I'm not bothering.
  //currTable.insertBefore(currRow, currTable.getElementsByTagName('TR')[1]);
 }
}

function moveRowUp(row, idx){
 var firstRow = false;
 currRow = $x_UpTill(row, 'TR');
 currTable = currRow.parentNode;
 prevRow = currRow.previousSibling;
 while(prevRow != null){
  if(prevRow.nodeType ==1){break;}
  prevRow = prevRow.previousSibling;
 }

 var currRowSeqEle = $('input[name="' + idx + '"]', currRow)[0];
 var prevRowSeqEle =$('input[name="' + idx + '"]', prevRow)[0];
 var currValue = currRowSeqEle.value;
 var prevValue = prevRowSeqEle.value;

 if(prevRow != null && prevRow.firstChild.nodeName != 'TH'){

  currRowSeqEle.value = prevValue;
  prevRowSeqEle.value = currValue;
  currTable.insertBefore(currRow, prevRow);
 } else {
  //if you want to implement that it goes back to top. I'm not bothering.
  //currTable.appendChild(currRow);
    }
}


A couple of things to note about this code.

The original version - if someone pushes down when at the bottom it will re-go to the top; if someone pushes up when at the top, it will re-go to the bottom. I decided against this for simplicity reasons - For one if one a multi page tabular form, i you push down at the bottom, should it go to the next page or back to the beginning? Having to cycle through all the sequences to re-calculate them - this isn't a big deal at all, but i've just decided i prefer that when they reach the bottom, that is that.

The functions both accept two parameters - the row, that is, the element being pushed, And also the f0x index. Each tabular form column gets an f0x index which is in the name attribute. In our case, it is f04, so i will be passing that value.

So paste this code somewhere - in your page header; in your page template; in your personal javascript library. Now, go back and edit the column link to add an onclick event to both img elements. So they should look like:


<img class="pb" onclick="moveRowUp(this, 'f04')" src="/i/htmldb/icons/up_arrow.gif" alt="" /><img class="pb" onclick="moveRowDown(this, 'f04')" src="/i/htmldb/icons/down_arrow.gif" alt="" />

And voila. Now you can re-sequence your items. The only thing you are likely wanting to do is hide the sequence column (hidden display type). I've left mine as display simply so i can see the sequence working, for example purposes.

See an example here: http://apex.oracle.com/pls/apex/f?p=45448:9

..

Another solution is not to deal with the sequence number on client side, and just have an on submit process that re-orders them, such as (un-tested):


for i in 1..APEX_APPLICATION.g_f01.COUNT LOOP
--update table set seq_column = i;
END LOOP;