Implementing Drag & Drop in your APEX applications

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 & 5.1

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.

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

  8. Mahesh says:

    Hi Jorge,

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

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

  9. Derek Solis says:

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

    • Derek Solis says:

      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

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

        • Derek Solis says:

          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

          • Derek Solis says:

            Or maybe when the sortable widget starts up it stores the values?

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

          • Derek Solis says:

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

          • Derek Solis says:

            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

          • Hey, that’s great to hear. Very glad you figured it out!

          • Rick Schoppenhorst says:

            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

      • Jorgelina Aydar Paz says:

        Hi Derek, is it possible to share what you did so it would work with IGs? Thanks!

  10. gqali says:

    i am using 5.1 . this is working on 5.1

  11. Geert De Paep says:

    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.

    • 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");
      }

  12. Bruno Amorim says:

    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!

    • rimblas says:

      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.

  13. Isabell says:

    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

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

  14. Malli Ka says:

    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?

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

      • Malli Ka says:

        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!

  15. Santhosh Jose says:

    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.

  16. Nathan Peters says:

    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?

    • Jorgelina Aydar Paz says:

      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.

    • Jorgelina Aydar Paz says:

      In the heading i have:

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

Leave a Reply to Bruno Amorim Cancel reply