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
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.
Search
Match Type
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
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 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.
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.
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.
