Tag: Intersection

End-users find multivalue page items intuitive for quick data entry, and APEX devs have many kinds to choose from. However, using multivalue items in the simplest way means storing data in a single column as a delimited string. While easy to do, this could complicate other aspects of your app processing. For a row in a book table, for example, often the multiple tags applied to the book are stored in an intersection table. Imagine a book_tag_xref table with foreign key values recording which tag ids are paired with which book ids. In this article, you’ll learn how to manage intersection rows using a multivalue page item.

We start with an approach to edit the tags applied to a book using a combobox. Then we generalize the technique to easily configure this support for any data model and any kind of multivalue page item. The sample app contains an intersection_multival helper package you can use in your own apps.

NOTE: The APEX 24.1 downloadable sample app installs a trycombo ORDS REST Service module used later in the article, so before installing the sample please ensure your workspace parsing schema is enabled for ORDS REST Services. You can check that (and enable it if needed) on the SQL Workshop > RESTFul Services page of the APEX Builder.

Sample Data Model

When you import the sample app, its Supporting Objects scripts install the following simple three-table data model and insert initial sample data for two books, six tags, and rows in the intersection table that record three tags applied to each book. For ease of discussion, we’ll refer to the tables in this article without their prefix simply as book, tag, and book_tag_xref, but the code and pages we’ll study use the full names shown below.

Books Manual Cards + Smart Filters Page

Running the app opens the home page Books Manual (page 8) displaying books in a cards region as shown below. We’ll study this page first, along with its modal form page Book Manual (page 11).

The card region’s SQL query includes an appropriate LISTAGG() select list expression to retrieve a colon-delimited string of applied tag names from the tag table based on the corresponding tag_id foreign key values it finds in the intersection book_tag_xref table for the current book:

select b.id,
       b.name,
       (select listagg(t.name,':')
          from eba_demo_trycombo_tag t
         where t.id in (select btx.tag_id
                          from eba_demo_trycombo_book_tag_xref btx
                         where btx.book_id = b.id)) as tags
  from eba_demo_trycombo_book b
order by b.name

On the card region’s Attributes tab in the property editor, the card Body uses Advanced Formatting with an HTML Expression containing the {loop/} template directive to iterate over the colon-separated tag names it finds in the current row’s TAGS column value. This loop formats each tag name using the a-Chip CSS class. This makes the tag names look like little “chips”:

{loop ":" TAGS/}<span class="a-Chip">&APEX$ITEM.</span> {endloop/}

The Full Card action is configured to open page 11 (Book Manual), passing in the value of the book ID column for the P11_ID page item. Clicking on the card for the Building Oracle XML Applications book opens the modal drawer page we explore next.

Book Manual Modal Form Page

When we edit a book, the page opens to show the name of the book and the currently applied tags in a combobox as shown below:

The P11_TAGS combobox page item uses the shared component List of Values LOV_TAGS to provide the list of existing tag names to choose from. The Value HTML Expression uses the following HTML markup to format each entry in the combobox dropdown list using the same a‑Chip CSS class we used in the Books Manual cards page:

<span class="a-Chip">&NAME.</span>

Notice we are referencing a NAME column from the List of Values data source using the &NAME. notation in the combobox’s Value HTML Expression. For this to work, the columns must be configured in the List of Values component definition as Additional Display Columns as shown below:

This produces the following look and feel when the end-user clicks into the combobox field to show the dropdown list:

While it’s not required for this simple sample app, know that the Value HTML Expression can also make use of HTML Template Directives to perform sophisticated conditional output for each item in the list whenever that could prove useful in your own apps.

Unified Config for Multivalue Items

The Combobox page item lets end-users pick multiple options from a flexibly-styled, type-to-autoreduce list of values. Users can also key in new entries manually. This works perfectly for an app like this sample where users can invent their own tags to classify books, or pick from a set of existing tags. APEX 24.1 adds the Select Many item that works similarly, but in that case users can only choose from the listed options. These two new multivalued item types join the ranks of the Select List, Shuttle, List Manager, and Popup LOV that all handle displaying and editing multiple values from a list. In the APEX 24.1 Builder, you configure all of their multivalue behavior in a consistent way in a new Multiple Values section of the property editor.

