Using Select2 on APEX tabular forms
I love the Select2 jQuery plugin. It’s a feature rich, good looking, select list replacement. As an APEX developer, it makes sense to use the excellent plugin from Nick Buytaert. Since its announcement, in Aug 2013, you would be hard pressed to find an APEX application where I don’t use it.
All that said, APEX doesn’t (yet) support plugins on tabular forms. This blog post is about documenting the JavaScript and Dynamic Actions (DA) you could use to use it as part of a tabular form.
The Quick and Easy Approach
If your tabular form contains a Select List already, this method is for you. It will work whether you used a Column Type of “Select List” or APEX_ITEM in it’s various forms: apex_item.select_list , apex_item.select_list_from_query or apex_item.select_list_from_query_xl.
- You include the Select2 library on the page. (more on this in a moment)
- You target your Select Lists with jQuery and run the .select2() method on them. Yup, that’s it.
Step #2 may look like this:
$("select[name='f01']").select2();
or if you have any options to include:
var options={allowClear: true};
$("select[name='f01']").select2(options);
Check out this demo. (Click on the “Apply Select2” button).
The button runs a simple DA that runs the code above. Notice that, if you’re using the Universal Theme in APEX5 you may want to remove the select list styling applied by the class “u-TF-item–select”. Use a command like this:
// Full effect for APEX5
$("select[name='f01']").removeClass("u-TF-item--select").select2();
Let’s talk about #2 above. In order to include the Select2 library on the page, I like to “cheat” whenever possible and have at least one Select2 APEX item plugin on the page. Doing this will include all the JavaScript and CSS that you need to run the .select2() method. That’s exactly how the previous demo page works.
The (slightly) Messy Approach
However, if it’s not possible or desirable to include a regular Select2 item on your page, you’ll have to manually add the necessary libraries. This can get a little tricky if say for example you download the Select2 jQuery files, they don’t match the version used by the plugin, and then later you add a Select2 item plugin to the page. You’ll want to be careful with that. For this reason, I like to use the same files that the Select2 item plugin is using. That’s also not ideal because you could upgrade the plugin and then again be in conflict with the versions. My preferred approach is to move the file includes of the Select2 APEX plugin to their own procedure. Share that new procedure between the Select2 APEX plugin and my own, simple, “Manual Select2” plugin. You always move all plugin code to a package don’t you? (perhaps that’s a conversation for a different blog post).
This new “Manual Select2” plugin is what we’ll use to initialize the select lists on a tabular form.
My new procedure with the includes looks like this:
--==============================================================================
-- BEGIN Select2 Helpers. Used by tabular_select2 and select2_render
--==============================================================================
procedure select2_includes(p_plugin in apex_plugin.t_plugin)
is
begin
apex_javascript.add_library(
p_name => 'select2.min',
p_directory => p_plugin.file_prefix,
p_version => null
);
apex_javascript.add_library(
p_name => 'select2-apex',
p_directory => p_plugin.file_prefix,
p_version => null
);
apex_css.add_file(
p_name => 'select2',
p_directory => p_plugin.file_prefix,
p_version => null
);
apex_css.add_file(
p_name => 'select2-bootstrap',
p_directory => p_plugin.file_prefix,
p_version => null
);
end select2_includes;
--==============================================================================
-- END Select2 Helpers.
--==============================================================================
The original Select2 plugin changes to something like this:
sys.htp.p('</ul>');
end if;
return l_render_result;
end if;
select2_includes(p_plugin);
if (l_select_list_type = 'MULTI') then
l_multiselect := 'multiple';
else
l_multiselect := '';
end if;
if (l_select_list_type = 'TAG' or l_lazy_loading is not null) then
For the new plugin, we’ll use a Dynamic Action type plugin with an Initialize category. It will have only one custom attribute for the component. The attribute will be used to contain the code that will initialize the select2 items.
When we use the plugin, the code “JS Function” can look like this:
var options={allowClear: true, width: "150px"};
$("select[name='f01']").select2(options);
Or it can be a lot more complicated, for example:
$("#P30_DENIAL_CODE,#P30_REMARK_CODE").select2({
width: "85px",
formatResult: format_RR_R,
formatSelection: format_RR_S,
dropdownAutoWidth: true,
allowClear: true
});
As you can probably tell from this code, you’re not limited to using this on tabular forms. With this technique you can use the full power of the Select2 plugin, like for example using the formatResult or formatSelection options that allow you to specify a function that formats how the values are displayed.
This technique does require some trial an error, but hopefully it gives you a starting point to extend the capabilities of your applications.
Check out the demo using the Manual Select2 plugin approach.
Here’s the plugin dynamic_action_plugin_com_enkitec_manual_select2.sql.
The code code of the Manual Select2 plugin looks like this:
l_func_prefix varchar2(20) := 'f_ek_sel2_';
l_js_code varchar2(32767) := p_dynamic_action.attribute_01;
l_onload_code varchar2(32767);
begin
...
-- the javascript_function is required for this type of plugin, but we won't use it.
l_result.javascript_function := 'null';
select2_includes(p_plugin);
if l_js_code is not null then
-- add the provided code wrapped as a function using l_func_prefix and the DA id.
l_js_code := 'function ' || l_func_prefix || p_dynamic_action.id || '(){' || l_js_code || '}';
apex_javascript.add_inline_code(p_code => l_js_code, p_key => p_dynamic_action.id);
l_onload_code := l_func_prefix || p_dynamic_action.id || '();';
apex_javascript.add_onload_code(p_code => l_onload_code);
end if;
return l_result;
end;
It basically takes the code you provide (the code to initialize select2), wraps it with a function and calls it on page load.
If you provide the code:
var options={allowClear: true, width: "150px"};
$("select[name='f01']").select2(options);
The function will look something like this (additional formatting added for clarity):
function f_ek_sel2_13421097715712641729() {
var options={allowClear: true, width: "150px"};
$("select[name='f01']").select2(options);
}
UPDATE Feb 7, 2016: I’ve added a new fully working example implementing Lazy Loading.
https://apex.oracle.com/pls/apex/f?p=46011:30
UPDATE Oct 7, 2016: Github Repo with the App https://github.com/rimblas/apex-demo-app
Hi, I'm Jorge Rimblas. Father, husband, photographer, Oraclenerd, Oracle APEX expert, Oracle ACE, coffee lover, car guy, gadget addict, etc... I'm an APEX Tech Lead DRW. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.
Jorge,
Wonderful article.. Now I have a question, involving manual tabular forms and the select2 plugin. In your demo you are showing select lists only. Do you have an example of an autocomplete textbox in a tabular form?
Ah great question, I do not, but I did implement the AJAX (lazy loading) option in the tabular form. It looked something like this.
var denialOptions = { dropdownAutoWidth: true, minimumInputLength: 1, ajax: { // instead of writing the function to execute the request we use Select2's convenient helper url: 'wwv_flow.show', dataType: 'json', quietMillis: 500, data: function (term, page) { return { p_request : 'APPLICATION_PROCESS=DENIAL_REASONS_JSON', p_flow_id : $v('pFlowId'), p_flow_step_id : $v('pFlowStepId'), p_instance : $v('pInstance'), x01 : term }; }, results: function (data, page) { return { results: data.row }; }, cache: true }, // end ajax call initSelection: function(element, callback) { var id = element.val(); if (id) { var data = {id: element.val(), text: dcDic[id]}; // dcDic is a JS array with all the values needed to pre-polulate the page // if only to rows on the tab form have values then dcDic has two entries. // Optionall, add another ajax callback here to grab the text for the ID, // However, I don't like the performance hit here with a lot of rows callback(data); } }, // end init selection placeholder: " ", // Allow clear seems to only work with remote data if there is a placeholder allowClear: true}; $('.lineDenialReason').select2(denialOptions);This is how the dcDic referenced above is populated
-- Code for populating dcDic -- place this as a PL/SQL Dynamic Content region BEFORE your tabular form region declare l_need_comma boolean := false; begin -- Create "dictionary" arrays: -- dcDic for Denial Reasons -- They are used for the populating the Select2 items -- that already have codes and avoid an extra AJAX call for each one. sys.htp.p(''); end;Now, for a future blog post, I did implement a tabular form autocomplete using awesomplete https://leaverou.github.io/awesomplete/
That little plugin really rocks!
Sorry fat fingered last posting… I have have an issue with a manual tabular form needing a couple of autocomplete textboxes and am stuck.. Posted up on the APEX support Forum: https://community.oracle.com/thread/3886036
Appreciate the help on this issue. I have changed the password on the demo account since it seems someone else was accessing the account and running pages and such.
I also have to try and take the application from that instance and move back to my office instance, a mistake occurred and they wiped out the application and tables. They are looking to a backup but are worried it might be too much of a pain to restore for schema..
Hi,
Great post.
could you please export the plugin from your application as a sql file.
Sure, here it is: https://dl.dropboxusercontent.com/u/82641814/blog/dynamic_action_plugin_com_enkitec_manual_select2.sql
I made the link in the post more noticeable.
Good article.
I tried the plugin it is working for already loaded rows, but when i tried to click add row button , plugin is not working.
When the new row is added, the corresponding elements need to be initialized via the select2() call. When the Add Row button is called it calls
apex.widget.tabular.addRow();. Unfortunately, the call doesn’t issue any JS events. If it did, it would be easier to know when to initialize the selects. One way around this, might be changing the button to call your own function: onclick=”addRow()”function addRow() {
apex.widget.tabular.addRow();
$("#reportID").find(".t-Report-report tr:last").find("select[name='f01']").select2();
}
I tried the plugin it is working for already loaded rows, but when i tried to click add row button , plugin is not working.
I can implementation this code
/*function addRow() {
apex.widget.tabular.addRow();
$(“#reportID”).find(“.t-Report-report tr:last”).find(“select[name=’f01′]”).select2();
}*/
for add row button.
And i need to search list populated when i click add row button.
Please some one help me.
Hi,
The download link is down….
Great Post
Hi Jorge,
I have been using Select2 in Tabular forms too albeit an equally messy manual approach and have been meaning to package up as a plugin for a while – but as you mentioned, creating an easy to use plug-in for Tabular forms isn’t the most straight forward. I did a presentation on adding it manually at the Apex Meetup in Leeds.
I see that you have added the Lazy loading segment – to allow for the select list to be loaded when clicked – correct me if I’m wrong?
Assuming I haven’t misunderstood, I have a different solution to this – and happy to share my code if you are interested. My solution essentially loaded the entire results set of the select list into a JSON array in a page load event.
It then appends the results of the stored JSON onto the select2 elements.
This essentially means that you only need 1 database call to populate all of the Select2s – and by storing the JSON values in a JavaScript array – it can be called again and again at any point – for example after a region refresh DA.
This was particularly useful when I wanted to apply Select2 to a Select list which had thousands of results.
Let me know, if this sounds useful to you – and I would be happy to share my code.
Many thanks,
Adrian
Adrian, I really like the sound of the solution you describe storing the values in JSON and it makes a lot of sense. User experience is probably pretty nice too. I basically follow a similar technique when loading the form for existing data. It would be pretty nice to do something similar for the regular functionality.
Yeah, please share the code, here or via email (first at last dot com)
Thank you
-Jorge
Hi,
Thanks for an excellent post.
Your solution works really well, but I have noticed that if a report is reloaded using PPR, (for example when navigating to the next page in the report pagination), that the Select2’s are not being generated.
To get around this, I have changed the following:
l_result.javascript_function := ‘NULL’;
to
l_result.javascript_function := ‘function() { ‘|| l_func_prefix||p_dynamic_action.id|| ‘(); }’;
and then moved that line down to the bottom of the ‘IF’ statement. This allows the Select2’s to be re-generated when the report is refreshed.
On a side note, I am currently working on a solution, based around your work, and that of Nick Buytaert to create this solution in a complete plugin, without the need to create a PL/SQL dynamic region or Application Process, and based the whole data source on an LOV in APEX.
I’ll let you know how I get on!
Cheers,
Cj
Thank you! Good comment. Yes you found out, you have to instantiate the Select2 elements again.
I like what you describe, good enhancement!
Thanks…This plugin is very helpful for tabular form ..However only the clear icon is not coming ..
var options={allowClear: true, width: “150px”};
$(“select[name=’f17′]”).select2(options);
Can you please help me out.
That’s odd. Can you reproduce in apex.oracle.com ?
Hey Jorge, Can you please provide video of this functionality or any sample app i can look in backend how its coded.
Done! Here’s a repo with the example application: https://github.com/rimblas/apex-demo-app
Hi Jorge, in your example you are using the select2 plugin version 2.6.4.
Is it working correctly with the latest version 3.0.1?
I’m receiving an error “Uncaught Error: Option ‘ajax’ is not allowed for Select2 when attached to a element.”.
Master – detail form with one select list on each other.
The master select list worked well until the additions needed for the tabular form (detail).
I have not tested it. However, I can see that Select2 still supports the ajax option: http://select2.github.io/select2/#ajax
My guess is that your re-initializing a elements that’s already a Select2. Maybe check your selectors?
I loaded the previous select2 plugin version (from your example) and it works as expected.
Hi again,
select2 (change etc..) events work in the tabular form?
In my case only default change event works but on a new row (addrow) it doesn’t trigger at all.
Sounds like the “Event Scope” in your DA is set to Static. Change it to Dynamic.
Thank you for the hint! it works as it should be.
But again with the default change event (not the select2 change event) not a problem for me at all.
Hi there! Im new on this and I really want to make this work, I want to know if this works in Master detail Pages, Im trying to do it but I cant, can you give me a hand? I think that the problem is that I have more than one Select2 in my page, cause they have change their form but my tabular dont, hope you understand my problem! Thanks!
Will this work on a tabular form with apex 4.2 or just 5+. I need a users to select one or more values from a select list in a tabular form.
Yes, it will work with 4.2
You’ll need to use the 4.2 version of the Select2 APEX plugin, or add the Select2 library yourself.
I have downloaded and installed the 4.2 version, but “Select2” is not an option when I try to edit the column attribute. Do you have instructions on setting it up/overriding/forcing Apex to use the Select2 plug-in with a tabular column form item? Any example or demo app?
Right, there’s no Select2 item type on tabular forms, that’s what this blog post is about. All the blog steps should work on 4.2
Sorry, the post is just not that easy to follow, with bits and pieces in the comments. I’m not sure why your example uses a button to apply select2. Do we have to use a button, or can we just put the code in the header? And the comments suggest you have to do other coding for it to work correctly after clicking Add Rows. You also say you like to “cheat’ by have the select2 list used elsewhere on the same page. Would you put it in it’s own region, then hide the region or have it return no records so it doesn’t display anything?
The button is for demo purposes. On a real app you want to use a Page Load Dynamic Action, just like p20 and p30 do on my demo (did you see those?).
Yes, you do need to handle the Add Rows and call .select2() on those. Otherwise the new row will have a regular select list. You need to instantiate the Select2 element on the new row.
Hope this helps!
Hi,
It is really great post, this post helped me a lot for one of my requirement.
But I have one issue when I use this select2 plugin i.e.
I have created a manual tabular from and Using Select2 on APEX tabular forms I have applied select2 options on one of the tabular form column “DNAME”.
On click ‘Add Row’ button a new row appended to tabular form, after filling the data in new rows when I click save/submit newly appended records columns values are stored in the table as I filled but DNAME column values stored as null.
When I do inspect element,
Whenever a new row gets appended in the form the select2 plugin is appending the div’s based on the count of the rows which is not allowing us to submit the data.
When I added one row the div is appending perfectly.
But when I add another row in the tabular form more than 1 divs are getting appended which is causing problem for the data to be submitted which can be seen in below figures:
I have produced same issue in test environment below are the credentials for work space/application access.
Please check and help me to overcome this issue.
https://apex.oracle.com/pls/apex/f?p=22665:20
Workspace: globle_app
Username: sayyedibrahim18@gmail.com
Password : Demo
Thanks and regards,
Ibrahim Sayyed.
Jorge,
I have loaded your app in Oracle workspace. On page 20 of your application, what would I change to make the tabular form select list a multi-select field ? Would I change the dynamic action option field and add multiple:true ?
Thanks,
Tom
Jorge,
Do you have an example of saving a mult-select value out of an Apex Tabular Form value using the Automated Row Processing using Select2 plugin ? Or is this possible ? Works fine for single select values but when adding “multi-selected” values I have a problem. Any help appreciated.
Tom
Using Select2 on APEX tabular forms..I can’t doing it.. Can you give me the instruction step by step please..
Hi Jorge, I was checking this solution but I was wondering on the 1st approach, the very 1st step… I am not sure how to include the library in the page.
You mentioned this: 1. You include the Select2 library on the page. (more on this in a moment)
But I am not sure how to include the library to be able to use the method.
Would you mind explaining this?
Thanks.
You basically have to options: The messy one is to create your own plugin to include the libraries. I’m not recommending this anymore.
The simpler one, add a Select2 Item to the page. That’s it. By adding the item you include the Select2 code into the page. If you don’t need an extra Select2, then simply hide it.
I tried the plugin it is working for already loaded rows, but when i tried to click add row button , plugin is not working.
I can implementation this code
function addRow() {
apex.widget.tabular.addRow();
$(“#reportID”).find(“.t-Report-report tr:last”).find(“select[name=’f01′]”).select2();
}
for add row button.
And i need to search list populated when i click add row button.
Please some one help me.