Single Article

Drag & Drop tabular form rows

Category 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.

Comments

  • APEX 12-JUN-20

    No Problem, Thanks.!

        
  • Jari Laine 12-JUN-20

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

        
  • APEX 11-JUN-20

    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-20

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

        
  • APEX 11-JUN-20

    Hi Jari,

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

    Thanks!

        
  • siddhartha pasupuleti 24-OCT-19

    Thanks Jira

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

    Thanks :-)

        
  • Jari Laine 24-OCT-19

    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-19

    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

        
  • Jari Laine 24-OCT-19

    Hi siddhartha pasupuleti,

    From your example page 5, I'm not quite sure are you sorting report and update table by correct columns, because I don't know purpose of columns.

    You have columns SEQ_NO, DISPLAY_SEQ and SORT_ORDER. Report is sorted by column DISPLAY_SEQ, but in after submit process you update column SORT_ORDER that isn't visible or used in order by.

    Please check that you update same column that you use to sort report. Then I think you get what you are looking for.
    Please note that rows order is updated only when you submit page.

    Regards,
    Jari

        
  • siddhartha pasupuleti 24-OCT-19

    Workspace : dragdrop

    Thanks :-)

        

Global Right Column

Archives

Subscribe in a Reader