Creating a shuttlebox in Apex

3

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!
 

 

Share.

About Author

3 Comments

  1. Hi,
    Your solution really help me to get this shuttle working. Infect, solution described in apex forum by Carl, did not work for me.

    I still have one issue though. When there is no item in list2, width of multiselct2 changes to narrow. How can fix the width of second multiselct list item?

    Thanks!!

  2. I checked again, you’re right. I thought that in the second shuttle only static values could be entered, but it is possible to provide a query there also. The Apex shuttle function has a layout which is more neat and it takes some work out of the hands of the developer, but I think the custom shuttle is more versatile and offers more options for specific functionality.

  3. Hi Patrick,

    >And it’s pretty limited, because this feature assumes the use of static values.

    I can’t really confirm this statement. The new Shuttle in APEX 3.0 supports a lov query as data source. You don’t have to crate static values. The Shuttle is basically a normal multi-select list which returns the selected values as colon separated list when you submit the page. You have to create a process which parses the string and put the single values into your assignment table.

    Patrick