Friday, June 25, 2010

Application Express - using the sort arrows in a tabular form

!! This post is created using Apex 3.x and will not work for newer versions. For Apex 4.x a new blogpost is available !!

When you use APEX, chances are you've come across this feature many times. When developing an application and you create a form or report, you can arrange the order of the columns by using arrows (up/down). This changes the order in which the columns are displayed, just by clicking on the appropriate arrow.



So far, nothing new. But when you think about it: APEX has been build using APEX. This should mean that this functionality is also available to use in you own application. There seems to be one problem: there is no standard item or option to enable this.

Searching the internet, there are some strategies as how this can be accomplished. So far, I have not found a complete description that is short and easy to implement. Starting from there, I found that you need only three rather simple steps to accomplish this in your own tabular form.

I will demonstrate this using a simple tabular form:



First step: create the arrows

The first step is to incorporate some standard Javascript into your region. After creating a regular tabular form, you add one piece of Javascript to the footer of the region.

<script type="text/javascript">
  var g_#REGION_ID#;
  var g_rpreview_global = 'report_#REGION_ID#';
  function f_#REGION_ID#(){
   g_#REGION_ID# = new apex.tabular.sort('report_#REGION_ID#');
   g_#REGION_ID#.row.after_move = function(){rpreview('report_#REGION_ID#')}
   rpreview('report_#REGION_ID#');
  }
  addLoadEvent(f_#REGION_ID#);
</script>

After this, the form has the up/down arrows next to every line.


Second step: hide the order column and make it orderable

To enable ordering and make the order column hidden, just take these steps:
  • Edit the form properties and set the order item property "show" unchecked
  • Edit the column properties for "order" and set Element Attributes to 'class="orderby"'

Now we have a simple form which we can order using the arrows.


Third step: adjusting the style

The last step we need to take is to make the background and header to look like the template we use. Regrettably, I found no really easy (configurable) way to do this. So, we'll do this the hard way.

First of all, you'll have to get the style you're using in the application. The stylesheet is referenced in your application and viewable by just showing the source of your page in your browser. In this example I ran the application, selected "show source" after right-clicking and searched for the stylesheet. This shouldn't be too hard to find (was on line 3 for me).

When you look at this stylesheet (by either downloading it from the application server, or looking it up in an APEX software download), you should be able to find the section of interest by searching for "Standard th". The line you'll find and the next line are to be copied. For me this was:

table.t13Standard th.t13ReportHeader {border-bottom:1px #aaa solid;}
table.t13Standard td.t13data{background:#efefef;border-top:1px #fff solid;border-bottom:1px #ccc solid;font-size:8pt;padding:3px 9px;empty-cells:show;}

Depending on the template you chose, the numbers and settings will be somewhat different. These lines should be modified to a more generic class for th and td. So we'll leave out the specific .t13ReportHeader and .t13data qualifiers.

table.t13Standard th {border-bottom:1px #aaa solid;}
table.t13Standard td {background:#efefef;border-top:1px #fff solid;border-bottom:1px #ccc solid;font-size:8pt;padding:3px 9px;empty-cells:show;}


Finally, we must set the background color for the header. You can find this about 6 lines higher in the stylesheet, which contains the th.t13ReportHeader, which contains a background. Place the style tags around this block. We now have:

<style>
table.t13Standard th {border-bottom:1px #aaa solid;background-color:#7b9cbd;}
table.t13Standard td {background:#efefef;border-top:1px #fff solid;border-bottom:1px #ccc solid;font-size:8pt;padding:3px 9px;empty-cells:show;}
</style>


By placing this as a style tag in the HTML header of the page, our tabular form is now ready to go.

15 comments:

  1. How would you hide the arrows when you add pagination to the report?

    ReplyDelete
  2. I think I see what you mean. When you add pagination to the form (report layout), the pagination line will also have sort arrows displayed. Though this does nothing, except move around the pagination, it is a bit strange.

    So far, I have not found a way around this. As I use the standard apex.tabular.sort, I have nothing to modify this behaviour. As far as I know, you'd better not use pagination together with the sort arrows. This may be the reason that you don't see pagination in the places where Apex uses this mechanism itself...

    ReplyDelete
  3. hi, in:
    "The first step is to incorporate some standard Javascript into your region. After creating a regular tabular form, you add one piece of Javascript to the footer of the region."

    where specific location for the javascript.

    ReplyDelete
    Replies
    1. The location for the Javascript is in the footer of the region. When you have created a (tabular) page/region, you can edit this region (under the section "Regions" on the page you have created by following the link for the region that must contain the arrows).
      On the edit page there is a section "Header and Footer" and in that section you have a textbox with the caption "Region Footer". Add the script there and press the "Apply changes" button.

      Good luck!

      Delete
  4. How can one put the arrow column on the left side instead of the right?

    ReplyDelete
    Replies
    1. Hi Joe,

      To be honest: I don't know. I suspect that this is not possible, as the sorting is added using "standard" functionality apex.tabular.sort. I haven't found (yet) how to manipulate that behaviour.

      Delete
    2. I appreciate your quick response. That's a bummer however this solution is pretty neat though. I suspect you're right that it's a functionality of the apex container just like the goofy pagination row with the arrows I can't seem to get rid of even with CSS.

      Delete
    3. will it works for dynamic rows, i have a table if required than only i will add a row, in this scenario can we integrate your solution

      Delete
    4. That will most probably not work directly. As a workaround, you could use a pop-up to insert the row, using max(value)+1 for the order value. After inserting and refreshing, you can manipulate the row using the sort arrows..

      Delete
  5. Great thanks for subject!

    Works fine on APEX 4.2.3.00.08 where step 1 and 2 are enough actually but row.move function algorithm should be changed a bit to be compatible with modern tabular for region template.

    Hint, I am using static id "params_" for report region

    <__script type="text/javascript">
    // APEX adds static id to region only but function expected table id, so add it
    $('#report_params_ .uReportBody table:first').attr('id', 'report_params')
    // not required
    //var g_rpreview_global = 'report_params';
    //var g_params;
    function f_params(){
    g_params = new apex.tabular.sort('report_params');
    g_params.row.after_move = function(){ rpreview() }
    // override
    g_params.row.move = function (a, c) {

    // need access to parent object
    b = g_params

    // copy of original function body
    // in Chrome console type "g_params.row.move" to get body
    ...

    // Seems original algorithm was designed for old-fashioned template where
    // header was placed into tbody so algorithm started from 1 but now
    // header is in theader and need to start from 0
    for (var h = 0, i = e.rows.length; h < i; h++) {
    ...
    }

    ReplyDelete
  6. Hello Erik,
    by the first step, create the arrows, i have just one up/down arrow in the middle oft he tabular form and not to every line?
    Can you please tell me waht did i do wrong ?

    ReplyDelete
    Replies
    1. Hi Michel,

      I have not yet had a chance to try and reproduce this, but here are some thoughts for you:
      * What version are you using. This was originally done on Apex 3.x and already a comment was made that on 4.x there are some differences.
      * Did you copy/paste the snippet? It should work without modification.
      * Did you place it in the footer of the Tabular Form Region? Are there any other snippets there that might interfere?

      I'll try to reproduce this, but I've never seen this behaviour before... In the meanwhile, could you investigate these points?

      Hope this helps.

      Erik

      Delete
  7. Hi Erik,
    thank you very much indeed for your quick reply and your recommendations
    Unfortunately it doesn’t work like in your example at the first step.

    The Apex version I am using is the 4.1.1. I implemented all your comments into a new project with thème 2 and a regular tabular form Type : SQL Query (updateable report).

    I read the comment concerning the 4.x version but the given comments do not touch step one.

    Remark, when I add pagination and click the arrows up or down, the pagination line and the complete list order are changing on the screen from headline to footline and backwards.

    Thanking you very much for your kind help in advance.
    Michel

    ReplyDelete
    Replies
    1. Hi Michel,

      I reproduced this in Apex 4.2 (should not be too different from your situation) and found the same problem. It turns out that the Javascript I add is referencing the wrong region.
      Actually, a different approach must be taken. I solved the problem and wrote a new post about that (see http://eriksekeris.blogspot.nl/2014/09/application-express-using-sort-arrows.html). I hope that this helps you solve the problems you are facing.

      Regards,

      Erik

      Delete
  8. Thank you very much indeed for your kind help. I did all tests of the chapter "using the sort arrows in a tabular form - Updated for Apex 4.x" as explained by you and it works. It is really great! Once again thank you.
    Best regards.
    Michel

    ReplyDelete