The Lame Story

*** The names and events in this post are fictional and not based on any real person or event. ***

After the fifth consecutive record breaking year at Jefeb Inc., the management team was finally convinced to reward it's employee with an employee outing.  The local sports team was in the middle of a mediocre season, therefore,  the Wednesday early afternoon game would offer a cheap group package which the company could take advantage of.  

To "build team camaraderie" the company would provide department vans to transport all employees to and from the game.  The company president's early career, which seemed to be eons ago, was that of a first grade teacher.  A fine educator back in the days he still held on to the feelings of providing for the care and safety of those under his care when it came to "field trips".  One way to provide this safety would be to make sure that all those that were taken to the game would return and not be left behind.  This would be done by keeping track of all the employees in each van.  Instead of using the ancient tools of paper, pen, and clipboard he would need to have the lists create and managed with the new fandangled computer gizmo what-cha-ma-call-its.  

So the call was made to your neighborly friendly APEX developer who quickly create a couple pages to manage the list of employees per van.  Each van was assigned to a department and then a select list of employees in the van's department was used to pick and add employees to the van's passenger list.

It didn't take long for a problem to come up though.  The accounting department only had a few employees; plus the fact that they couldn't all be in the same room for more than 5 minutes before a SWAT team needed to be called to disperse the resulting frenzy.  Throwing them all in the same van would be disastrous and a huge concern to the safety of the general public.  

How would the user be able to add a name a van that isn't part of the van's department?  Wouldn't it be nice if there were a quick way to add a name to the select list?  Let's see how.

The Technical Stuff

Combine a Select List with a Popup LOV.  

The Select List will be a subset of data such as a list of employees for a department.  

The Popup LOV will be a greater set of data such as a list of all employees.  

When an employee is selected from the Popup LOV the name will be added to the Select List and selected.

  • The Select List item (P89_PASSENGER) is initially created with the query to list all employees in the department:
SELECT ename d, empno r 
  FROM emp
 WHERE deptno = :P89_DEPTNO 
  • The Popup LOV (P89_PASSENGER_EXTRA) is created with a query to list all employees:  
SELECT ename d, empno r
  FROM emp
 ORDER BY ename

The sequence will also be set to a value to make the item appear after the Select List and the "Start New Row" and "New Column" options will be set to No.  Also the "Form Element Width" option is set to 1.

  • To hide the box which shows the value of the Popup LOV a dynamic action is created to run on Page Load which executes some javascript to hide the box.

Dynamic Action

Name: Hide Passenger Extra Box 

Event: Page Load  

 True Event 

   Action: Execute JavaScript Code

   Code: $("#P89_PASSENGER_EXTRA.popup_lov").hide();

      [ I used my broswer's developer toolkit to view the source of my page to discover the textbox I want to hide has an id of P89_PASSENGER_EXTRA and a class of popup_lov ]

  • The select list is modified to include the popup LOV value when it is selected.  

Cascading LOV Parent Item(s): P89_PASSENGER_EXTRA

Page Items to Submit: P89_DEPTNO

List of values definition:

SELECT ename d, empno r 
  FROM emp
 WHERE ( deptno = :P89_DEPTNO OR empno = :P89_PASSENGER_EXTRA )

 

  • Finally a dynamic action is created to set the Select List's selected value to the value picked from the popup LOV.

Dynamic Action

Name: Set Passenger to Extra Passenger When Picked

Event: After Refresh

Selection Type: Item(s)

Item(s): P89_PASSENGER 

 True Event 

   Action: Set Value

  Set Type: JavaScript Expression

  JavaScript Expression: $v('P89_PASSENGER_EXTRA')

  Selection Type: Item(s)

  Item(s): P89_PASSENGER

  Code: $("#P89_PASSENGER_EXTRA.popup_lov").hide();

The final results:

A Select List which lists all the employees in a department.

After the popup LOV is used the selected employee is shown in the Select List.

 

The Example

Example pages at http://apex.oracle.com/pls/apex/f?p=9487:88