Refitting Filtered Map Points

Using a public dataset of New York City high schools, I built a page that lets students or parents filter the list based on various criteria. As they narrow their options, a map region reflects where the remaining schools are located, and a stacked bar chart lets them compare the contenders on a few important metrics. While the map initially centered and zoomed itself to fit the unfiltered set of schools, to give users the sense of “homing in” on their perfect school I wanted to refit the map around the narrowed search results. To reflect the filtered results from the cards region in the map and chart, I used the region data sharing technique from my colleague Carsten’s article Add a Chart to your Faceted Search Page. Then I got a little help from my colleagues Christian Lara and Stefan Dobre to learn how to build my first simple “Center/Zoom Map Around Points” dynamic action plug-in to make it easy to build this feature into any map-based pages I build in the future. You can check out the sample using the link at the end of the article.

Refreshing the Map and Chart

The data source for the map is a query from a pipelined table function I created following Carsten’s technique. It retrieves the high school data using the same filters currently applied to the schools region in the page:

select school_name, latitude, longitude
from eba_demo_nyc_highschool_data( 
       p_page_id => :APP_PAGE_ID,
       p_region_static_id => 'schools')

Similarly, the chart’s datasource is a query that selects a few different columns from the same pipelined table function:

select school_name, graduation_rate, 
       attendance_rate, college_career_rate
from eba_demo_nyc_highschool_data( 
       p_page_id => :APP_PAGE_ID,
       p_region_static_id => 'schools')

When the cards region changes due to the user’s applying new filters, we want to refresh the map and chart regions. The lesson I learned while getting this to work was that rather than using the “After Refresh” event on the cards region, I needed to instead use that region’s “Page Change [Cards]” event to trigger the dynamic action refresh, using two dynamic action steps of type “Refresh”.

Centering & Zooming the Map After Refresh

Whenever the map region gets refreshed, my goal was to have it refocus the user’s attention by using the new set of filtered data points to center and zoom the map appropriately. After hunting for a built-in APEX map JavaScript API, or a built-in dynamic action, I realized the solution would take a bit more research. My teammate Christian Lara pointed me at the MapLibre Fit a map to a bounding box example, and gave me this snippet of JavaScript below to consider.

The first statement accesses the map’s (post-refresh) bounding box from its mapData.map.bbox member and defines a new bounds array that contains the two points representing that rectangle. The second line gets the MapLibre map object from the APEX map region on my page, and calls its fitBounds() method to perform the centering and zooming to the new dimensions. It uses 30 pixels of padding so points near the edge of the box stay visible.

// Define the bounds using refreshed map bounding box coordinates
let bbox = apex.region("map").mapData.map.bbox,
    bounds = [
        [ bbox[0], bbox[1] ],
        [ bbox[2], bbox[3] ]
    ];
// Fit the map to the new bounds
apex.region("map").getMapObject().fitBounds(bounds,{padding: 30});

Creating a Reusable Dynamic Action Plug-in

With the code above in a dynamic action step triggered by the “After Refresh” event on the map region, the functionality I desired was working, but I wanted to learn how to encapsulate that little bit of code into a reusable dynamic action plug-in. I first watched Stefan Dobre’s The Ultimate Guide to APEX Plug-ins video from the APEX@Home 2020 conference, and then created a new dynamic action plugin named “Center & Zoom Map Around Points” in my application. Following best practice, I put the JavaScript code in a with a centerZoomMap.js file, and referenced its qualified name in the File URLs to Load section using the syntax PLUGIN_FILES#centerZoomMap#MIN#.js

I instinctively knew that to be reusable, the name of the map region’s static id would have to be a function parameter, so my first attempt at writing the contents of this centerZoomMap.js file looked like this:

// centerZoomMap.js -- First Attempt
function centerZoomMap(staticId) {
  // Define bounds using refreshed map bounding box coordinates
  let bbox = apex.region(staticId).mapData.map.bbox,
    bounds = [
        [ bbox[0], bbox[1] ],
        [ bbox[2], bbox[3] ]
    ];
  // Fit the map to the new bounds
  apex.region(staticId).getMapObject().fitBounds(bounds, {padding: 30});
}

