I recently launched the Select2 APEX plugin and thought it would be a good idea to talk about its seemingly endless list of features. The plugin is so powerful that it can be used as a replacement for three traditional item types: select lists (both single and multi-select), shuttles and text fields with autocomplete. It doesn’t simply replace these item types but rather enriches them with extra functionalities and a more sophisticated user interface.

All credits of course go to the developers of Select2. My job existed of creating an APEX item type plugin on top of this wonderful jQuery library. I also strived to support all typical APEX item type settings such as a read only condition, null display value, cascading LOV, etc. I’m glad I can say this worked out pretty well resulting in a high level of integration with the APEX builder.

Here are some details and helpful links about the Select2 APEX plugin:

Now let’s dive into the features of the plugin. The first thing you should be aware of is that the plugin allows you to create three different types of select lists: single-value select lists, multi-value select lists and tagging support. Some features are only available for a particular select list type, while others are shared among multiple types.

Single-value select lists

This type of select list is the most basic one and therefore contains fewer settings than the other two types. The main difference between a regular select list and a Select2 select list is the search capability, allowing the user to easily find a specific item in a lengthy list. Select lists with just a few items don’t benefit from this feature of course. That’s why there’s a Minimum Results for Search Field setting that determines whether it is useful or not to display the search field. This setting only applies to single-value select lists since the other two types always permit searches to be executed.

The Search Logic setting allows you to define the criteria under which a search term matches an option. You’ll probably recognize this setting from the Text Field with autocomplete item type as it contains the same options:

  • Contains & Ignore Case (default)
  • Contains & Case Sensitive
  • Exact & Ignore Case
  • Exact & Case Sensitive

The Minimum Input Length setting enforces the user to enter a minimum amount of characters before a search operation is performed. The screenshot below shows you all the available settings for the single-value select list. The untreated settings will be explained in just a few moments.

single-value select list settings
single-value select list settings

Multi-value select lists

A multi-value select list serves as an excellent replacement for the shuttle item. The difference here, again, lies in the user experience. A shuttle item typically takes up a lot of space and lacks the ability to search. The Select2 multi-value select list overcomes these shortcomings contributing to a more pleasant way of selecting multiple items in a list of values. It also adds an extra functionality; the Maximum Selection Size limits the maximum number of items that can be selected. You might also want to enable Rapid Selection so that the dropdown list stays open after a selection is made.

multi-value select list settings
multi-value select list settings

Tagging support

You can’t compare this type of select list with a pre-defined item type in APEX. That’s what makes is so interesting. In tagging mode, you can select from pre-existing options (coming from your list of values) or create new ones on the fly. Please note that the return value of this select list type is based on the display values and no longer on the return values.

Two new settings are available here. A Maximum Input Length can be set to limit the character length of new values, and it is possible to enable Drag and Drop Sorting of the selected choices. This last feature is not yet available in the current version (1.4 on the moment of writing). It will be released in version 1.5.

tagging support settings
tagging support settings

Remaining settings

We haven’t covered all of the settings yet. Let’s go through them quickly:

  • Width: gives you control over the width of the select list page item. Have a read through the help text of this setting for more information.
  • Select on Blur: tick the checkbox if you want Select2 to select the currently highlighted option when you blur out of the dropdown list.
  • Label for Null Option Group: allows you to name the group for values whose grouping column value is null. The default group name is Ungrouped.

Okay, you might not understand what that last setting is all about. The Select2 APEX plugin supports option grouping based on a third column in the LOV definition SQL statement. This feature greatly improves the structure of select lists. Note that option grouping is not possible in tagging mode. Here’s an example query that groups all employees based on their department. Employees with no department assigned (hence the Label for Null Option Group) will fall under the Ungrouped group.

SELECT e.ename d
     , e.empno r
     , d.dname grp
  FROM emp e
  LEFT JOIN dept d ON d.deptno = e.deptno
 ORDER BY grp, d

All the features I described in this article can be tried out on the demo page. You can download the Select2 APEX plugin on that same page.