Implementing Drag & Drop in your APEX applications

Posted on Posted in APEX, Javascript, Oracle, Web
Implementing Drag & Drop in your APEX applications

I first learned how to implement Drag & Drop functionality from Doug Gault’s presentation at Kaleidoscope 2010 (yes before it was Kscope) “Replicating NetFlix Queue Drag-and-Drop Functionality with Oracle Application Express/jQuery” (Membership required). I mean, I wasn’t actually there, but the presentation has excellent step by step instructions. My goal is for this post to become a useful quick reference guide (as much for me as others).

With the proliferation of mobile apps and being fully into (probably the end of) Web 2.0, users come to expect this sort of feature more and more. When we implement it in our Oracle Application Express applications, it brings a nice “wow” factor. That said, I don’t think it’s for every scenario, and I would not overuse it. Also, keep in mind, on mobile devices, you may need some extra libraries to fill in for the lack of a mouse.

If you’ve seen some of my videos, Part 1 covers the creation of the template that is mentioned below. In video installment 004, I following these instructions. If you have any questions, hopefully, the video will cover them.

Demo app here.

Structure to Sort “Lines”

You’ll need a parent container that includes the sortable lines.
For a report think <table> as the container and <tr> as the lines. For a list think <ul> as the container and <li> as the lines.

The lines that are to be sorted require some ID that uniquely identifies them. You can use a real id tag (id="line123") attribute or a dataset attribute like data-id. The dataset approach is more robust as HTML id values cannot (well should not) start with a number. We’ll use a markup like the following:

<ul>
  <li data-id="1"></li>
  <li data-id="2"></li>
  <li data-id="3"></li>
</ul>

We’ll define a “Named Column” report template to define the UL list. (To see how to do this watch Part 1)

“Before Rows”

<ul>

“Row Template 1”

    <li data-id="#ID#">#TODO#</li>

“After Rows”

</ul>

We’ll use the following table to hold our data:

create table app_todos (
    id number generated by default on null as identity (start with 1) primary key not null
  , display_seq number not null
  , todo varchar2(32) not null
)

The DISPLAY_SEQ is the value we’ll use to sort and to re-arrange after a drag & drop action takes place.
The following SQL will define our Classic Report with the custom Named Column template. Give the report a Static ID, we’ll use todoRegion

select id
     , todo
  from app_todos
  order by display_seq

Include the sortable jQuery UI library

This library is already part of your standard APEX distribution. Select the correct one for your version of APEX

APEX 4.2

#IMAGE_PREFIX#libraries/jquery-ui/1.8.22/ui/minified/jquery.ui.sortable.min.js

APEX 5.0 & 5.1

#IMAGE_PREFIX#libraries/jquery-ui/1.10.4/ui/minified/jquery.ui.sortable.min.js

APEX 18.1 and beyond

Just remove the sortable.min.js reference. This functionality is included by default

Make the report Sortable

var el = this.affectedElements[0];
$(el).find("ul").sortable({
    items: 'li'
  , containment: el
  , update: function(event,ui) { updateDisplaySeq(el); }
});

Notice this.affectedElements[0] which means you’ll use this within a Dynamic Action and specify your report region as the Affected Element.
containment (line 4) is optional, but sometimes it’s a helpful option to restrict how far the elements can be dragged. Try it without to see the effect.
At this point, the report lines can be dragged, but their new order won’t be saved because we have not defined updateDisplaySeq()

function updateDisplaySeq(pRegionID) {
  var results = $(pRegionID).find("ul.appTodo").sortable('toArray', {attribute: 'data-id'});
  apex.server.process ( "UPDATE_ORDER",
    {f01:results},
    {
      success: function( pData )
      {
        // apex.jQuery.gritter.add({title: "Todos",text:"New order saved."});
        apex.message.showPageSuccess("New order saved.");
        apex.event.trigger(pRegionID, 'apexrefresh');
      }
    }
  );
}

NOTICE Changes made on 9/12/16 :
I’ve made some small but important changes. Instead of using x01 the code above now uses f01. This also eliminates the need to issue a .toString() on line 2. The next big change is that now, in the PL/SQL side we can loop directly on apex_application.g_f01 and we don’t need to use apex_util.string_to_table. Thank you to Erik Diaz for pointing this out in the comments below.

The 'toArray' method will return an array with the ID of our lines. Then .toString() makes it easier to work with our AJAX process.