After that, I defined a custom attribute in the plug-in named “Map Region Static Id” as attribute slot number one. However, I admit to getting a bit confused on how to pass the value of the plug in’s dynamic attribute to the JavaScript function. After asking my colleague Stefan Dobre for a tip, he used the occasion as a teachable moment to show me about the two standard plug-in attributes:

  • For Region
  • Affected Element Required

By leveraging these standard plug-in attributes, the developer using the plug-in gets a more native-feeling experience of picking the region to associate the plug-in with. It also allowed me to remove the custom attribute I had created in the plug in. The developer now configures the map she wants to center and zoom by simply picking the map region in the Affected Element section as shown below:

Configuring Affected Elements in the Properties editor to pick Map region to zoom and center

Stefan also took the opportunity to teach me a best practice of defining the centerZoomMap function as a property on the window to make its scope more clear when reading the code. So the final contents of centerZoomMap.js after consulting with Stefan looked like this:

// centerZoomMap.js - Final version
window.centerZoomMap = function() {
    // Ensure dev-configured affected element has a static id 
    const id = this.affectedElements.attr( "id" );
    if ( !id ) {
        throw new Error( "Affected Region must have an ID" );
    }
    // Use static id to ensure dev chose a map region
    const region = apex.region( id );
    if( !region || region.type !== "SpatialMap" ) {
        throw new Error( "Affected Region must be a Map" );
    }
    // Define bounds using refreshed map bounding box coordinates
    let bbox = region.mapData.map.bbox,
        bounds = [
            [bbox[0], bbox[1]],
            [bbox[2], bbox[3]]
        ];
    // Fit the map to the new bounds
    region.getMapObject().fitBounds(bounds, {padding: 30});
};

The last piece of the simple dynamic action plug-in was writing the render function using the appropriate function specification that I copied from the online help to use as a guide. The only job it had to do was tell the APEX engine the name of my JavaScript function to invoke when the dynamic action gets used at runtime:

function render (
    p_dynamic_action in apex_plugin.t_dynamic_action,
    p_plugin         in apex_plugin.t_plugin )
    return apex_plugin.t_dynamic_action_render_result is
    l_result apex_plugin.t_dynamic_action_render_result;
begin
    l_result.javascript_function := 'centerZoomMap';
    return l_result;
end;

Using the New Plugin in the Page

With the plug-in now defined, I went back to the Page Designer and removed the dynamic action step that was directly calling Christian’s snippet of JavaScript and replaced it by using the new “Center & Zoom Map Around Points” plugin we built. After picking the “Map” region from the Affected Elements section, it was ready to go.

Page Designer showing use of new plug-in to center & zoom the map after the map gets refreshed

Giving the Sample a Spin

To try out the sample, you can download the APEX 22.2 application from here. It gives the end user a nice school-searching experience like what you see in the screen recording below. Thanks again to Christian and Stefan for sharing their wisdom in getting to this end result.

Data-Driven Diagrams

I often want to visualize my Oracle APEX app’s data model as an Entity/Relationship diagram to remind myself how tables are related and exactly how columns are named. After recently stumbling on the open source Mermaid JS project, I had a lightbulb moment and set out to build my own data model visualizer app with APEX itself.

Mermaid Diagram Syntax

The Mermaid JS open source project aims to improve software documentation quality with an easy-to-maintain diagram syntax for Markdown files. The typical README.md file of a software project can include an Entity/Relationship diagram by simply including text like this:

erDiagram
DEPT ||--|{ EMP : "Works In"
EMP ||--|{ EMP : "Works For"

Including a diagram like this into your product doc is as simple as shown below:

Adding a Mermaid diagram to a Markdown file

If your markdown editor offers a WYSIWYG experience, the effect is even more dramatic and productive: you immediately see the results of the diagram you’re editing. For example, editing a Mermaid diagram in a readme file using Typora looks like this:

Editing a Mermaid diagram in a WYSIWYG Markdown editor like Typora

Popular source control repository sites like GitHub and GitLab have also embraced Mermaid diagrams. Since Markdown is used to provide check-in comments, on these sites (and others like them) it’s easy to include Mermaid diagrams in the helpful summaries you provide along with every commit.

Mermaid’s Diagram Types and Live Editor

Mermaid supports creating many different kinds of diagrams, each using a simple text-based syntax like the ER diagram above. At the time of writing, supported diagram types include Entity/Relationship, Class, Gantt, Flow, State, Mindmap, User Journey, Sequence, Git branch diagrams, and pie charts. The handy Mermaid Live site provides a “sandbox” editor experience where you can experiment with all the different kinds of diagrams, explore samples, and instantly see the results.

Mermaid Live editor for experimentation at https://mermaid.live

For example, after consulting their excellent documentation, I immediately tried including column details into my ER diagram for the DEPT table as shown below:

Mermaid Live editor showing ER diagram with column info and library of diagram samples

Rendering Mermaid Diagrams in Web Pages

To maximize the usefulness of the diagrams, the Mermaid project provides a simple JavaScript API to incorporate scalable vector graphics (SVG) renderings of text-based diagrams into any web page or web application. After referencing the Mermaid JS library URL, including a diagram into a page in my APEX application took a truly tiny amount of JavaScript: one line to initialize the library and one line to render the diagram from the text syntax.

In order to reference the current version of the Mermaid JS library on my page, I typed this URL into my page-level JavaScript > File URLs property:

https://cdnjs.cloudflare.com/ajax/libs/mermaid/9.3.0/mermaid.min.js

Then, after including a Static Content region in my page and assigning it a Static Id of diagram, the two lines of JavaScript code I added to the page-level Execute When Page Loads section looked like this:

mermaid.initialize();
mermaid.mermaidAPI.render('diagramsvg',
    `erDiagram
    DEPT ||--|{ EMP : "Works In"
    EMP ||--|{ EMP : "Works For"`,
    (svg) => {
    document.getElementById('diagram').innerHTML = svg;
});

These two lines of “When Page Loads” JavaScript do the following:

  1. Initialize the Mermaid library
  2. Render the diagram defined by the text passed in as an SVG drawing
  3. Set the contents of the diagram region to be this <svg id="diagramsvg"> element.

In no time, my APEX page now displayed a text-based Mermaid ER diagram:

APEX page including a Mermaid ER diagram based on its text-based syntax

Generating Mermaid Diagram Syntax from a Query

After proving out the concept, next I tackled generating the appropriate Mermaid erDiagram syntax based on the tables and relationships in the current APEX application schema. I made quick work of this task in a PL/SQL package function diagram_text() that combined a query over the USER_CONSTRAINTS data dictionary view with another query over the USER_TABLES view.

The USER_CONSTRAINTS query finds the tables involved in foreign key constraints as a “child” table, and gives the name of the primary key constraint of the “parent” table involved in the relationship. By joining a second time to the USER_CONSTRAINTS table, I can query both child and parent table names at once like this:

  select fk.table_name as many_table, 
         pk.table_name as one_table
    from user_constraints fk
    left outer join user_constraints pk
                 on pk.constraint_name = fk.r_constraint_name
   where fk.constraint_type = 'R' /* Relationship, a.k.a. Foreign Key */

The USER_TABLES query, using an appropriate MINUS clause, finds me the tables that aren’t already involved in a “parent/child” relationship above. By looping over the results of these two queries and “printing out” the appropriate Mermaid ER diagram syntax into a CLOB, my diagram_text() function returns the data-driven diagram syntax for all tables in the current schema.

I ultimately decided to include some additional parameters to filter the tables based on a prefix (e.g. EBA_DEMO_CONF), to control whether to include column info, and to decide whether common columns like ID, ROW_VERSION, and audit info should be included or not. This means the final PL/SQL API I settled on looked like this:

create or replace package eba_erd_mermaid as
    function diagram_text(p_table_prefix    varchar2 := null, 
                          p_include_columns boolean := false, 
                          p_all_columns     boolean := false )
    return clob;
end;

Wiring Up the Data-Driven Diagram

With the diagram_text() function in place, I added a hidden CLOB-valued page item P1_DIAGRAM to my page, added a P1_TABLE_PREFIX page item for an optional table prefix, and added two switch page items to let the user opt in to including column information.

Next, I added the computation to compute the value of the hidden P1_DIAGRAM page item using the diagram_text() function:

eba_erd_mermaid.diagram_text(
  p_table_prefix    => :P1_TABLE_PREFIX,
  p_include_columns => :P1_INCLUDE_COLUMNS = 'Y',
  p_all_columns     => :P1_ALL_COLUMNS = 'Y')

Lastly, I adjusted the “When Page Loads” JavaScript code to use the value of the P1_DIAGRAM hidden page item instead of my hard-coded EMP/DEPT diagram syntax:

mermaid.initialize();
mermaid.mermaidAPI.render('diagramsvg',
    apex.items.P1_DIAGRAM.value,
    (svg) => {
    document.getElementById('diagram').innerHTML = svg;
});

With these changes, I saw the instant database diagram I’d been dreaming of.

The Mermaid library handles the layout for a great-looking result out of the box. The diagram helped remind me of all the tables and relationships in the APEX app I wrote to manage VIEW Conference, Italy’s largest annual animation and computer graphics conference. It’s one of my volunteer nerd activities that I do in my spare time for fun.

Mermaid ER diagram of all tables in current schema matching prefix EBA_DEMO_CONF

However, when I tried installing my ER Diagram app in another workspace where I’m building a new app with a much larger data model, I realized that the default behavior of scaling the diagram to fit in the available space was not ideal for larger schemas. So I set out to find a way to let the user pan and zoom the SVG diagram.

SVG Pan Zoom

Luckily, I found a second open source project svg-pan-zoom that was just what the doctor ordered. By adding one additional JavaScript URL and one line of “When Page Loads” code, I quickly had my dynamically rendered ER diagram zooming and panning. The additional library URL I included was:

https://bumbu.github.io/svg-pan-zoom/dist/svg-pan-zoom.min.js

The extra line of JavaScript code I added to initialize the pan/zoom functionality looked like this:

var panZoom = svgPanZoom('#diagramsvg');

The combination of Mermaid JS and this SVG pan/zoom library puts some pretty impressive functionality into the hands of APEX developers for creating useful, data-driven visualizations. Even for developers like myself who are not JavaScript experts, the couple of lines required to jumpstart the libraries’ features is easily within reach.

With this change in place, now visualizing larger diagrams including showing column information was possible.

Dream #2: Reverse Engineer Quick SQL

Since I sometimes create APEX apps based on existing tables, a second schema-related dream I had was to reverse-engineer Quick SQL from the current user’s tables and relationships. This would let me quickly add additional columns using a developer-friendly, shorthand syntax as new application requirements demanded them. Googling around for leads, I found a 2017 blog article by Dimitri Gielis that gave me a headstart for the code required. Building on his original code, I expanded its datatype support and integrated it with my table prefix filtering to add a second page to my application that produces the Quick SQL syntax for the tables in the current schema.

Quick SQL syntax reverse-engineered from existing schema’s tables and relationships

I expanded the eba_erd_mermaid package to include an additional quicksql_text() function for this purpose:

create or replace package eba_erd_mermaid as
    function diagram_text(p_table_prefix    varchar2 := null, 
                          p_include_columns boolean := false, 
                          p_all_columns     boolean := false )
    return clob;
    function quicksql_text(p_table_prefix varchar2 := null)
    return clob;
end;

Copying Text to the Clipboard

As a last flourish, I wanted to make it easy to copy the Mermaid diagram text syntax to the clipboard so I could easily paste it into the Mermaid Live editor if necessary. And while I was at it, why not make it easy to also copy the Quick SQL text syntax to the clipboard to paste into APEX’s Quick SQL utility?

After searching for a built-in dynamic action to copy the text of a page item to the clipboard, I ended up looking for an existing plug-in to accomplish that functionality. I found an aptly-named APEX Copy Text to Clipboard dynamic action plugin from my colleague Ronny Weiss to get the job done easily with a few clicks of declarative configuration.

APEX, SQL & Open-Source JavaScript for the Win!

In short order, by using APEX to combine the power of the SQL that I know and some minimal JavaScript (that I don’t!), I was able to build myself two dream productivity tools to improve my life as an APEX developer in the future.

If you want to give the sample application a spin, download the APEX 22.2 application export from here. It installs only a single supporting PL/SQL package, so any tables you visualize with it will be your own.

Further Reading

For a full-featured, web-based ERD modeling solution from Oracle, make sure to check out the Data Modeler in SQL Developer Web.

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.