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.

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.

Adding Tags Using Multi-Value PopupLOVs and Smart Filters

Overview

Google’s Gmail was the first app I remember that popularized users’ inventing their own descriptive labels and applying them as “tags” to data. Instead of moving an email from the “Inbox” folder into one named “Charitable Donations 2019 USA”, for example, it encouraged me to tag the message with multiple, distinct labels like “2019”, “taxes”, “charitable donations”, and “USA”. While not initially as intuitive as folders, this approach quickly proved its value. It let me quickly locate mails related to a particular year, to charitable donations, to taxes in general, or to the US, or any combination of those criteria.

For the same reasons, a flexible tagging facility comes in handy for many kinds of data we work with everyday. Whether it’s pictures in a photo library, products in an online store, or books in your collection, tags that help users quickly find what they are looking for are a boon. When combined with Oracle APEX’s powerful faceted search and Smart Filters capabilities, it packs a powerful productivity punch.

In this article, I explain the technique I used over the holidays to add a flexible tagging facility to my art tracker application using a no-code approach that takes advantage of APEX’s multiple value support in popup LOV page items and Smart Filters. Here, we’ll apply the approach to a simple application that tracks books, publishers and authors. You can find the link to download the example application at the end of the article.

Book Finder page featuring Cards region showing title, authors, publisher, and tags

Multi-Value PopupLOV for Tags

The BOOK_TAGS table defines an ID and NAME for the descriptive tags you can apply to books. The sample data includes pre-defined tags like “Trains” (2), “Travel Narrative” (1), “Children” (5), “Fiction” (6), and others.

The BOOK_TITLES table contains a book’s TITLE, the PUBLISHER_ID, and a TAGS column, whose value is a colon-separated list of tag ids. For example, a book that is a fictional travel narrative about riding on trains might have the value “6:1:2” representing the id values of the three tags “Fiction”, “Travel Narrative”, and “Trains”. Since the order of the the tags is not significant to our use case, it also might have the value “1:2:6” depending on the order in which the end-user added the tags to the list.

The figure below shows how I configured the P3_TAGS page item in page 3’s form region to support visualizing and editing the possibly-multiple tags applied to a book. Notice that the page item type is Popup LOV, the Multiple Values property is enabled, the literal colon character (:) is indicated as the value Separator, and the Search as You Type property is on. I’ve configured a shared component TAGS_LOV List of Values to provide the alphabetized list of available tags.

Configuring multi-value popup LOV page item in an edit form page

Pay special attention to the Manual Entry setting above. We’ve consciously disabled that property since by design at the moment APEX shows the end user the underlying values (e.g. 6 , 1 , 2 ) for a manual-entry popup LOV instead of showing their corresponding display text values (e.g. Fiction , Travel Narrative, Trains ). That may be appropriate for some multi-value use cases where the LOV entry’s display and return values are the same, but here I preferred that my end-users would see the tag display values.

The result is the easy-to-use book editing page shown below. Notice that the P3_AUTHOR_IDS page item is configured in the same way as P3_TAGS to allow entering the colon-separated list of one or more author ids for the book.

Multi-value popup LOV page items editing book details at runtime

Multi-Value Smart Filter for Tags

APEX makes it simple to easily filter on one or more tags applied to records in both faceted search as well as the new Smart Filters region. The figure below shows how the P5_TAGS smart filters search facet in the Book Finder cards region page (5) is configured to enable this behavior. Notice the search facet Type is set to Checkbox Group, the Multiple ValuesType is set to Delimited List with the Separator configured to be a literal colon character (:), and we’ve enabled the Trim Whitespace option.

Configuring multi-value P5_TAGS smart filter facet to handle colon-delimited values

This is the only configuration necessary to get the tags field working for searching. The result produces a smart filter search field (above our cards region) on page 5 in the example app that looks like the figure below. Ticking one or more tags in the list narrows the search results to show only books having that/those tags applied (in any order).

Checkbox Group smart filters facet to search for books by one or more tags applied.

As above, the P5_AUTHOR_IDS search facet has been configured identically to the P5_TAGS one to allow narrowing down the search results by any combination of authors and/or tags as shown below where we’ve found books authored by Brian Spendolini about APEX.

Applying two multi-value smart filters: one for authors, one for tags

Displaying Multi-Value Fields in Report Regions

When working with multi-value columns like TAGS and AUTHOR_IDS in our BOOK_TITLES example table, it’s useful in report pages or card regions to show the list of display values corresponding to the one-or-more ids stored in the colon-separated column value. For this task, I employed the handy LISTAGG() function to aggregate the set of related tag display values and author names into an ordered, comma-separated list. I combined it with the useful split_numbers() function in the apex_string utility package. When wrapped by a table() operator, this helpful routine lets us select the numbers in the colon-separated list as a table row source right in the query. To make it easier to use this information from any report or card regions where I needed it in my application, I created the BOOK_TITLES_V database view with the following SELECT statement.

