Implementing Drag & Drop in your APEX applications

Datetime:2016-08-23 02:24:27          Topic: Ajax  Oracle  jQuery           Share

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 myvideos,Part 1 covers the creation of the template that is mentioned below. In installment 004 I’ll be following these instructions, so if you have any questions, hopefully, the video will cover them.

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:

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

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

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'}).toString();
 apex.server.process ( "UPDATE_ORDER", 
  {x01:results},
  {
    success: function( pData ) 
    {
      // apex.jQuery.gritter.add({title: "Todos",text:"New order saved."});
      apex.event.trigger(pRegionID, 'apexrefresh');
    },
    dataType: "text"
  }
 );
}
 

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 x01 with our string of comma delimited ID in the new order we want.

declare
  l_array apex_application_global.vc_arr2;
  s number;
begin
  l_array := apex_util.string_to_table(apex_application.g_x01,',');
 
  for i in 1..l_array.count loop
  s := i * 10;
  update app_todo
      set display_seq = s
    where id = to_number(l_array(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.

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
      , update: function(event,ui) { updateDisplaySeq(r); }
  }); 
}
 

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'}).toString();
 




About List