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