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 start with a number.

We’ll use a markup like the following:

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

“Before Rows”

“Row Template 1″

“After Rows”

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

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

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

APEX 5.0

Make the report Sortable

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()

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 on the comments below.

The 'toArray' method will return an array with the ID of our lines. Then .toString() makes it easier to work with in 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. If we were using the old Notification Plugin from Oracle (that uses Gritter) we could generate a Notification to the user that the new order has been saved. You could consider using pNotify instead.
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.

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.

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:

Hi, I'm Jorge Rimblas. Father, husband, photographer, Oraclenerd, Oracle APEX expert, Oracle ACE, coffee lover, car guy, gadget addict, etc... I'm a Senior APEX Consultant with Insum Solutions, a consulting firm specialized in Oracle databases and the APEX development tool. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.

Posted in APEX, Javascript, Oracle, Web
13 comments on “Implementing Drag & Drop in your APEX applications
  1. I remember messing with this a long time ago. I’m glad you posted some nice instructions. Thanks!

  2. Erick Diaz says:

    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!

    • 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!

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

  3. Jean-Pierre says:

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

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

  4. Jean-Pierre says:

    Thanks Jorge.
    Looking forward to hear from you on this.

  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.

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

  6. Ross says:

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

    • 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…

  7. Ross says:

    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.

I love comments, write me a line