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.

Reordering Cards Using Clicks

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.

Cards region showing the explicitly ordered 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:

  1. Handle the card click to assign the hidden page item values, and
  2. 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.

Custom dynamic action event anchored to the page body.

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:

Example app for reordering cards with two clicks

If you’d like to try out the working example, download the app from here.

SmartFiltering Multiple Regions

A fellow Oracle employee wielding the power of APEX to solve an internal business problem for her team asked an interesting question on our internal discussion forum this week. Her app shows resources partitioned into three categories in side-by-side cards regions. She wanted to use a single SmartFilter region to filter the results showing up in these three regions, each based on the same underlying table with a slightly different WHERE clause.

Wherever possible, my brain tends to map questions onto the familiar EMP and DEPT tables to simmer a question down to its essence. So I imagined side-by-side cards regions with employees in three different departments as shown below.

Three EMP-based cards regions partitioned by department

Setting Up the Page

I started by setting up the three cards regions based on the EMP table, and including an appropriate DEPTNO=10 where clause in the “Accounting” region, DEPTNO=20 predicate in the “Research” region, and DEPTNO=30 in the “Sales” region. Then I added a SmartFilter region and positioned it in the Before Navigation Bar slot with the w100p class to make to stretch to 100% width and fill up the space left in the center of the navigation bar.

A Twist on The Gift That Keeps on Giving

By default, a SmartFilter region filters the results of only one other region on the page. However, my colleague Carsten’s article Add a Chart to your Faceted Search Page is the gift that keeps on giving, because it inspired me with the idea I used to achieve the result our fellow APEX user desired. Carsten explains therein how to use the open_query_context() function in the apex_region package in a pipelined table function that returns the results from another region’s data source, automatically taking into account the search filters currently applied by the end user in that region. The implementation uses functions in the apex_exec package to retrieve the data.

My twist on his idea was to create a pipelined table function get_region_results_pk_values()to return only the primary key values of a given region’s filtered results. Assuming a numeric primary key column, the get_region_results_pk_values() function can accept three parameters:

  1. Page id
  2. Static ID of a region on that page
  3. Name of the primary key column whose value we should return

The function’s pipelined result rows will each contain a NUMBER column named ID containing the primary key values identifying the rows that the filtered region is returning. For our employee-based example here, it will return ID values representing the EMPNO values of the filtered region’s query results. With this function in place, we can update the WHERE clause of the three employees cards regions to reference get_region_results_pk_values() in an additional predicate like this:

  • “Accounting”
    • deptno=10 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))
  • “Research”
    • deptno=20 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))
  • “Sales”
    • deptno=30 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))

Configuring the Filtered Region

To play the role of the SmartFilter region’s Filtered Region, we need an additional region based on the EMP table with no WHERE clause of its own. This is because using any of the Accounting, Research, or Sales cards regions would only filter the employee rows of one particular department and consequently its list of filtered EMPNO primary key values would not correspond to any employees in the other cards regions who, by design, belong to a different department.

So, I added an EmployeesResults classic report region based on the EMP table and configured this classic report region to be the SmartFilter region’s Filtered Region. I set the static ID of this region to RESULTS as referenced in the three WHERE clause function calls above.

A Chain Reaction of Filtering

When the end-user changes the filters on a SmartFilter, it automatically refreshes its filtered region. So in our example, the EmployeesResults classic report region will be refreshed. When this happens, we need the other three cards regions on the page to also get refreshed. Luckily, this is simple to achieve with a dynamic action event handler on the EmployeesResults region’s AfterRefresh event. I included three dynamic action steps in the TRUE branch of the event handler. Each one uses the built-in Refresh action to refresh one of the cards regions.

AfterRefresh dynamic action on filtered region refreshes employees cards regions

At this point, I had a working page with a SmartFilter at the top, whose search filters would restrict the rows in the filtered EmployeesResults region at the bottom, and whose filtered list of primary keys were correctly filtering the three separate Accounting, Research, and Sales cards regions as desired. However, seeing the classic report at the bottom was ruining the magic effect, so I sought out a way to hide it.

Classic report filtered region displays, ruining the magic

Hiding the Filtered Region