By default toArray looks for the element ID, since we’re using data-id we need this option: {attribute: 'data-id'}

The code for UPDATE_ORDER will follow, but notice line 8 where we use APEX API apex.message.showPageSuccess to display a success message.

Finally, line 9 forces an APEX Refresh of the report to ensure all the latest data is in place and fresh.

AJAX Callback

This is the AJAX Callback reference in the code above as UPDATE_ORDER. Remember that the AJAX Callback name is case sensitive.
The following code receives f01 with our array in the new order we want.

declare
  s number;
begin

for i in 1..apex_application.g_f01.count loop
  s := i * 10;
  update app_todos
     set display_seq = s
   where id = to_number(apex_application.g_f01(i))
     and display_seq != s;
end loop;

htp.prn('{"result":"OK"}');

exception
  when OTHERS then
    htp.prn('{"result":"ERROR"}');
end;

Extras

If you’re using a standard Classic Report (instead of a Named Column template that you can control). You’ll need to add the ID handles to your <tr> rows. The following code will find the ID column (it could be a link column). You’ll want to have this snippet in the attributes data-id=#ID#.It will extract it from a dataset attribute and add it to the TR tags.You will need this code defined as a function because you’ll call it after Refresh to re-insert the ID to the report.pRegionID is the Static ID of the report. The first selector for $r may need to be adjusted depending on the theme.

var fixHelper = function(e, ui) {
  ui.children().each(function() {
  $(this).width($(this).width());
});
return ui;
}function makeSortable(pRegionID) {
var $r = $("#report_" + pRegionID);
var r = $r[0];// add ID to TR element so we know the correct position
$r.find("[headers='ID'] a").each(function(){
// This selector should work for Theme 26
// $(this).parents('.uReportBody .uReport tr').attr('data-id',$(this).data("id"));
$(this).parent().parent().attr('data-id',$(this).data("id"));
});// finally make it sortable
$r.find("table.t-Report-report").sortable({
items: 'tr'
, containment: r
, helper: fixHelper
, update: function(event,ui) { updateDisplaySeq(r); }
});
}

fixHelper is very useful for a table report as it will set the width or the TR being dragged to the original width it had. Said another way, it will maintain the width of the row being dragged. Without it, the row may collapse to the width of its elements. You can see it’s being referenced in the helper parameter as part of the sortable constructor.The selector inside updateDisplaySeq (called above) will need to be changed to work with a table. Something like this should work for you:

var results = $(pRegionID).find("table.t-Report-report").sortable('toArray', {attribute: 'data-id'});
About

Hi, I'm Jorge Rimblas. Father, husband, photographer, Oraclenerd, Oracle APEX expert, Oracle ACE, coffee lover, car guy, gadget addict, etc... I'm an APEX Tech Lead DRW. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.

