Forms LOV : avoid the user to select twice the same value

13.10.2006

It is not the first time I see this question on forums:
"How can I restrict the values of my LOV, that the end user cannot select twice the same ?"

One solution is to use a record group with a WHERE clause that excludes the records that has just been posted.

Another solution, that needs no POST is to remove the rows from the record group.

Assume the record group select order is the following:

select empno, ename from emp

The automatic refresh of the LOV must be set to NO

Assume the LOV is attached to the EMP.EMPNO item, add the following code to its When-Validate-Item trigger:

--
-- Remove the selected value from the record group
--
DECLARE
rg_id RecordGroup;
rec_count NUMBER;
BEGIN

-- Get the record group ID --
rg_id := Find_Group( 'EMP_GRP' );

-- Get the number of rows --
rec_Count := Get_Group_Row_Count( rg_id );

-- delete the selected row --
FOR j IN 1..rec_Count LOOP
  If Get_Group_Number_Cell( 'EMP_GRP.EMPNO', j ) = :EMP.EMPNO Then
     Delete_Group_Row( rg_id, j );
     exit ;
  End if ;
END LOOP;

END;

Of course, if the end user clear or delete the record, you should add the row to the record group.