Sometimes your data needs to be explicitly ordered rather than sorted by an intrinsic property like a name, salary, or hire date. In these cases we introduce an additional number column like SORT_SEQUENCE into the data model and sort on that manually-assigned position number. For a volunteer project I’m working on, I needed the ability to explicitly order the speakers at a conference, and easily adjust it as the organizer moves speakers around in the lineup. Before implementing the feature in my actual application, I built a simpler example based on employee names first to get the basic idea working. This article explains how I used a new feature of APEX 22.1 called Declarative Action URLs along with a dynamic action custom event to let users easily adjust the explicit ordering by clicking on a source card and a target card in a cards region.
Sorting Cards by Sequence in Related Table
To more closely mimic the data model of my actual conference management application, I have a simple employee table EBA_DEMO_REORDER_EMP with just an ID and NAME column and a separate table called EBA_DEMO_ORDER_EMP_LINEUP that contains an EMP_ID column referencing the ID primary key of the main table, along with the SORT_SEQUENCE number column. Out of a possibly larger set of employee names, a certain set get introduced into the “lineup” and then their explicit ordering is established as part of that lineup.
I started by building a cards region based on the following query, that joins the two tables and orders by the SORT_SEQUENCE column in the employee lineup table. I configured card title to use the NAME column and the badge to use the CARD_NUMBER.
select e.id,
e.name,
row_number() over (order by lu.sort_sequence nulls last,
e.created)
as card_number
from eba_demo_reorder_emp_lineup lu
left join eba_demo_reorder_emp e on e.id = lu.emp_id
order by lu.sort_sequence nulls last, e.created
This quickly produced the basic card layout for the lineup of employee names.
Getting the Reordering Working
The lineup in my actual application can include hundreds of names, so I decided to let the user click on the card of the employee that needed to move, then click on the card of the place they’d like to move that employee. Using these two clicks, the end-user identifies first a “source” employee and then chooses a “target” employee position.
Inspired by the “source” and “target” naming, I created two hidden page items, P1_EMP_ID_TO_MOVE and P1_EMP_TARGET_POSITION, each with its Maintain Session State property set to Per Request (Memory Only). My strategy was to populate the first page item with the employee ID value of the initial click, and set the value of the second page item with the ID of the second click.
I wrote the PL/SQL package procedure to accept the source and target employee ids and perform the automatic reassignment of the SORT_SEQUENCE values of the affected rows:
create or replace package eba_demo_reorder is
procedure move_source_to_target(p_emp_source_id number,
p_emp_target_id number);
end;
With this backend business logic in place, the two remaining tasks were:
- Handle the card click to assign the hidden page item values, and
- Invoke the above procedure once both source and target were defined, refresh the cards region, and clear out the hidden page items again.
I chose to tackle the second task first using a Dynamic Action custom event to maximize the amount of APEX’s declarative functionality I could take advantage of.
Using a Custom Event to Maximize Low-Code
Assuming the two hidden page items have the source and target employee ids populated, executing the server-side PL/SQL code, refreshing the cards region, and clearing out the hidden page items are all actions I can easily accomplish using dynamic action steps in response to a custom event. As shown below, I created a dynamic action event handler for a custom event with event name move-source-to-target-da. The Selection Type is jQuery Selector and I used the page’sbody as the jQuery Selector to be the anchor element for the event listener. I chose the page body at the recommendation of my colleagues John and Stefan who reminded me that refreshing the cards region would remove any event listeners on the cards themselves. The body targets the event listener on a element of the page that contains the cards region, but which is not itself getting refreshed.
The dynamic action steps include an Execute Server-side Code step to run this block of code to perform the reordering, making sure to include both P1_EMP_ID_TO_MOVE and P1_EMP_TARGET_POSITION in the page Items to Submit list:
eba_demo_reorder.move_source_to_target(
p_emp_source_id => :P1_EMP_ID_TO_MOVE,
p_emp_target_id => :P1_EMP_TARGET_POSITION);
That is followed by a Refresh step to refresh the cards region on the page, and finally a Clear step to clear the values of the two hidden page items.
Wiring a Full Card Click to a Named Action
To tackle the remaining task of handling the click on the card, I added a card action and set the action Type to be Full Card. Following a suggestion from my colleague John, I used the new Declarative URL action invocation syntax he describes more in depth in his blog article Exploring new APIs in APEX 22.1. To put it to use, for the link type I chose Redirect to URL and provided a special URL syntax that invokes a named action, passing along one or more parameters in the process:
#action$move-source-to-target?id=&ID.
A URL of this syntax lets a click on my card invoke an action named move-source-to-target, passing along a parameter named id whose value is provided by the ID column of the current employee card.
Defining the named action at the moment requires a bit of JavaScript code. I added the following to my page’s Execute when Page Loads code block. If the P1_EMP_ID_TO_MOVE item is blank, it sets its value to the value of the id argument passed in. If P1_EMP_ID_TO_MOVE is set but P1_EMP_TARGET_POSITION is blank, then it sets the target and triggers the custom event named move-source-to-target-da that we configured above to perform the server-side PL/SQL call, refresh the cards region, and clear out the two hidden page items again.
apex.actions.add([
{
name: "move-source-to-target",
action: function( event, element, args)
{
/* If both are blank, set emp to move */
if (apex.items.P1_EMP_ID_TO_MOVE.value === '' &&
apex.items.P1_EMP_TARGET_POSITION.value === '') {
apex.items.P1_EMP_ID_TO_MOVE.value = args.id;
}
// If emp to move is set and target blank, set target
// and trigger the custom event to complete the job
// using declarative DA action steps to invoke the
// server-side PL/SQL package procedure to move the
// source emp to the slot where the target is.
else if (apex.items.P1_EMP_ID_TO_MOVE.value !== '' &&
apex.items.P1_EMP_TARGET_POSITION.value === '') {
apex.items.P1_EMP_TARGET_POSITION.value = args.id;
// Trigger custom event to perform the server-side call
$("body").trigger("move-source-to-target-da");
}
}
}
] );
My colleague Stefan gave me the great idea to use a custom event for this and to trigger it programmatically from the named action code. This allowed me to benefit from the simple action URL-wiring syntax as well as from the simplicity of using declarative dynamic action steps to perform the rest of the functionality.
The result is the click-click card reordering you see in this short video:
If you’d like to try out the working example, download the app from here.
Dive Into APEX 