47 Comments on “Implementing Drag & Drop in your APEX applications”

  2. Hi Jorge,

    Thank you for sharing this, very useful information.

    I’m wondering if you could pass the “results” variable in updateDisplaySeq as an array using the f01 parameter, instead of converting it toString()? This way, you could use apex_application.g_f01.count and apex_application.g_f01(i) on your UPDATE_ORDER procedure thus, no need to use apex_util.string_to_table, which can save you some lines of code and make it cleaner.

    Best Regards!

    Reply

    • Now that you mention it, in 4.2 that was the behavior. The `toString` was not needed and you could just loop in the elements. I’m not completely sure what changed in APEX 5 or if I have a bug in the code. Definitely something to explore. It should be possible.
      Thanks!

      Reply

    • Erik, you were so right! It took me until today to realize that somewhere along the line (probably when the code changed from htmldb_Get to apex.server.process) that I mistakenly switched from f01 to x01. As you correctly pointed out, f01 is already an array. Using it greatly simplifies the code. I changed the code above and made a note.
      When you first commented, my brain did not distinguish the (subtle, but critical) difference between f01 and x01 in your comment. THANK YOU for pointing this out even if I didn’t get it at first. My bad.

      Reply

  3. And how should this be implemented if you want to move values between two regions or reports via drag and drop?

    Reply

    • That’s a great question and one that I just received a few times in the last week.
      I’m going to have to explore that. I think it would require using the `draggable` and the `droppable` jQuery functions.
      Those allow you to specify which elements can be moved and which elements can receive them.

      Reply

  5. Hi Jorge,
    First of all, Big Thank You!! for posting this very cool blog with the actual how-to and video! (Doesn’t get any better than that!)
    I am super happy to report that I implemented the drag-n-drop on my classic report and that really elevated how modern the application interaction feels!
    Before the drag-n-drop functionality, the user had to edit in a tabular form the order, ugh! Now is as slick as any other modern app.

    One thing that I struggled (actually gave up) is implementing this on a Interactive Report.. I just could not find selector.. Maybe is not possible… who knows.. I’m happy and the user is happy..

    Gracias!
    Gaspar G.

    Reply

    • Thank you!
      The IR can get tricky because they may have two headers. Once is used for fixing the headings when you scroll.

      I did a quick test and this seems to work for an IR on the Universal Theme with the standard Edit column (headers=’LINK’) :

      “`
      var $r=$(“#” + pRegionID).find(“.t-fht-tbody”);

      $r.find(“[headers=’LINK’] a”).each(function(){
      $(this).parent().parent().attr(‘data-id’,$(this).data(“id”));
      });

      $r.sortable({
      items: ‘tr’
      , containment: r
      , helper: fixHelper
      , update: function(event,ui) { updateDisplaySeq(r); }
      });
      “`

      Word of caution: Since the IR can sort on any column (unless you disable it) a refresh may re-sort data in an unexpected way.

      Reply

  6. Hi Jorge, nice tutorial, thanks. Did you ever get round to exploring draggable / droppable for multiple reports?

    Reply

    • Thank you Ross. I explored the technical details and it’s a matter of defining the droppable regions. The data structure we use needs to be able to identify the area you’re coming from and going to (which is not hard to do).
      However, I have not worked on an actual working prototype. But I do see a use case in my near future…

      Reply

  7. In that case I look forward to your write up. I am trying right now and so far can drag and drop the regions into each other, but haven’t figured out how to move a particular row from one to another. I’ll create a demo instance if I do figure it out.

    Thanks once again for your videos, they are very clear and concise, and I appreciate you doing the debugging as well, helps us learn what to look out for when experimenting with similar issues.

    Reply

  8. Hi Jorge,

    Thanks for this useful article. I have a doubt. if the report is with pagination, how the update process will work ?

    Reply

    • Good question, as you figured out, everything is coded assuming no pagination.
      However, I think it would be a matter of adjusting the process and maintaining the sort position on previous page and following pages.

      Reply

  9. Please help!! I am running APEX 5.1.2 and am trying to get this to work on an Interactive Grid where I’m getting the data from a table with a PK as an ID and a Sequence number column. I got the sorting part working where I can click and drag a row down or up and it will remain. But that’s as far as I can get. I’ve been able to add a tag to the tbody and the tr rows by adding the data-id #ID#. But I can’t get that data to the ajax call process based on your work. Was hoping you could give me some guidance? Any help will be greatly greatly appreciated!!

    Reply

    • It’s like this piece: $(“tbody.ui-sortable”).sortable(‘toArray’, {attribute: ‘data-id’}) is grabbing all the ID’s, but it’s grabbing the ID’s from before it was resequenced. Like it’s grabbing the data from a cache somewhere? But I have caching disabled everywhere I look and have cleared the browser cache too. Any advice here? Seems like absolutely everything is working except grabbing the updated/resequenced ID’s.

      -Derek

      Reply

      • Derek, I going to say you can’t do it with this technique. This technique is for Classic Reports for a reason.
        The IG is a whole different beast.
        Perhaps there’s an approach you can use
        from the IG Cookbook:
        http://hardlikesoftware.com/weblog/2017/07/24/apex-ig-cookbook-update/
        And it would be useful o get an idea of just how different the IG really is here:
        http://hardlikesoftware.com/weblog/2017/01/18/how-to-hack-apex-interactive-grid-part-1/

        Reply

        • It’s so close though! I’m getting everything working just like your video. So when I’m in the console on the browser and I move a row down. I can see in the Console that the order of the ‘tr’ rows have changed. But then when I run this in the console: $(“tbody.ui-sortable”).sortable(‘toArray’, {attribute: ‘data-id’}) I’m getting some kind of cached result of the original order of ID’s instead of the new re-arranged order. When you do a toArray does it get stored somewhere? If so do you know if there’s a way to clear it? If there’s a way to clear it then we will have it working with an interactive grid!

          -Derek

          Reply

          • I don’t know of any caching. But you’re going to run into another issue. The IG stores it’s own data model in JS. You’ll need to alter that or you’ll be out of sync.
            Oh and the IG does not support a Refresh DA. It requires a different technique.

          • Is there a different way to grab those ID’s from the web page other than $(“tbody.ui-sortable”).sortable(‘toArray’, {attribute: ‘data-id’})?

          • Hey thanks for the info on this page. I was able to figure out the problem and now have the sorting functionality working on an interactive grid with apex 5.1.2 without any issues. It’s working amazing!

            Thanks,
            Derek

          • Derek – I am trying to also get this to work with an IG but cannot figure out how to keep them in sync. Any help you could give me would be greatly appreciated.

            Rick Schoppenhorst
            Humana, Inc

    • Can you tell me how you got it to work or point me at an example? I would greatly appreciate it.

      Reply

  11. Thanks a lot. I took me some time to figure out how to create the dynami action, but finally I succeeded.
    One remark. In your UPDATE_ORDER procedure, you update table app_todo, but originally you created table app_todos (with “s” at then end).
    In that way I realized that such a PL/SQL error is hard to detect. The update just didn’t happen, but I needed to trace the ajax calls to see the ‘table or view does not exist’ message. If you would have a way to show clearly that the plsql has failed, that would be helpful.

    Reply

    • Thank you very much for the feedback and bug report. Corrected.
      And yes, good idea, it is possible to trap that error and take action. `apex.server.process` can use an error section
      “`
      error: function (pData) {
      apex.navigation.redirect(“f?p=” + $v(“pFlowId”) + “:ERRORPAGE”);
      }
      “`

      Reply

  12. Hi Jorge,

    Thanks a lot.
    I did and it was great.

    But in Google Chrome I had problems, because the line disappear.

    In Firefox 100% ok.

    Any tips?

    Thanks!

    Reply

    • That is very strange, I’ve never had issues with Chrome. This may point out to an issue with the helper function. This function is in charge of rendering the placeholder.

      Reply

  13. Hi Jorge,

    First of all thank you for that post and all the explanations. That helped a lot and I am very happy about it. As we are using Internet Explorer mostly I experienced problems with drag’n’drop, It seems as if the changed order is not being stored. Is there any news on this how this could be fixed?

    Thanks for your Feedback.
    Isabell

    Reply

    • I suspect something else is amiss. You don’t mention the IE version, but to the best of my knowledge, the functionality does work on IE11 & Edge. Do you see any errors on the Console?

      Reply

  14. Hi Jorge,

    Thanks for such a great post! I tried to implement this on the IR and was successful!.

    Now, I’m trying to figure out if this would work even incase there is a Control Break on the Interactive Report? In this case, the header has two values, one is LINK and the other is a random numerical string.
    I tried a few things like

    $r.find(‘[headers*=”LINK”] a’).each(function()

    i.e. the attribute selector * and some filter functions, but it almost never catches the correct one and fails to “pick up” the row to drag. Would be great if you could point me in the right direction for this? Or would it just never work incase a Control Break is present?

    Reply

    • Interesting challenge. I never used this with an IR and even less with Control Breaks.

      I prefer to use the tilde (~) like so:
      “`
      $(‘[headers~=”LINK”] a’)
      “`
      Because it matches whole words. But I don’t think that’s your issue. Perhaps the issue is that the TR rows for the group are not draggable, I wonder if this interferes with the functionality. Maybe they need to be flagged as draggable also, or it depends on what you’re trying to accomplish.

      Reply

      • Haha, you do love comments :) Thanks for your quick response Jorge. I will try to work out if enabling draggable on this works. What I would like to achieve is to drag and drops rows on the IR based on the Control Break because the data is essentially divided into different groups and each group will have its own “order”.

        I did find a lazy workaround where the user uses the IR Filter to find rows that belong to a group and then “orders” the rows for that group and moves on to the next. There are about 4-5 such groups so it should not be a lot of hassle to use the Filter. Then the user clears all filters and applies the control break. This seems to do the job.

        So its not like I am stuck, but this just screams “not too intuitive” and I’m really curious to know why this would not work with a Control Break. I will get back if I am able to solve it.

        Thanks once again!

        Reply

  15. Hi Jorge,

    Thanks a lot for the instructions and video.

    I am facing an issue with sortable when there’s a refresh involved in the region.

    I created a Classic Report with the Media List template and added the JavaScript action on load with the affected element set to the report. When the page loads, the drag and drop and sorting works.

    Then there is a button on the report region. The behaviour is defined by DA. The TRUE action on the DA is a refresh of the report region. This breaks the sorting and drag and drop feature. I also tried adding a second action (after the Refresh action). This second action was a JS expression (same as the original one). However, still no luck.

    Ajax refresh breaks the sortable feature and the fix is to add it again. But for some reason this is not working in APEX with the DA actions. I also tried running “destroy” method first. That also didn’t help.

    Would appreciate if you had any suggestions for this.

    Reply

  16. Did anyone figure out how to do this with an IR? I am having trouble getting the sort to save. I have it drag and dropping but not staying put. Any help?

    Reply

    • Hi, I did.
      Added Jscript File URLs:
      #JQUERYUI_DIRECTORY#ui/#MIN_DIRECTORY#jquery.ui.sortable#MIN#.js
      #IMAGE_PREFIX#libraries/jquery-ui/1.8.14/ui/minified/jquery.ui.sortable.min.js

      In my query I have a “select …. , ” set_order ” , being set_order the column to use to order.
      In the column Type: Plain Text.
      In the column “Heading” I have a hint and icon to the user:

      In the column HTML Expression:

      In the column, Enable Users to… Everything to NO.

      I also have, embedded in another column, apex_item.hidden, I have 2, one to reflect the ID, the other to reflect the order in which I am setting the rows, since later will have to use that in order to save the order they currently have. It could be in another column where you reflect current order, this is as you choose it to be, but 2 apex_item.hidden are needed, to associate to the table’s ID and to associate the right order will have.

      So, let’s say apex_item.hidden(1, ID) and apex_item.hidden(2, order).

      This other column, should have a static ID, I named mine as SPC_ORDER

      Now, in order to work… you need to create a DA on the Region that contains the query, with an event of AFTER REFRESH.
      And the true Action to be jscript Code:

      $(this.triggeringElement).find(“.a-IRR-table”).sortable({
      cursor : “move”,
      handle : “img.sort-row”,
      items : “tr”, //”tr:not(:first)”,
      containment : $(this.triggeringElement),
      axis : “y”,
      opacity : 0.9,
      revert : true,
      helper : function(e,u){
      u.children().each(function(){
      $(this).width($(this).width());
      });
      return u;
      },
      stop : function(){
      $(this).find(‘td[headers=”SPC_ORDER”]’).each(function(i){
      //$(this).text(i+1);
      $(this).find(“input[name=’f02′]”).val(i+1);
      });
      }
      }).find(“img.sort-row”).css({“cursor”:”move”}).disableSelection();

      // Also we need it to be re sorted at refresh
      $(this).find(‘td[headers=”SPC_ORDER”]’).each(function(i){
      $(this).text(i+1);
      });

      This is working on APEX 5.1
      If it does not work for you, let me know, I can check again (I just had to, because I did not remember all the things I did, and these are the ones that make sense) and see if I missed anything.
      Remember to not let your users to be able to sort any column by any other mean… or it will not work.

      Now if you want to “save” the order, you just need to add a save button, and when clicked do a process that should go through apex_application.g_f01 and g_f02 to assign to each id (1) the order (2).

      Regards.

      Reply

        • img class=”sort-row” style=”cursor: move;” alt=”” src=”/i/ws/sort.gif”

          hope this works now.

          Reply

    • In the heading i have:

      img title=”Drag rows to sort” src=”/i/ws/sort.gif”

      Reply

  17. Hello jorge,

    I use Oracle APEX 19.1.0.00.15, I add the library #IMAGE_PREFIX#libraries/jquery-ui/1.10.4/ui/minified/jquery.ui but I had this error:
    GEThttps://lc000020.info.ratp:8443/i/libraries/jquery-ui/1.10.4/ui/minified/jquery.ui?v=19.1.0.00.15
    [HTTP/1.1 404 Not Found 18ms]

    I remove this URL to skip this error but I had a json parsing error when I drag the element list (I use the javascript function of update with the f01 and without to_string), this is the error of Json parsing:
    Error: SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

    I don’t know if this error have a relation with the library not found in apex 19 server.

    Can you help me please, I need this drag and drop a lot and thanks.

    Regards,
    Rached

    Reply

  18. Hey Jorge,

    Any updates on ability to drag and drop rows across different reports? This functionality would be much more useful than sorting within the same report.

    Cheers!

    Reply

I love comments, write me a line