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.

Social Buttons

Comments

  • 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

        
  • Jari Laine 24 Oct 2019  

    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 2019  

    Workspace : dragdrop

    Thanks :-)

        
  • Jari Laine 23 Oct 2019  

    Hi siddhartha pasupuleti,

    Could you please provide again your workspace name in apex.oracle.com? I did already forgot it.

    Regards,
    Jari

        
  • siddhartha pasupuleti 23 Oct 2019  

    Hi Jira

    Just to Update.

    With your approach I am able to get the same functionality with version 5.1.4.00.08.

    Thanks for your support in resolving this.

    One one this I have to fix is

    I had one more query here

    Seq Number can be updated back to DB data..? because when rows are changed seq. no is changing but but not overridden in DB.

    Any suggestions would be helpful

    Regards

    Siddhartha

        
  • siddhartha pasupuleti 23 Oct 2019  

    Thanks Jira for your quick response.

    I can see It's working now wonderful.

    I had one more query here

    1) Seq Number can be updated back to DB data..? because when rows are changed seq. no is changing but but not overridden in DB.

    2) My Current working version is 5.1.4.00.08 where I have to implement the same capability.

    I followed the same steps in my Apex version but it's not working can you please suggest any thing where I have to correct.

    Regards

    Siddhartha

        
  • Jari Laine 23 Oct 2019  

    Hi siddhartha pasupuleti,

    Dynamic action need to be set "Fire on Initialization".
    You need adjust selectors for JavaScript in dynamic action.

    You don't need add anything to page JavaScript file URLs on APEX 19.1

    I did these changes to your example in apex.oracle.com

    Regards,
    Jari

        
  • siddhartha pasupuleti 23 Oct 2019  

    Hi Jira,

    Hope you are doing good.

    Your articel was good & I tried to replicate the samewithout success

    I think I followed all steps, but not drag and drop functionality is not working.

    I'll really appreciate help

    Workspace / Application information

    http://apex.oracle.com

    Workspace : xxx

    username / pwd : xxx/xxx

    Application : nnn

    page: 5 - dd

    Big thanks in advance

        

Global Right Column

Search

Archives

Subscribe in a Reader