# APEX Select One & Select Many Item Types

# 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.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">The <strong>Select One</strong> and <strong>Select Many Item types were introduced in APEX 24.1</strong></div>
</div>

# 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1718834461936/ec4468ff-65ce-4de0-81e5-91a7b60f937f.png align="center")

* 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](https://blog.cloudnueva.com/apex-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](https://cdn.hashnode.com/res/hashnode/image/upload/v1718835121865/40b7803b-42b0-4261-98af-2ec15883f987.png align="center")

## Search

![Oracle APEX Select One Item Type Search](https://cdn.hashnode.com/res/hashnode/image/upload/v1718834528747/4c977325-d56c-44b1-8258-e95aab06780d.png align="center")

* 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1718837353267/dda7f59f-ad3b-435b-9fcc-4cfe555fac70.png align="center")

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1718837323210/cddb98af-fa29-41c1-be7c-f6a34027b5af.png align="center")

# 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1718835961069/f0ec80af-1811-4a5a-b554-fa1d94cba2c4.png align="center")

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1718836109723/696d507e-1371-4c85-8811-88984e49e22d.png align="center")

Example JSON Array: `[12433,28282,22822]`

Example Delimited List: `12433,28282,22822`

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

```sql
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:

```sql
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.