As shown in the figure below, the P11_TAGS combobox in the Book Manual page (11) has Delimited List for its Multiple Values > Type property with the colon character (:) as the delimiter.

Reading Multivalue from Intersection Table

The P11_TAGS combobox item expects a colon-delimited list of tag ids, but in our data model the tag ids applied to the book being edited are saved in the book_tag_xref table. So, in our page’s Pre-Rendering section, in the Before Header subsection we use an Invoke API page process to call the READ_APPLIED_TAG_LIST() function in the EBA_DEMO_TRYCOMBO package. This is declaratively configured to pass in the value of the P11_ID page item with the book’s primary key and to return the function result into the hidden P11_TAGS_BEFORE page item. In the After Header subsection, we use a computation to copy the value of P11_TAGS_BEFORE into the P11_TAGS combobox. We do this so we can later compare the “before” value of the applied tags with the submitted value of the P11_TAGS combobox to notice if the user has effectively made any changes or not. There’s no need to process the list of tag ids if nothing has changed.

The code of the READ_APPLIED_TAG_LIST() function is straightforward. It uses the listagg() function to produce the colon-separated list of tag id’s from the book_tag_xref intersection table corresponding to the p_book_id passed in using a SQL query as shown below:

-- In Package Body EBA_DEMO_TRYCOMBO
function read_applied_tag_list(
    p_book_id in number)
    return       varchar2
is
    l_ret varchar2(4000);
begin
    select listagg(tag_id,':')
        into l_ret
        from eba_demo_trycombo_book_tag_xref
        where book_id = p_book_id
        order by tag_id;
    return l_ret;
exception
    when no_data_found then
        return null;
end read_applied_tag_list;

This setup is all we need to retrieve the multivalue string of tag ids during page load so the combobox can show the list of applied tags in the edit form.

Simplifying Processing of Manual Entries

The value of the P11_TAGS combobox is a colon-separated list of tag ids. These correspond to values of the “return column” of the LOV_TAGS list of values shared component. It defines the tag’s ID as return column and tag NAME as display column. However, recall that the “combo” in combobox means it combines the selection of pre-defined entries with manually-typed-in entries.

The combobox simplifies our job of processing the manually-typed entries by partitioning them into a separate (typically hidden) page item. The Book Manual page (11) has the P11_NEW_MANUAL_TAG_NAMES hidden page item for this purpose. The P11_TAGS combobox references P11_NEW_MANUAL_TAG_NAMES as its Manual Entries Item.

When the page is submitted, we need to process any manual entries in P11_NEW_MANUAL_TAG_NAMES and save any other changes made to P11_TAGS to the intersection table. We’ll do that next.

Before we move on, let’s take a moment to study the Session State > Storage property of the P11_NEW_MANUAL_TAG_NAMES hidden page item. Since it is not related to the form region or a database column, by default it will be created in Page Designer with a value of Per Session (Persistent) for this property. It is super-important to change this to Per Request (Memory Only). Failing to do this means the same manually entered items will appear to be present the next time you visit the edit form. By setting this property to Per Request (Memory Only), its value is submitted to be processed but then will not “hang around” for longer than that.

Saving Applied Tags to Intersection Table

The Book Manual page (11) configures another Invoke API page process in the Processing section of the Processing tab. It declaratively invokes the SAVE_APPLIED_TAG_LIST() procedure in the EBA_DEMO_TRYCOMBO package that has the following API. We pass in P11_ID for the p_book_id, P11_TAGS for the p_delimited_tags, and P11_NEW_MANUAL_TAG_NAMES for the p_new_manual_entries parameters.

procedure save_applied_tag_list(
    p_book_id            in number,
    p_delimited_tags     in varchar2,
    p_new_manual_entries in varchar2);