My first experiment was to set the Server-side Condition on the filtered EmployeesResults region to Never. This succeeded in hiding the classic report, but it also broke the chained filtering functionality being implemented by the dynamic action. I guess a region that’s not rendered on the server can’t be used to trigger events on the client, which I could understand.

For my second attempt, I went looking for properties of the classic report region that I could adjust to limit the data that it was retrieving. After all, I primarily cared about the filters applied to its data source, not seeing the results themselves in the classic report. These applied filters are important to correctly produce the filtered list of primary keys to filter the Accounting, Research, and Sales cards regions appropriately.

With the classic report EmployeesResults region selected, I studied the Attributes tab of the property palette and noticed a Number of Rows property which I tried setting to zero (0). I figured the classic report functionality related to pagination might be producing some content on the page, so I also set the Pagination > Type to the value No Pagination (Show All Rows). Since I knew that the SmartFilter region uses partial page refresh to update the filtered region, I kept the Partial Page Refresh switch in the ON position.

Setting the classic report to retrieve no rows & skip pagination, keeping partial page refresh

These changes got me very close to the desired result, but as shown below the column headings were still showing.

Almost There! The Filter chaining works, but the filtered region’s column headers still show

I managed to resolve this final issue by hiding the classic report’s column headings using the CSS property display:none. I accomplished this by adding a CSS class called hidden to the page-level CSS > Inline settings:

hidden {
   display: none;
}

After doing this, I configured the EmployeesResults classic report region’s Column CSS Classes to be the name of my hidden CSS class. After doing that, I had a working SmartFilter that was filtering the three separate cards regions, without showing the classic report region involved in triggering the chain reaction of filtering.

Filtering Code In the Demo

In practice, the APEX 21.2 demo app that you can download using the link below is based on an EMP-like table called EBA_DEMO_WORKLIST_EMP and the filtering code for the function is in a package named EBA_DEMO_WORKLIST whose specification looks like this. The package body looks very similar to Carsten’s blog article, but it’s simplified to only return the single primary key column of results in each row.

create or replace package eba_demo_worklist as 
    type eba_demo_worklist_pk_t_row is record (
        id number
    );

    type eba_demo_worklist_pk_t_tab is table 
        of eba_demo_worklist_pk_t_row;

    function get_region_results_pk_values( 
        p_page_id          in number,
        p_region_static_id in varchar2,
        p_pk_column_name in varchar2 )  
    return eba_demo_worklist_pk_t_tab pipelined;
end;
 

You can download the demo app from here, and install it into an APEX 21.2 (or later).

Trick to Reference Field Value in Dynamic Action on Grid Column

The Oracle APEX interactive grid region is a richly functional component with a JavaScript API for all aspects of its multi-row user interface and client-side data model, but this article concerns something simpler. I was recently building an application with an interactive grid and wanted the change in one date field in the row to update the value of another date field in the same row. I knew it would be simple to achieve, but there was a little trick I needed to learn from a colleague to crack the nut.

Consider the interactive grid below that displays an Order Date, Ship Date, and Bill Date for some line items. Let’s investigate the two easiest ways I could find to set the Ship Date and the Bill Date to the value of the Order Date whenever the end-user changes the Order Date.

Interactive grid with three date fields per row

To react to the change in Order Date, I right-clicked on the ORDER_DATE grid column in the Page Designer and created a dynamic action event handler named “When Value Changed” keeping the default “Change” event.

Creating a dynamic action on the ORDER_DATE interactive grid column

Since I wanted to set the value of the Ship Date field to the value of the Order Date in the current row, the “Set Value” type of action step felt like a good choice. I wanted it to affect the Ship Date field, so I chose the SHIP_DATE column name in the Affected Elements section. I needed the ORDER_DATE column in the current row to provide the value to assign. My first instinct was to look for a Set Type of “Column”, but that wasn’t listed among the types available. The next most likely candidate for Set Type was “JavaScript Expression”, so I selected that and used the expression: $v('ORDER_DATE') Alas, after trying this expression, I wasn’t seeing the desired result. Luckily, my colleague Interactive Grid master John Snyders taught me the trick I needed to get things working.