select 
    b.id,
    b.title,
    b.tags,
    b.rating,
    b.publisher_id,
    b.author_ids,
    (select listagg(name,', ')
            within group (order by name collate binary_ai)
       from book_tags 
      where id in (
        select column_value 
          from table(apex_string.split_numbers(b.tags,':'))
        )
    ) display_tags,
    (select listagg(name,', ')
            within group (order by name collate binary_ai)
      from book_authors 
     where id in (
       select column_value 
         from table(apex_string.split_numbers(b.author_ids,':')))
    ) display_authors,
    p.name as publisher
from book_titles b
left outer join book_publisher p 
             on p.id = b.publisher_id

Notice that I’m passing the literal colon character as the separator to the apex_string.split_numbers() function in two places, and passing the separator string consisting of a comma followed by a space to the listagg() function. The collate binary_ai keywords in the order by part of the listagg function’s within group clause ensures that display values sort in an accent-insensitive (and case-insensitive) way.

Enabling Custom PopupLOV Behavior via CSS Class

I had achieved my goal without writing any code and was happy with how simple it was to implement… when I noticed an interesting extra-credit opportunity that piqued my interest.

While interacting with the PopupLOV component, I observed that its Search as You Type filter remained “sticky” across multiple interactions with the dropdown list of choices. For example, consider the screenshot below where I was editing the tags for a Bill Bryson book. If I typed tr into the PopupLOV search field, as expected the list narrowed down to only show relevant tags “Trains” and “Travel Narrative”. However after choosing “Trains”, if I clicked again into the multi-value field to drop-down the list to choose another tag to apply, the list remained filtered as before to only those tags containing tr in their names. However, often the next tag I wanted to apply required me to clear the previous search field text to start fresh with the complete list of tags to choose from or search through.

Search as You Type filter in use in a PopupLOV for tags applied to a book.

After not finding any declarative PopupLOV setting to control the “stickiness” of the search field value, I began by experimenting with various ideas using dynamic actions to force the PopupLOV search field to clear. Not satisfied with the results, I reached out to my colleague John for some expert advice.

He suggested I implement generic JavaScript code in an app.js static application file that would automatically enable a custom behavior whenever a PopupLOV page item had a particular custom CSS class applied to it. This way, the code was reusable and enabled declaratively by simply adding a CSS class name like popup-lov-reset when the non-default search-field-resetting behavior was desired. The figure below shows the modal Edit Book page (12) called by the full card action on the card region of the Book Finder page (5). Notice the custom CSS class name popup-lov-reset in the AdvancedCSS Classes section. This is the signal to our generic code in app.js that this particular PopupLOV prefers the reset-search-field behavior each time the user engages the page item’s dropdown list.

Opting-in to custom application behavior by adding a custom CSS class to a page item

If you’re curious, you can study the full details of the custom JavaScript code John helped me with in the downloaded example app. However, most important were the higher-level principles he taught me along the way about how he recommends structuring application-level JavaScript code. Since I’m not a JavaScript expert, these were the even more interesting bits of precious knowledge that I felt fortunate to learn from him.

The high-level structure of the code in the example application’s app.js file appears below. It exposes a single app namespace inside of which can appear private functions specific to its implementation. This app namespace exports only the members it wants to be the public API. In this application, only the single function handlePopupLOVsWithSearchResetClass() is exported.

// Export just a single global symbol "app" to keep code clean
const app = (function($) {

    // Function private to the impl of the
    // exported 'app' namespace

    function makeResetPopupLov(itemName) {
      // Code removed for clarity here registers an event
      // handler on body of the page to react to the popupopen
      // event of PopupLOV page item in question.
      // See example app for full details.
    }

    // app namespace members
    return {
        //----------------------------------------------------
        // Turn any PopupLOV page items on page into ones that
        // reset their search field when dropdown pops open. 
        //----------------------------------------------------
        handlePopupLOVsWithSearchResetClass: function() {
            $(".popup-lov-reset.apex-item-popup-lov").each(
                function() {
                  makeResetPopupLov(this.id);
                }
             );
        }
    };
    // Ensure $ in app namespace resolves to correct jQuery
})(apex.jQuery);

If we include the “document ready” event handler code below inside the app.js file, then all pages in the application magically inherit the ability to have any PopupLOV page item on the page opt-in to reset-search-field behavior just by adding the CSS class name popup-lov-reset in the page item’s AdvancedCSS Classes section. In contrast, if you only want the functionality to be available on selected pages, then include this event handler code just on the specific pages where you want the behavior to be available.

// Inside app.js, runs for every page after document is ready
// Alternatively, you can just add to the pages where you want
// the behavior to be available.
//
// "Document Ready" event handler code
$(function() {
    app.handlePopupLOVsWithSearchResetClass();
});

If you want to try out the example for yourself, then download the APEX 21.2 example application here.