This Invoke API page process uses the following server-side condition expression to execute only if necessary. Specifically, we only want this “save applied tags” logic to run if the P11_TAGS is different from P11_TAGS_BEFORE or if there are any new manual tag names to process. The nvl() expressions are a compact way of correctly handling the situations where the tag ids value changed from null to non-null or vice versa.

nvl(:P11_TAGS_BEFORE,':') != nvl(:P11_TAGS,':')
or :P11_NEW_MANUAL_TAG_NAMES IS NOT NULL

Pseudocode for Saving Applied Tags List

The SAVE_APPLIED_TAG_LIST() procedure performs the following pseudocode steps to track two lists of tag ids: l_add and l_delete. These lists are the tag ids we need to add to and delete from the intersection table for the current book id.

1. l_old := list of applied tag ids from book_tag_xref for current book
2. l_new := list of submitted tag ids
3. l_manuals := list of manual entries (if any)
4. loop over l_manuals to…
   • insert new row for each into tag table, and
   • add each’s new system-assigned primary key to l_add list
5. loop over tag ids in l_old list to…
   • detect if any is missing from l_new list, and if so…
   • add intersection row primary key for missing tag id to l_delete list
6. loop over tag ids inl_new list to…
   • detect if any is missing from l_old list, and if so…
   • add missing tag id to l_add list
7. Delete any rows in l_delete from book_tag_xref by primary key
8. Insert any tag ids for this book id in l_add to book_tag_xref

The result is a pair of Books Manual and Book Manual pages (8 & 11) that let us edit the applied tags of any book. For example, as shown below we’re adding to manually-typed tag names PL/SQL and XSLT to the Building Oracle XML Applications book:

Then we can see they are shown in the Books Manual page on the card for the book just edited, and the new tags also show up as existing tags in the next book that gets edited as shown below:

When using the combobox page item, keep in mind that a manually-entered tag value can contain spaces in the name, too. So a new tag value could be Dynamic Action and when you type the [Enter] key, the combobox turns the two-word tag name “Dynamic Action” into a new chip to differentiate between one chip for “Dynamic” and another chip for “Action”.

Generalizing Read_List() + Save_List()

The code explained earlier for READ_APPLIED_TAGS_LIST() and the approach outlined in pseudocode above for the SAVE_APPLIED_TAGS_LIST() procedure implement one particular example of a more general pattern. The APEX_EXEC package is a general-purpose API for data retrieval and manipulation, and we can use it to generalize the approach to work for any use case. Imagine a generic READ_LIST() function and SAVE_LIST() procedure that accept as parameters all the appropriate information required that make each use case distinct. Our generic routines would need to accept as parameters details like the names of the:

• source intersection table (e.g BOOK_TAG_XREF)
• source table’s “parent foreign key” column (e.g. BOOK_ID)
• source table’s “other reference foreign key” column (e.g. TAG_ID)
• other reference table (e.g. TAG)
• other reference table’s primary key column name (e.g. ID)
• other reference table’s description column (e.g. NAME)

While we are at it, we can imagine supporting both theDELIMITED_LIST style of APEX multivalue string, with developer-configured delimiter, as well as the JSON_ARRAYtype of multivalue string.

And since APEX_EXEC provides APIs for working both with local database data sources as well as REST Data Sources, why not also let the generic APIs work with both common types of intersection and other reference sources: DB or REST?

This leads us to create two APIs like the following you will find in the INTERSECTION_MULTIVAL package in the sample app:

function read_list( 
    p_multival_type             in t_multival_type, 
    p_intersection_source_type  in t_source_type, 
    p_intersection_source_name  in varchar2,
    p_other_ref_foreign_key_col in varchar2,  
    p_parent_foreign_key_col    in varchar2,
    p_parent_foreign_key_val    in varchar2,
    p_delimiter                 in varchar2 default ':',
    p_parent_param_name         in varchar2 default null)
    return                         varchar2;

