Managing Intersection Rows with Drag & Drop

Overview

After publishing my last article about reordering cards using clicks, Peter Raganitsch suggested on Twitter that I try the same use case using drag and drop with the free FOS – Drag and Drop plugin. While exploring the idea, I expanded the use case to more closely resemble my conference organizer app where each yearly edition of a conference can have a different “lineup” of speakers. So I introduced a Lineup table into the sample app’s data model and document here how I created a page to reorder the employees in one or more lineups, including the ability to add unassigned employees to the current lineup or remove an employee from the lineup. The result is a UX pattern that may work well for other situations involving the data in an intersection table like EmpLineup below when the user needs to impose a preferred ordering on them. The expanded data model looks like this now:

Data Model for the Emp Lineup Reordering sample application

Choosing the Lineup to Work On

I started with a page containing a Cards Region named Employee Lineup based on the following query that joins the EmpLineup and Emp tables to present the names of the employees in a lineup:

select e.id, e.name
from eba_demo_dragdrop_emp_lineup lu
left join eba_demo_dragdrop_emp e on e.id = lu.emp_id
order by lu.sort_sequence nulls last, e.created

Then, to allow the user to choose which lineup she wants to work on, I added a select list P1_LINEUP page item to a static content region above the cards region, with the simple List of Values SQL query to show the available lineups:

select name, id
from eba_demo_dragdrop_lineup
order by name

With the “Lineup Chooser” select list in place, I updated the cards region’s SQL query to add a WHERE clause to filter the intersection table rows to only retrieve the ones related to the selected lineup, and ensured that the P1_LINEUP page item name was listed in the Page Items to Submit list for the region.

select e.id, e.name
from eba_demo_dragdrop_emp_lineup lu
left join eba_demo_dragdrop_emp e on e.id = lu.emp_id
where lu.lineup_id = :P1_LINEUP
order by lu.sort_sequence nulls last, e.created

To immediately see the list of employees in the newly selected lineup reflected below in the cards region, I added a dynamic action event handler named On Change Lineup to the P1_LINEUP page item to handle its Change event, and configured its first action step to use the built-in Refresh action to refresh the Employee Lineup region.

Since the end-user will be changing the value of the chooser, APEX’s default behavior is to warn them if they attempt to navigate away from the page with unsaved changes. In this case, since I know the value of P1_LINEUP is only used as a query filter, I set the page-level Warn on Unsaved Changes switch to “off” to avoid this warning.

This gave me the following in-progress page letting the user choose which lineup to work on:

Cards region filtered by “Lineup Chooser” select list above

Adding Employees to the Lineup

To allow the user to add new employees to a lineup, I started by adding a button named ADD_TO_LINEUP to the page, and set its position to Before Navigation Bar. I’d used modal dialog pages many times before, but wanted to learn how to also use inline dialogs in APEX, and this use case seemed like a good one for an inline dialog. I wanted to quickly present a shuttle control in a modal dialog when the user clicks on the (Add to Lineup…) button, that shows employees that are not yet assigned to the current lineup and lets the end-user to pick one or more employees to add.

After not finding any obvious way to directly create an inline dialog in the APEX builder, my next intuition was that doing so must involve creating a region. But I also failed to find any “Inline Dialog” region type. At this point, I figured it must be a static content region with a particular template setting. Seeing a position heading in the rendering tree called Dialogs, Drawers, and Popups gave me a clue that the region position needed to be configured to fall in this category. So I added a static content region “Add Employees” to contain the shuttle page item, set its Position to Dialogs, Drawers, and Popups and set its Appearance > Template to Inline Dialog.

I added a page item P1_EMP_IDS_TO_ADD to this new static region and set the type to Shuttle. I set its Label to “Unassigned Employees”, and Template to Optional – Above to get what looked best to me from previous shuttle experiments I’d done. To have the shuttle show only employees who were not yet assigned to the current lineup, I configured the shuttle’s List of Values SQL query as follows and set the Display Extra Values to the off position:

select name,id
  from eba_demo_dragdrop_emp
 where id not in (
   select emp_id
     from eba_demo_dragdrop_emp_lineup
    where lineup_id = :P1_LINEUP
 )
order by name

To have the (Add to Lineup…) button created above open the inline dialog, I added a dynamic action event handler to its Click event, configured the first action step to clear the value of the P1_EMP_IDS_TO_ADD page item, a second action step to refresh the page item to update its list of available employees, and a third action step to show the inline dialog.

Initially tried to show the dialog using the Show dynamic action step to show the corresponding “Add Employees” region, but when that didn’t work, I looked again through the list of built-in actions available and tried the Open Region action instead which worked great.

Back in the “Add Employees” inline dialog’s static region, I added a button named CANCEL to the Close position of the region, and a dynamic action event handler for its Click event to perform the corresponding action step of Close Region.

