Creating a shuttlebox in Apex

The changelog of Apex 3.0 mentions something about featuring shuttleboxes. Don’t get too enthousiastic, you still have to do a lot of work yourself. And it’s pretty limited, because this feature assumes the use of static values. That’s not what we want! We want dynamic information from the database! Here’s a demonstration of a DIY shuttlebox with dynamic data.

Shuttleboxes are very convenient for updating tables with an N:M relation. A project can have multiple workers and a worker can be on multiple projects.

So we have table WORKER( ID, Name, Department_id) and table PROJECT (ID, Projname, startdate, enddate). And a table who combines them in an N:M relation; WRK_PRJ( id, project_id, worker_id).

For me, the most convenient way to create such a function, is to start with a blank new page, on which I create an HTML region. As region template, I choose Forms region.

In the region, I place a Select list with submit. The LOV lists all workers with the query “select name name, id id from worker order by 1”.

Then, I add two Multiselect items, one for the projects added to a worker, and one for projects which are not added yet. Assign a LOV to both the multiselect lists, either by entering a query or creating a named LOV first and assigning that to the item. Set the “Display null” option to No.

The first multiselect is based on this query:

select projname, id from project
minus
select prj.projname, prj.id from project prj, wrk_prj wpj
where prj.id = wpj.project_id and : p7_wrk = wpj.worker_id

Select all the projects that are not assigned to the selected worker.

The second multiselect contains this query:

select name name , id id from project where id in (Select project_id from wrk_prj where worker_id = : P7_WRK )

Select all projects that are assigned to the selected worker in the wrk_prj table. Note that it’s possible to write this query in many ways. Keep in mind that the LOV editor in ApEx might have some difficulties with certain constructs, like inline views and unions. If your query becomes complex, consider, for instance, creating a view..

Set the Element properties height, width and max width to 10, 40 and 40, and for the second multiselect, the Begin on new line property to No.

Now, we need to add some buttons for moving contents between the shuttles. I create them with the Add Button wizard; choose amongs region items and atyle Template based button. One has both label and request Add, the other Remove.

There we are, some buttons. Now al we have to do is add logic to the buttons and we’re done!

For that, I create two processes, called addprj and rmvprj. Their properties are: Process type PL/SQL; Process point On Submit – Before computations and validations; Run process Once per page visit. The process is conditional; Condition type is Request = Expression 1 with expression 1 respectively add and remove (remember, those are case sensitive). Alternatively, you can set the When button pressed condition to the corresponding button name.

The Proces source for adding a project to a worker:

 declare
l_vc_arr    HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
begin
l_vc_arr := htmldb_util.string_to_Table(V(‘P7_TOADD’), ‘:’ );
FOR z IN 1..l_vc_arr.count
LOOP
insert into wrk_prj
( id, worker_id, project_id )
values
( wpj_seq.nextval, v(‘P7_WRK’), l_vc_arr(z) );
END LOOP;
end;

And for removing a project from the list:

declare
l_vc_arr    HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
begin
l_vc_arr := htmldb_util.string_to_Table(V(‘P7_ADDED’), ‘:’ );
FOR z IN 1..l_vc_arr.count
LOOP
delete from wrk_prj
where worker_id = v(‘P7_WRK’)
and project_id = l_vc_arr(z) ;
END LOOP;
end;

As you see, I use some standard Apex functions and types. You can select multiple items in a Mulitselect list (hence the name ;)), and when you read the contents of the item, you’ll see that it’s an colon-separated list with the values (in our case, the primary key). With the htmldb_util.string_to_table you can put those in an array and read them out in a loop.

Once you have the id’s you can add or delete the corresponding records one by one.

Problem is that you don’t want to be able to add or remove items when you have no worker selected. To prevent that, I created a new region with conditional display. The condition: Value of item in expression 1 != expression 2. The item is P7_WRK, the value is 0. All items except P7_WRK go into the conditional displayed region.

And with that, we have a working shuttle box!

There’s one additional nifty little feature: If you modify the property HTML Form Element Attributes in the multiselect items with the value

ondblclick=”javascript:doSubmit(‘add’);”  (and remove for the second box ofcourse)

you can doubleclick one value and it is immediately added to the other box. Nice!