In the JavaScript world of the grid, the ORDER_DATE is the name of the column. Here what I learned I needed to reference instead was the value of the “column item”, the name of the particular item in the current row of the ORDER_DATE column. John explained that by setting the Static ID of the ORDER_DATE column to some name other than ORDER_DATE, I could assign a meaningful name to the column item for this column. Just to make it really clear what I was doing, I set the Static ID for the ORDER_DATE column to be COLUMN_ITEM_FOR_ORDER_DATE. Then I updated my “When Value Changed” dynamic action event handler’s “Set Value” action step to reference $v('COLUMN_ITEM_FOR_ORDER_DATE') as shown below for the JavaScript expression providing the value to set.

After setting Static ID of the ORDER_DATE column, referenced it as column item in the “Set Value”

After doing that, the Ship Date in the current row suddenly started updating interactively as I had hoped whenever the Order Date got changed. This technique illustrates the way to implement the requirement on the client-side without incurring a round-trip to the server to perform the assignment.

To assign the Bill Date, I wanted to show off the other technique I experimented with. Building on the same dynamic action event handler, I added a second action step with action type “Execute Server-side Code”. For the PL/SQL code, as shown in the figure below, I simply used the page item names as bind variables and APEX automatically interprets them as the values in the current row:

:BILL_DATE := ORDER_DATE;

As the name of the action type implies, this approach executes the PL/SQL on the server-side, so we need to remember to configure the Items to Submit to include the ORDER_DATE and the Items to Return to include the BILL_DATE as shown below.

Setting interactive grid column Bill Date to the value of Order Date using Server-side PL/SQL

In this second server-side approach, I was able to directly reference the name of the ORDER_DATE and BILL_DATE columns without involving the Static ID name. That additional trick was only required for use in the JavaScript expression shown earlier.

Of course, since the “Set Value” using the JavaScript Expression is performed completely on the client-side in end-user’s browser, it is faster than the Execute Server-side code technique that requires an additional HTTP round-trip to the server to perform the assignment. It’s good to understand both approaches and pick the one that meets your needs best in your own applications.

You can download the sample application if you want to check out the technique in your own APEX workspace.

Checking If a Region’s Data Source Returned Any Data

Oracle APEX lets application developers easily use any data they need. A region’s data can come from a local table or view, the rows returned by a SQL query, a REST service’s response payload, or rows from a table, view or query sent to a remote, REST-enabled SQL database. APEX also supports the conditional visibility of regions and page items using a declaratively-configured “server-side condition”. Let’s consider a use case that combines these features to show or hide a region based on whether it contains any data. In other words, if the region’s data source returns rows, then show it, otherwise hide it.

At first, the solution sounds simple. One of the Server-side Condition types is “Rows Returned”. This configures the region to be processed and displayed when at least one row is returned from the SQL Query that you type into the box below. This works great for regions based on local data that the user cannot filter further, but if the end-user can filter the data using interactive report/grid filtering, a faceted search, or smart filters, how can this “Rows returned” query correctly reflect the end-user’s active filters applied? What’s more, if the region uses a REST service or REST-enabled SQL data source, then what SQL Query do we type in here? The APEX engine executes the Server-side condition’s “Rows returned” query against the local database, so it won’t be useful to determine if the remote data source returns data. So we need another strategy when a region employs remote data or might be actively filtered by the end-user.

Server-side Condition “Rows returned” query is executed against the local database

I didn’t find any ready-made APEX API to inquire whether a region’s data source returned data, so I set out to write my own. Luckily, I had recently read my colleague Carsten’s great article Add a Chart to your Faceted Search Page. There he explains a technique to produce a chart of exactly the same filtered results as another report region on the page that has been filtered using a faceted search region. The crux of his clever plan involves using the open_query_context() function in the apex_region package to obtain a logical cursor over that region’s data, which can be used to process its data one row at a time. The cursor-like “query context” returned by this API hides the underlying details of how the data is retrieved, so it works the same way no matter how the region is configured to source its rows. It also conveniently encapsulates the current state of the user’s applied filters, if any.

In Carsten’s article, the chart needs all of the other region’s rows to visualize them graphically. However, for our purposes we can use a simplified version of his approach that just attempts to retrieve a single row to see whether or not the region has data or not. The has_data() function in the region_utils package below implements the idea. When someone calls has_data('CHART'), for example, passing in the static id of a region on the current page, the function…

  1. looks up the region id using the region’s static id and the id of the current page
  2. opens a query context “cursor” on the region’s data source indicating that it wants to retrieve at most one row (p_max_rows=>1),
  3. tests whether the cursor has at least one result row by calling next_row(),
  4. closes the context, and
  5. returns the result of whether or not a first row was returned.
