Friday, 1 November 2013

Exchange Table Data with Drag and Drop

This post will describe how you can use jQuery drag and drop to move records between two tables. In my example, as always I’ve used the EMPLOYEE and DEPARTMENT tables. The drag and drop effect will help to transfer employees from one department to the other.

The setup is fairly simple: first of it requires two tables on employees per department, here I’ve used the employees from department number 10 and 20. For the sake of simplicity, I’ll use a simple query that doesn’t include all employee details:

To make each report row draggable, we need to be able to simply identify each row, so we add a class to each report row for both tables:

We want these functions to add the classes on page load, so either put the code in a page load dynamic action(for older Apex releases), or put it under “Execute when page loads” in the JavaScript section of the page attributes.

Now that we have two reports, each with rows of employees, which have a few distinctive classes, we can almost add the drag and drop effect. Almost. When we can drag table rows around, we need some mechanism to track which employees have been moved, so that we can actually save our changes. For this we will use two hidden page items, one under each report. In these page items we will store all employees that we need to update. Also we need to create a page button to submit our page. We need this for the update process that we will create later on.

With everything in place we can add the drag and drop effect. The code looks as follows:

You can put this code in your page’s html header or in a separate JavaScript file if you like. Note that I’ve included the jQuery and jQueryUI libraries in the code. If your Apex version is up to date, you probably do not need to include these files, as they are distributed with your Apex install.

The first function of the previous code segment makes all employee records draggable; here we use the ‘emp’ class which we added in the previous paragraph. Each table row now can be dragged around so next we need to make the tables droppable. This In essence means that each table can receive a draggable table row. That each table row can be dropped in a table is defined by the ‘accept: “.emp”’ part in the droppable functions. What should happen when you drop a table row in a table is defined in the ‘drop: function(…’ part. Here each dragged row is added as last row to the table and next the empno of that row is added to a colon delimited string in the hidden page item.

At this point you should be able to drag rows from table to table. Also all the empno’s of employees that have been moved from table to table, have been stored in two separate hidden page items, one for deptno 10 and one for deptno 20. The last thing we need to do is to create an update procedure to save our changes to the database. Here I’ll use a page submit process, but you can also write an AJAX process if you prefer to handle the updates asynchronous. The process should look something like this:

The update process casts each string from the hidden page items to an array. Next we loop over each array and change the department number for the employee.

With this final step we have everything in place. We can identify table rows with a class. We use that class to make the rows draggable. We have two page items to store the empno’s of each table row. Also we have for each table a drop effect and which lets the tables receive new draggable rows and we have a drop function which places each moved row at the end of the table and stores the empno’s in the page items. Last, we have a process to update the tables and change the department numbers for each transferred employee.

To view a working demo, please visit my demo application. For more information on drag and drop view this old post of mine, or go to the jQueryUI site.

Have fun!