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

  • Jari Laine 22 Sep 2020

    Hi APEX Learn,

    This example is done back in APEX 4 times. Those classic report templates you mention and exists in APEX 18 and later use different CSS classes and html elements than standard report template.

    You need adjust JavaScript in dynamic accordingly.

    I can't help more withe given details, but see example in apex.oracle.com

  • APEX Learn 22 Sep 2020

    I am using APEX 19.1. How can I get this working on a Standard Classic Report.

    I am able to get the sorting working correctly on cards/medialist/LinksList.

  • APEX 12 Jun 2020

    No Problem, Thanks.!

  • Jari Laine 12 Jun 2020

    Yes, you need change dynamic action jQuery selectors to match IR css classes. Unfortunately sample isn't available for download.

  • APEX 11 Jun 2020

    Thanks for your quick response. I'm actually trying to do with an interactive report and the sorting doesn't seem to work. Do I need to change the css class for interactive report? If you have any reference to your application itself to download that will help. Thanks!

  • Jari Laine 11 Jun 2020

    Hi,
    You can access example just entering something to user name field

  • APEX 11 Jun 2020

    Hi Jari,

    Could you please provide access to your apex application where this code was implemented. Appreciate your help.

    Thanks!

  • siddhartha pasupuleti 24 Oct 2019

    Thanks Jira

    Solved all my queries & it was really pleasure to connect with you.

    Thanks :-)

  • Jari Laine 24 Oct 2019

    Hi siddhartha pasupuleti,

    You shouldn't use order by in report query. Instead set sorting Default Sequence to SORT_ORDER column.
    I did that change to example page 5 and I think it might work now.

    Regards,
    Jari

  • siddhartha pasupuleti 24 Oct 2019

    Hi Jira

    Updated the changes as u advised.

    Perfectly working & sort row is updating in DB.

    But one issue I am facing is :

    After submitting report DB is updated but report is not displaying proper sort order and moving back to the position where initially report was there

    even though order by clause is SORT_ORDER.

    Thanks for your patience & response on the queries.

    Regards

    Siddhartha