Similarly, for the SAVE_LIST() procedure, we have the following API:

procedure save_list( 
    p_multival_list              in varchar2,
    p_multival_type              in t_multival_type, 
    p_intersection_source_type   in t_source_type, 
    p_intersection_source_name   in varchar2,
    p_intersection_source_pk_col in varchar2,
    p_parent_foreign_key_col     in varchar2,
    p_parent_foreign_key_val     in varchar2,
    p_other_ref_foreign_key_col  in varchar2,
    p_delimiter                  in varchar2 default ':', 
    p_new_manual_entries         in varchar2 default null,
    p_other_ref_source_name      in varchar2 default null,
    p_other_ref_primary_key_col  in varchar2 default null,
    p_other_ref_description_col  in varchar2 default null,
    p_parent_param_name          in varchar2 default null);

The sample app contains the following pairs of pages that illustrate using the generic READ_LIST() and SAVE_LIST() helper package routines against DB and REST sources and using Delimited List and JSON Array types of multivalue page items. Each corresponding Book edit page uses a pair of Invoke API page processes to declaratively configure the call to READ_LIST() and SAVE_LIST() in the INTERSECTION_MULTIVAL package to get the job done:

• DB, Colon-Delimited: Books Delimited (2) + Book Delimited (3)
• DB, JSON Array: Books JSON Array (6) + Book JSON Array (5)
• REST, Colon-Delimited: Books REST (9) + Book REST (10)

Getting the REST Data Sources Working

The sample app contains three REST Data Sources based on a trycombo ORDS module that the sample app installs using its Supporting Objects scripts. As shown below, in my own environment the ORDS services have a particular endpoint URL that will be different when you install the sample into your APEX workspace. So getting the REST Data Sources working in your environment involves adjusting the Remote Server definition to match the base URL of your environment’s ORDS REST service modules.

To adjust the Remote Server definition appropriately, follow these steps:

1. Visit the RESTFul Services editor
   • SQL Workshop > RESTFul Services
2. Expand the Modules Node in the RESTFul Data Services tree
3. Select the trycombo module node in the tree
4. Copy the value to the clipboard of the Full URL shown in the panel on the right
   • For example, it might look like https://apex.oracle.com/pls/apex/workspace_alias/trycombo/
5. Visit the Remote Servers page in the APEX Builder
   • App Builder > Workspace Utilities > All Workspace Utilities > Remote Servers
6. Edit the remote server with name apex-oraclecorp-com-pls-apex-stevemuench
7. In the Endpoint URL field, paste the value you copied to the clipboard in step 4
8. Remove the trailing /trycombo/ from this value, and click (Apply Changes)
9. Run the sample, visit the “Setup” page, and click the (Setup REST Tags Synchronization) button

If you have done everything correctly, you should see the success message:

• “REST Synchronization Setup for REST Tags“

Trying Different Generic Combinations

You can now try out any of the pages in the sample app. They all edit the same book, tag, and book_tag_xref data, including the REST-based option. Experiment searching for books by title or tag using the smart filters search bar at the top of each cards page. In addition, notice the multivalue page item selector menu options under the logged-in username shown below. They let you change the APEX multivalue page item used by the Books Delimited, Books JSON Array, and Books REST page pairs. It does not affect the Books Manual page which only has the combobox we studied above.

To achieve this effect, the menu options set an application item named MULTIVAL_PAGE_ITEM_TYPE to one of the values COMBOBOX, SELECTMANY, or POPUPLOV and the pages reference this value in a server-side condition to conditionally display the appropriate multivalue page item, conditionally execute a computation to copy the “before” tag ids to the appropriate multivalue page item, and conditionally execute page processes for the saving of the tag id multivalue string and new manual entries (if applicable).

Downloading the Sample App

Hopefully this sample helps you better understand what’s possible using the various kinds of APEX multivalue page items and gives you a head start on creating APEX pages in the future that use multivalue page items with intersection row data.

You can download the APEX 24.1 sample app from here.