create or replace package region_utils is
  function has_data(p_region_static_id varchar2) return boolean;
end;
/
create or replace package body region_utils is
    function boolean_to_string(p_value boolean) return varchar2 is
    begin
        return case when p_value then 'TRUE' else 'FALSE' end;
    end;
    --
    function has_data(p_region_static_id varchar2) return boolean
    is
        l_context     apex_exec.t_context;
        l_region_id   apex_application_page_regions.region_id%type;
        l_return      boolean;
        l_page_id     number := v('APP_PAGE_ID');
        l_app_id      number := v('APP_ID');
    begin
        -- 1. Lookup region id using static id & page id
        select region_id
        into l_region_id
        from apex_application_page_regions
        where application_id = l_app_id
          and page_id        = l_page_id
          and static_id      = p_region_static_id;
        -- 2. Open "cursor" on region's data source (max 1 row!)
        l_context := apex_region.open_query_context(
                       p_page_id   => l_page_id,
                       p_region_id => l_region_id,
                       p_max_rows  => 1);
        -- 3. Test if there is a next (i.e. first) row.
        l_return  := apex_exec.next_row( p_context => l_context );
        -- 4. Close the context "cursor"
        apex_exec.close( l_context );
        apex_debug.info('--- region_utils.has_data(''%s'') = %s',
                        p_region_static_id,
                        boolean_to_string(l_return));  
        -- 5. Return whether we got a row or not
        return l_return;
    exception when others then
        apex_exec.close( l_context );
        raise;
    end;
end;
/

With this package in place, we can configure a chart region having static id of CHART to have a Server-side Condition using the “Expression” type as shown below to include the single line of PL/SQL code that uses our new has_data() function:

Using new has_data() function in a PL/SQL Expression for Server-side Condition

This approach worked great for a page that did not need the chart region’s data to be refreshed based on actions the end-user takes while using the page. When I tried to employ it on a page with interactivity, there was a learning opportunity awaiting me.

Consider the example below where the user can choose a department from a select list and immediately see a pie chart of all of the employees salaries in that department. Here both the shared component LOV for department names and the employees chart region below are based on REST-enabled SQL using a remote database.

Refreshing the chart region when Department changes

What I discovered from trying the Server-side Condition mentioned above with this more dynamic example was that when the condition evaluates to false, the chart component was left out of the page completely (as expected, perhaps!) so in some cases it was not present in the browser HTML page for my dynamic action on the select list to interact with.

To get the more dynamic use case working how I wanted it, I abandoned the idea of using the Server-side Condition to hide the chart, and resorted instead to using dynamic actions to conditionally hide and show the chart region based on the result of calling region_utils.has_data('CHART'). I introduced a hidden page item P1_CHART_HAS_DATA and added an additional function to my region_utils package has_data_as_string() to return the result of the has_data() function as either the string TRUE or FALSE for easy storage in this new, hidden page item. Then I used a Client-side Condition on several dynamic action steps to test the value of this P1_CHART_HAS_DATA page item using the JavaScript expression:

$v('P1_CHART_HAS_DATA') === 'TRUE'

The figure below shows the four-step dynamic action event handler for the Change event on the P1_DEPTNO select list page item. The first step uses has_data_as_string('CHART') to set the value of the hidden P1_CHART_HAS_DATA page item, submitting the P1_DEPTNO page item value since the region’s query depends on it. Then the subsequent three steps use a Client-side Condition JavaScript expression to conditionally Refresh the chart region if it had any data to graph, conditionally Show the chart region if it had data, and conditionally Hide the chart region if it did not have data. In this last situation, the JavaScript expression tests if $v('P1_CHART_HAS_DATA') === 'FALSE' while the previous two steps tested for 'TRUE'.

Dynamic action event handler on the Change event of the P1_DEPTNO select list to set value of P1_CHART_HAS_DATA and conditionally refresh, show, or hide the chart

After making the Hide dynamic action step Fire on Initialization to initially hide the chart region when the P1_DEPTNO select list has its default null value, this combination achieved the result I was looking for.

