APEX Select One & Select Many Item Types

APEX Select One & Select Many Item Types

ยท

4 min read

Introduction

The last time I got this excited about a native APEX Item Type was the Popup LOV. The new Select One and Select Many item types represent the Evolutionary Peak of the Oracle APEX list of value Item Types.

In this post, I will describe my perfect select list and review the key configurations (and unique features) of the Select One and Select Many item types.

๐Ÿ’ก
The Select One and Select Many Item types were introduced in APEX 24.1

My Perfect Select List

Here are my criteria for the perfect selection list:

  • It should be able to display one value but return another value (usually an ID) โœ…

  • You should be able to decide if the complete list is loaded or if the list is dynamically filtered as you type โœ…

  • You should be able to start typing to narrow down the list โœ…

  • Once the list narrows down to one item, you should be able to tab out of the field, and the remaining value should be defaulted โœ…

  • You should be able to clear the currently selected value easily โœ…

  • You should be able to choose the method of filtering the list (Starts With / Contains) โœ…

  • It should utilize caching and a minimum number of characters to enter before filtering to optimize performance โœ…

  • You should be able to add some bling ๐Ÿ’Ž to the list based on conditional logic โœ…

  • The ability to select more than one value โœ…

โœ… Select One and Select Many support this.

Select One

This section describes the key configuration options for the Select One Item Type.

Settings

Oracle APEX Select One Item Type Settings

  • Maximum Values in List

    • Determines how many values will be displayed in the drop-down list up to a maximum of 250.
  • Fetch on Search

    • This determines whether the entire list should be loaded on page load (Un-Selected) or filtered dynamically as the user types (Selected). For lists of more than 100 entries, I would leave this Selected to improve performance.
  • Use Cache

    • Specify whether search results are cached in the browser when Fetch on Search is enabled.
  • Value HTML Expression

    • This field allows you to display pretty much anything you want in the list of values returned by your search.

    • It supports Template Directives so that you can include conditional logic that runs on the client.

    • You can reference any columns from your List of Values SQL, even if they are not displayed.

    • The screenshot below reflects what the user sees for the configuration in the screenshot above. In this case, Inactive items are displayed in red.

Oracle APEX Select One Value HTML Expression

Oracle APEX Select One Item Type Search

  • Match Type

    • Values: Starts With or Contains

    • Adding the Starts With option allows Oracle to use indexes to search your list.

  • Case-Sensitive

    • Determines if the search is case-sensitive.
  • Minimum Characters

    • The minimum number of characters the user must type before the search is initiated. This can make a big difference in performance when dealing with large lists because it reduces the initial number of values returned to those matching the characters entered.

Messages - No Data Found Message

Oracle APEX Select One Item Type No Data Found Message

This is a nice touch. You can now specify what should be displayed in the list if there are no matches for your search criteria.

Select One No Data Found Message

Select Many

The Select Many Item Type works pretty much the same way as the Select One Item Type. The major difference is that you can select and return multiple values.

Oracle APEX Select Many Item Type

I like the checkbox-style UI. It gives the user positive feedback that they have selected the correct values.

Multiple Values

Another nice touch is the ability to return a JSON Array or a delimited list of the selected values.

Oracle APEX Select Many Item Type Multiple Values

Example JSON Array: [12433,28282,22822]

Example Delimited List: 12433,28282,22822

This allows you to do this when JSON Array is selected:

SELECT users.display_name
FROM   apx_users users
WHERE  EXISTS (SELECT 'X'
               FROM   json_table (:P10_USER_IDS, '$[*]'
                        columns user_id number path '$') ids
               WHERE ids.user_id = users.user_id);

or this when Delimited List is selected:

SELECT users.display_name
FROM   apx_users users
WHERE  user_id MEMBER OF (SELECT apex_string.split_numbers(:P10_USER_IDS,':') 
                          FROM DUAL);

Conclusion

Bravo to the APEX Development team. This item type will make my life (and, more importantly, my users' lives) much easier.

It would be great if some of the features of the new select lists could be added to existing item types, especially adding Template Directives and Match Type to the Popup LOV.

ย