To process the addition of any selected employees in the shuttle, I added a button named ADD to the Create position of the inline dialog region, and added a dynamic action event handler for its Click event having three action steps:

  1. Execute Server-side Code
  2. Close Region (Region: Add Employees)
  3. Refresh (Region: Employee Lineup)

The PL/SQL code for the server-side code in step 1 looks like this, using the handy SPLIT_NUMBERS() function in the APEX_STRING package to parse the colon-separated list of employee ids from the shuttle that need to be inserted into the eba_demo_dragdrop_emp_lineup table as intersection rows. I also made sure that both P1_LINEUP and P1_EMP_IDS_TO_ADD were listed in the Items to Submit field.

insert into eba_demo_dragdrop_emp_lineup(lineup_id,emp_id)
select :P1_LINEUP,column_value
from apex_string.split_numbers(:P1_EMP_IDS_TO_ADD,':');

With these steps I now had a working add dialog to add one or more employees not currently in the lineup:

Inline dialog with shuttle to add unassigned employees to the current lineup

Deleting an Employee from the Lineup

I thought the easiest way for a user to remove an employee from a lineup would be to offer a little “x” icon on each card the user could click. A trick I learned while working on the last article came in handy again here. I decided to use a link with the new named action syntax in its href attribute to trigger a named action. This named action would store the value of the action parameter named id in a page item, and then trigger a custom dynamic action event handler to perform the server-side code to remove the employee from the lineup. This combination let me use a maximal amount of declarative APEX functionality.

I started by adding a hidden page item P1_EMP_ID_TO_REMOVE to the page. This will be used to store the id of the employee to remove from the lineup. Next I defined a custom dynamic action event named remove-emp-from-lineup-event with Selection Type of jQuery Selector and jQuery Selector of body. It contains two dynamic action steps:

  1. Execute Server-side Code
  2. Refresh (Region: Employee Lineup)

The PL/SQL block for the first action step contains just the delete statement below, with P1_EMP_ID_TO_REMOVE and P1_LINEUP both listed in the Items to Submit.

delete from eba_demo_dragdrop_emp_lineup
where emp_id = :P1_EMP_ID_TO_REMOVE
and lineup_id = :P1_LINEUP;

Next, I defined a named action remove-emp-from-lineup-action by adding the following block of JavaScript to the Execute When Page Loads section of the page:

apex.actions.add([
{
  name: "remove-emp-from-lineup-action",
  action: function( event, element, args)
  {
    // Store the value of the EMP id from the action args
    apex.items.P1_EMP_ID_TO_REMOVE.value = args.id;
    // Trigger custom event to perform the server-side call
    $("body").trigger("remove-emp-from-lineup-event");
  }
}

To complete the job, I updated the HTML Expression for the card region’s Body section and changed it from:

<strong>&NAME.</strong> 

To add the following <a> element before the <strong> element. Its class attribute references the name of the fa-remove icon I wanted to use, and its href attribute contains the special (new in 22.1) named action link syntax to trigger the action named remove-emp-from-lineup-action passing along a parameter named id with the value of the ID column in the current row (i.e. card):

<a class="fa fa-remove" 
   href="#action$remove-emp-from-lineup-action?id=&ID."
></a><strong>&NAME.</strong> 

These steps produced the clickable remove icon next to the employee name in the body of each card, and clicking the “x” on any card removes it from the lineup. Since I also wanted clicking on the main card area to open a normal modal dialog to review/edit the employee’s details, I changed from using the “Full Card” action type to instead use the “Media” card action for that. In this way, the end-user can perform multiple actions on each card in a non-intrusive way.

Clickable “remove” icon on every card triggers named action to do the work

Reordering Employees Using Drag and Drop

Saving the most interesting feature for last, I downloaded the FOS plug-ins from here. The download was a sample app illustrating the use of all the FOS plug-ins, but my interest for this article was just the FOS – Drag and Drop one. After importing the FOS sample application, I visited its Shared Components > Plug-ins page in the APEX builder and exported just the FOS – Drag and Drop plug-in to a SQL file whose name APEX automatically chose for me. Importing the plug-in into my own app involved simply clicking the (Import>) button from the Plug-ins shared component page and choosing the name of this SQL file and completing the wizard, as shown below:

Importing the FOS – Drag and Drop plug-in into my own application

I enabled the drag and drop functionality on my page by adding a new dynamic action named “On Page Load Setup Drag/Drop” to the Page Load section and setting the Action of its first action step to FOS – Drag and Drop [Plug-In]. This plugin configures the drag and drop event listeners without having to understand what that means or how it works. I appreciated this, since I’m not an accomplished JavaScript programmer myself.

In the Affected Elements section of this action step, I set the Selection Type to Region and chose “Employee Lineup” from the Region list. This tells the plug-in that you want to have the drag and drop effect be applied to the cards in this region. In the sample app that FOS provides, they show how drag and drop can be easily applied to lots of other situations in APEX as well.

I noticed that the Mode was already set to Sort, which is the behavior I wanted. To configure the business logic that will execute when one card it dropped in the place of another card, I set the Drop Action to Execute PL/SQL Code and entered the following one line of PL/SQL in the Execute PL/SQL Code field, remembering to list P1_LINEUP in the Page Items to Submit field below it:

eba_demo_dragdrop.move_source_to_target(:P1_LINEUP,:DRAG_ID,:DROP_ID);

The :DRAG_ID and :DROP_ID bind variables are automatically set by the plug-in and evaluate to the primary key of the dragged card and that of the card currently in the dropped position, respectively. This was almost too easy.

Running my page, I was able to drag an employee card and drop it, but I noticed that when I’d refresh the page the order of the employees had not actually changed. To debug the problem, I added in one line of code to my move_source_to_target() package procedure to log the values of the source and target employee ids:

apex_debug.info('lineup=%s,source=%s,target=%s',
                 p_lineup_id,
                 p_emp_source_id,
                 p_emp_target_id); 

After enabling Debug mode for the page, this info() log message allowed me to understand that the drag and drop plug-in was passing values that were not my primary keys that I expected. This gave me a clue to check the “Employee Lineup” cards region to make sure I had correctly configured a column as the primary key, and in fact I had not done so yet. For the drag and drop to work as expected, I needed to select the cards region, activate its Attributes tab, and set the Primary Key Column 1 list to ID to let APEX know which column represented the primary key. After doing this small tweak, the drag and drop was working perfectly…

…until I tried changing the Lineup chooser select list to switch to working on a different lineup, or until I added some new employees to the current lineup, or until I removed an employee from the current lineup. After doing any of these actions that resulted in refreshing the cards region, the drag and drop behavior would cease to work.

Getting Drag and Drop to Work After Region Refresh

Googling around for a solution, I found an article that said I needed to re-execute the FOS – Drag and Drop [Plug-in] action step after the region refresh. It explained that the act of refreshing the region caused an AJAX request that resulted in changing the HTML elements in the cards region to which the drag and drop event listener was listening. This made sense, so I dutifully repeated the steps I did to originally configure the Page Load event action step above to “wire-up” the drag and drop event listener after each region refresh. I did that by adding an additional dynamic action step to each dynamic action step sequence where I was refreshing the cards region. However, to my chagrin, this additional effort did not remedy the problem.

Luckily, since I had met Peter Raganitsch in person at the APEX Alpe Adria conference back in April 2022 in Maribor, Slovenia, I felt comfortable contacting him by email. I let him know I was working on implementing his suggestion, but that I’d hit an issue I wasn’t sure how to debug. To my surprise and delight, he responded in short order with a solid tip that I probably would not have figured out on my own.

In his reply, he explained that I was doing the right thing to re-wire the drag and drop listener but that I needed to use that action step in a different dynamic action event handler responding to the Page Change [Cards] event instead of using the action step as part of the same action sequence that triggered the region refresh in the first place.

This has to do with timing of actions, meaning the “Refresh” of the Cards region wasn’t finished when you re-enabled Drag & Drop. Using the “Page Change [Cards]” event you make sure the Cards region is finished re-querying (or paginating). As DB and PL/SQL developers we often think about procedural and serial execution. In JavaScript that doesn’t always apply due to asynchronous operations. i.e. DA “Refresh” sends an asynchronous call to the DB and continues with the next action without waiting for the refresh result.

Peter Raganitsch

Enlightened by this new kernel of wisdom from Peter, I removed the three “re-wire the drag and drop” action steps I had added above. Then, on the Dynamic Actions tab, I added a new dynamic action event handler named “On Page Change Setup Drag/Drop Again”, set its When section’s Event to Page Change [Cards]. Repeating my steps done above, I set the Action of its first action step to FOS – Drag and Drop [Plug-In]. Again I set the Selection Type to Region and chose “Employee Lineup” from the Region list. Leaving the Mode set to Sort, I again set the Drop Action to Execute PL/SQL Code and configured the Execute PL/SQL Code to the same one-line of code as above, again remembering to list P1_LINEUP in the Page Items to Submit field below it.

After making that change, the drag and drop worked flawlessly, not only when the page renders originally, but also after adding new employees to a lineup, removing employees from a lineup, or switching to work on a different lineup. My final page looked like this:

Final lineup management page with add, remove, and drag/drop reordering functionality

Thanks again to Peter Raganitsch for the suggestion that resulted in my learning many new things about APEX and plug-ins. In addition, double thanks for the tip that helped me understand the right event handler to use to avoid timing problems due to JavaScript’s asynchronous nature when redefining event listeners after AJAX-driven page changes occur, like region refreshing.

If you’re interested in checking out the working sample, you can download it from here for use in APEX 22.1 or later.