You can download the example app, and during import you will need to configure its REST-enabled SQL endpoint named slc12kos to point to your own REST-enabled SQL endpoint that has the EMP and DEPT tables available.

One tip my colleague Carsten mentioned when I was asking him questions about this approach was that if the chart region’s data were coming from a REST service instead of a REST-enabled SQL source, that would give you some additional options to use APEX’s periodic REST synchronization feature or REST source caching to reduce the number of HTTP requests between the APEX engine and the remote data endpoint when rendering the chart region in this example.

The final source for the region_utils package looks like the code below. For more information on enabling REST-Enabled SQL in your ORDS listener, check out Tim Hall’s video on Setting up REST-Enabled SQL over on his Oracle-Base blog.

create or replace package region_utils is
  function has_data(p_region_static_id varchar2) return boolean;
  function has_data_as_string(p_region_static_id varchar2) return varchar2;
end;
/
create or replace package body region_utils is
    function boolean_to_string(p_value boolean) return varchar2 is
    begin
        return case when p_value then 'TRUE' else 'FALSE' end;
    end;
    --
    function has_data(p_region_static_id varchar2) return boolean
    is
        l_context     apex_exec.t_context;
        l_region_id   apex_application_page_regions.region_id%type;
        l_return      boolean;
        l_page_id     number := v('APP_PAGE_ID');
        l_app_id      number := v('APP_ID');
    begin
        -- 1. Lookup region id using static id & page id
        select region_id
        into l_region_id
        from apex_application_page_regions
        where application_id = l_app_id
          and page_id        = l_page_id
          and static_id      = p_region_static_id;
        -- 2. Open "cursor" on region's data source (max 1 row!)
        l_context := apex_region.open_query_context(
                       p_page_id   => l_page_id,
                       p_region_id => l_region_id,
                       p_max_rows  => 1);
        -- 3. Test if there is a next (i.e. first) row.
        l_return  := apex_exec.next_row( p_context => l_context );
        -- 4. Close the context "cursor"
        apex_exec.close( l_context );
        apex_debug.info('--- region_utils.has_data(''%s'') = %s',
                        p_region_static_id,
                        boolean_to_string(l_return));  
        -- 5. Return whether we got a row or not
        return l_return;
    exception when others then
        apex_exec.close( l_context );
        raise;
    end;
    function has_data_as_string(p_region_static_id varchar2) return varchar2 is 
    begin
        return boolean_to_string(has_data(p_region_static_id));
    end;
end;
/

Interactive, User-Configurable Card Width #JoelKallmanDay

Create a cards region with interactive card width selector, saving user’s preference across logins.

We miss you, Joel.

Everyone in the Oracle APEX community

Oracle APEX card regions let your users browse and act on a grid of tiles, each representing a row of data. The card region directly taps into your end user’s intuition of browsing their mobile phone’s photo library, especially when the cards feature an image, so it’s a compelling way to present data to users.

The card region’s grid style resizes automatically to the screen space available, but by default end users can’t influence the size of each tile in the grid. Read on to learn how to let your users adjust the card width interactively and remember their choice as a preference across logins. At the end, you’ll find a step-by-step video tutorial and downloadable sample application, but we’ll explore the key ideas behind the technique first.

Overview of Strategy

To implement the feature, you’ll add the following to your page with the card region:

  • A select-list page item showing list of available sizes (e.g. Small, Medium, Large)
    • Having corresponding values of the pixel widths 180px, 220px, 300px
    • Defaulted to the static value for the Medium size (220px)
    • Configured with Maintain Session State setting of Per User (Disk).
  • A dynamic action “trigger” for the select list’s Change event with actions:
    1. Execute JavaScript to update the CSS variable controlling the card size
    2. Execute Server-side Code to save the updated value to the APEX session state

What’s a CSS Variable?

A CSS variable is a custom property whose name is prefixed by a double-hyphen (e.g. --preferred-button-width). It can be associated with any element in a page, either explicitly or implicitly by being associated with a class applied to that element. Any CSS expression can reference the value of a variable by using the syntax var(--variable-name) . The usage can also provide a default value to use in case the variable reference has no value of its own by including a second argument like var(--variable-name, defaultValue) . So, a CSS class named myButton could set the width property to the value of the --preferred-button-width variable (providing a default of 80 pixels) like this:

.myButton {
  width : var(--preferred-button-width,80px);
}

If the same variable exists on multiple elements in the page, the value of the most specific occurrence is used. To provide a global default value for a variable, you can set a value for it on the special :root pseudo-class. If no more specific element in the page provides a value, then the one from the root is used.

As we’ll see below, the Universal Theme style class that defines the card region’s grid layout uses a CSS variable to control the size of the cards in the grid. So setting the right variable to a user-chosen value on the appropriate scope for your needs is the crux of the solution. So let’s explore which variable to set and consider on what context makes sense to set it.

Which Variable Do We Need to Set?

While a page containing a grid region is displayed, using the Chrome developer tools to inspect one of the cards (and clicking to enable the CSS-grid related style properties) we can observe that the card items grid layout is setup by this CSS class:

.a-CardView-items--grid {
    grid-template-columns: 
         repeat(auto-fill,minmax(var(--a-cv-item-width,320px),1fr));
}

At first glance, it’s admittedly cryptic, but let’s unpack what it says. This style property defines the grid-template-columns layout to be a repeating set of columns that auto-fill the horizontal space available with uniform-sized grid cells. The browser computes the width of each grid cell automatically so it falls in the range between the values passed to the minmax() function. The first argument, that is the minimum width value, is given by the value of the CSS variable named --a-cv-item-width (or a default to 320 pixels if the variable is not defined). The second argument providing the maximum card width is one fractional unit (1fr), which represents the width of one column in the layout taking into account a spacing between grid cells, too. In short, if we assign the user-preferred card width value to the CSS variable --a-cv-item-width then the grid will instantly react to layout the grid with cards having that width (or slightly bigger to make each grid cell uniformly sized).

On What Context Do We Set the CSS Variable?

We have at least two sensible choices for the context on which to set the card size CSS variable:

  1. On the card region itself, after assigning it a static id in the App Builder, or
  2. On the “global” root context

Choice 1 imposes the user-preferred card width on just the region on which it’s assigned, whereas choice 2 sets the user-preferred card width so that all card regions in the application will abide by it.

The code examples that follow assume you’ve created a page item named P3_CARD_SIZE on the page with the card region, and that the values for the P3_CARD_SIZE select list page item are one of 180px for Small, 220px for Medium, and 300px for Large.

JavaScript to Set the CSS Variable on a Region

After configuring a static id on your card region (e.g. BooksCardRegion), your dynamic action on the P3_CARD_SIZE page item’s Change event can use the following line of JavaScript to change the CSS variable --a-cv-item-width to the new value of the P3_CARD_SIZE page item:

$('#BooksCardRegion').css('--a-cv-item-width',$v('P3_CARD_SIZE'));
JavaScript to Set the CSS Variable Globally

Your dynamic action on the P3_CARD_SIZE page item’s Change event can use the following line of JavaScript to change the CSS variable --a-cv-item-width on the global “root” context to the new value of the P3_CARD_SIZE page item:

$(':root').css('--a-cv-item-width',$v('P3_CARD_SIZE'));

Pushing the Interactive Card Size Change Immediately to the Server

The change to the CSS variable is made in the browser when the dynamic action reacts to the user’s change of the select list page item, and immediately takes visual effect for the end user in their browser. However, to immediately force the value change to be saved in the APEX session, add an additional dynamic action step to Execute server-side Code and which specifies the P3_CARD_SIZE as one of the page items to send to the server. Since it doesn’t need to perform any other server-side logic beyond pushing the values, you can simply use the “do nothing” PL/SQL instruction NULL; to enter into the required PL/SQL script property. If the page item’s Maintain Session State setting is configured to “Per Session (Disk)” then the user’s preference will persist for the duration of the session. If instead it’s set to “Per User (Disk)“, the setting will survive across subsequent user logins.

Step by Step Tutorial Video

NOTE: The video illustrates setting the CSS variable on the :root context, while the downloadable example app illustrates setting the variable on the card region. See above for a consideration on which is appropriate for your use case .

Sample Application

You can download an Oracle APEX 21.1 example application (containing a supporting objects installation script for two sample tables PNL_THEROUX_BOOKS and PNL_PUBLISHERS) from here.