Skip to Main Content

Saturday, December 3, 2011

Drag & Drop tabular form rows

APEX and jQuery

Here is how drag & Drop tabular form rows to order records how you like.This solution works for tabular forms where you do not use pagination on APEX 4.1. Your table need have column that store display sequence id. If your table do not have column that can be used for this, alter your table and add column.

ALTER TABLE your_table ADD (display_seq NUMBER);

Go into the report attributes of your tabular form. On the right hand side of the page you will see Tasks. In there click Add Derived Column. Edit derived column attributes and chnage Display As to Standard Report Column. Add to Column Formatting HTML Expression:

Add to Column Formatting HTML Expression:

<img class="sort-row" src="#IMAGE_PREFIX#ws/sort.gif" alt="" />

Next uncheck all Sort checkboxes from report attributes and add order by to your report query:

ORDER BY display_seq

Create dynamic Action. Select Advanced

  • Name: Set Form Ordering
  • Event: After Refresh
  • Selection Type: Region
  • Region: {select your report region}
  • Condition: No Condition
  • Action: Execute JavaScript code
  • Fire On Page Load: True
  • Code:
    $(this.triggeringElement).find(".report-standard").sortable({
     cursor: "move",
     handle: "img.sort-row",
     items: "tr:not(:first)",
     containment: ".report-standard",
     axis: "y",
     opacity: 0.9,
     revert: true,
     helper: function(e,u){
     u.children().each(function(){
      $(this).width($(this).width());
     });
     return u;
     }
    }).find("img.sort-row").css({"cursor":"move"}).disableSelection();
    
  • Selection Type: None

Create PL/SQL process On Submit - After Computations and Validations:

FOR i IN 1 .. APEX_APPLICATION.G_FROWID.COUNT
LOOP
  UPDATE emp SET sort_order = i WHERE rowid = APEX_APPLICATION.G_FROWID(i);
END LOOP;

Add to page HTML Header:

<script type="text/javascript" src="#IMAGE_PREFIX#libraries/jquery-ui/1.8.14/ui/minified/jquery.ui.sortable.min.js"></script>

NOTE ! If you are on APEX 5:
Instead of adding jQuery UI library to HTML header, place below to page JavaScript file URLs

#JQUERYUI_DIRECTORY#ui/#MIN_DIRECTORY#jquery.ui.sortable#MIN#.js

See working example.

Pagination

Comments

  • Kinjan Bhavsar 28 Jan 2021

    Ok thanks.

  • Jari Laine 28 Jan 2021

    Hi Kinjan Bhavsar,

    Example has few issues that I haven't had time to solve.
    You need wait till I find time to finalize the example and write blog post about it. 

    Regards,
    Jari

  • Kinjan Bhavsar 28 Jan 2021

    Hi Jari,
    I am trying same for interactive grid as you have done in your demo application.
    Can you please suggest how to create a derived column in the interactive grid and what change in JS may be required?

  • Kinjan Bhavsar 22 Jan 2021

    Thanks 😊

  • Jari Laine 22 Jan 2021

    Hi Kinjan Bhavsar ,

    No problem. Glad that this post helped you.

    One think that I noticed when you have two reports. You need also adjust parameter containment. I did that to your example also.

    .....
    containment: this.triggeringElement,
    .....
    

    If using CSS class as selector, containment might select first report and you can't drag row.

    Regards,
    Jari

  • Kinjan Bhavsar 22 Jan 2021

    Thanks Jari.
    It works like a charm.
    Really appreciate your help throughout.
    Thanks once again.

  • Jari Laine 22 Jan 2021

    Hi Kinjan Bhavsar,

    If your report static id is emp_rpt, then JavaScript selector is #report_table_emp_rpt like

    $("#report_table_emp_rpt").sortable({
    .....
    

    If you like use dynamic action Affected Elements attributes, then set those like

    • Selection Type: jQuery Selector
    • jQuery Selector: #report_table_emp_rpt

    and change JavaSript first line to

    this.affectedElements.sortable({
    .....
    

    I changed your sample to use dynamic action Affected Elements attributes and also corrected dynamic actions When attributes

    Regards,
    Jari

  • Kinjan Bhavsar 22 Jan 2021

    Hi Jari,
    I tried using the static id for both reports and created two dyanamic actions but it works only for first report and not the second one. Can you please check my demo app and please let me know whether am I doing anything wrong?
    https://apex.oracle.com/pls/apex/f?p=4550:1:116211463398310:::::
    workspace: xxx
    username: xxx
    password: xxx
    In that, App name APEX_DEMO and in that page number 5.
    Please let me know your thoughts.

  • Kinjan Bhavsar 22 Jan 2021

    Do you mean like below
    Example static id = emp_rpt then in DA, I need to add below
    Event : After Refresh
    SelectionType : jQuery Selector
    jQueySelector :#report_table_emp_rpt
    Let me know am I correct, so will give it a try and if doesn't work, can share my demo app :-)

  • Jari Laine 22 Jan 2021

    Hi Kinjan Bhavsar,

    You need create dynamic action for both reports. Then set static ID for both report. Set different static ID for both reports as ID should be unique.
    Then use ID as jQuery selector like #report_table_<report static ID here> in dynamic action JavaScript.

    Regards,
    Jari