Multiple Select

[Callie]

Greetings,

I'm trying to create a multiple selection using a drop down list. I realise that this can not be done using a LOV and that
it also can not be done using a listbox or dropdown box.
Now, my question is...Can it be done using an ActiveX object?
and secondly, if it can be done...How?

Thank you for your time.
Callie

[akhil]

Hi Callie,
After performining multiple selects do you want to perform multiple inserts.?

[Callie]

G'Morning

It would be nice to do mulitiple inserts but that isn't a
priority. I would like to be able to search by those
multiple choices....for example search the database
for everything that is in Canada and the States.

Callie

[kmesser]

LOV's cannot perform multiple selects.


I checked Metalink (always a great first step in research), and I found a workaround. Check Note:122010.1

Essentially they instruct you on how to build multi-select "LOV-like" functionality in a form, using other form items rather than LOVs.

Good luck!

[Zee]

Hmm I have the same need. I need to allow multiple selections and then use that as a search criteria. Will you share once you have decided on a solution?

[vbaskar]

Hi

There was already questions floated on this..this can also be solved using STACKED VIEW with the desired Rownumbers..allow the user to select the records he wanted using checkbox in the corresponding rownum's and u write a algorithm for that logic and pick the records..

[Zee]

Can you elaborate what is "stacked view" or post some sample codes. would really appreciate.

[kmesser]

You may be aware that a "canvas" is necessary to display items in Forms. There are several types of canvassas, including:
content,
tab,
stacked,
. . .

A stacked canvas is used as follows:

Create a content canvas with the items you intend to display routinely. Create a stacked canvas, put content on it that you intend to display periodically, and position it over the content canvas in the appropriate location. Control the display of the content canvas, so as to make it appear that there is only one canvas, whose appearance has changed. In this way, you could put the data you wish to display in an LOV onto the stacked canvas, then display it at the appropriate time . . .

Good luck!

[kennethcha]

Hi Kmesser,

you mention there is a workaround note from Metalink to build a drop down with multiple select. What is the URL for Metalink ??

Thanks in advance

[kmesser]

http://metalink.oracle.com


Doc ID: Note:122010.1
Subject: How to select multiple values in a list of values
Type: PROBLEM
Status: REVIEWED
Content Type: TEXT/PLAIN
Creation Date: 13-OCT-2000
Last Revision Date: 12-FEB-2001


*************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************

Problem Description
-------------------

You want to select multiple values in your LOV.


Solution Description
--------------------

It is not possible with the standard list of values to select multiple values.
You can do this by creating your own lov in forms.
In the next example we will create an LOV with the same look and feel
as the standard LOV of forms

Your screen, during runtime, should look like this :

search item
-------------------
| |
-------------------

list5 item
-------------------
| |
| |
| |
| |
| |
| |
| |
| |
------------------

------ ---- --------
|find| |ok| |cancel|
------ ---- --------

Items on your form :
* two list items : list5 and list6
list6 List Style = TList
visible = NO
Elements in List => put one item with space
list5 List Style = TList
visible = YES
Elements in List => put one item with space

* textItem : search
Initial value = %

* 3 buttons : find, ok and cancel
* 2 parameters : x_postion and y_position
* record group : empgroup
select distinct to_char(empno), ename from emp
* 2 visual attributes : color and default
for default leave all the properties to unspecified
for color change the background color

Triggers :

When-new_form-instance

DECLARE
Error_Flag number;
BEGIN
set_window_property('WINDOW1',X_POS,to_number(:PARAMETER.x_position));
set_window_property('WINDOW1',Y_POS,to_number(:PARAMETER.y_position)+30);
Clear_List('list5');
Clear_List('list6');
Error_Flag := POPULATE_GROUP('EMPGROUP');
IF Error_Flag = 0 THEN
POPULATE_LIST('list5', 'EMPGroup');
ELSE
MESSAGE('Error while populating list group');
END IF;
END;


/* for list5*/
When-list-changed

declare
total_list_count number;
i number;
double number;
begin
double := 0;
i := 1;
total_list_count := Get_List_Element_Count('list6') + 1;
while i < total_list_count loop
if :list5 = Get_List_Element_value('list6',i) then
double := 1;
delete_list_element('list6',i);
end if;
exit when double = 1;
i := i + 1;
end loop;
if double = 0 then
total_list_count := Get_List_Element_Count('list6');
total_list_count := total_list_count + 1;
Add_List_Element('list6',total_list_count,:list5,:list5);
Set_Item_Property('list5',VISUAL_ATTRIBUTE,'color');
else
Set_Item_Property('list5',VISUAL_ATTRIBUTE,'default');
end if;
end;


/* for find button */
When-button-pressed

DECLARE
errcode NUMBER;
thequery varchar2(100);
BEGIN
thequery := 'select to_char(empno), ename from emp where empno like '''
|| :search || '%'||''' order by empno';
errcode := populate_Group_with_Query( 'EMPGROUP',thequery );
clear_list('list5');
populate_list('list5','empgroup');
END;


/* for ok button */
When-button-pressed

declare
total_list_count number;
i number;
my_elements varchar2(250);
begin
i := 1;
total_list_count := Get_List_Element_Count('list6') + 1;
while i < total_list_count loop
my_elements := my_elements || ','||Get_List_Element_value('list6',i);
i := i + 1;
end loop;
:global.my_elements := substr(my_elements,2);
exit_form;
end;


/* for cancel button */
When-button-pressed

exit_form;


Once you have done this you can call this form in the key-listval
of another form.

Create new form with one item : text_item

Triggers :

When-window-activated :

:text_item := :global.my_elements;


When-new-form-instance

:global.my_elements := ' ';


/* for text_item */
Key-Listval

declare
pl_id ParamList;
pl_name varchar2(10) := 'tempdata';
x_position number;
y_position number;
begin
/* Get position of item*/
x_position := get_item_property(:system.cursor_item,X_POS);
y_position := get_item_property(:system.cursor_item,Y_POS);

/* Create parameter list to give position to lov form*/
pl_id := Get_Parameter_List(pl_name);
IF not Id_Null(pl_id) THEN
destroy_parameter_list(pl_id);
end if;
pl_id := Create_Parameter_List(pl_name);
IF Id_Null(pl_id) THEN
Message('Error creating parameter list '||pl_name);
RAISE Form_Trigger_Failure;
END IF;
Add_Parameter(pl_id,'x_position',TEXT_PARAMETER,to_char(x_position));
Add_Parameter(pl_id,'y_position',TEXT_PARAMETER,to_char(y_position));

open_form('lovform', ACTIVATE, NO_SESSION,NO_SHARE_LIBRARY_DATA, pl_id);
end;


Example
--------

ORACLE_HOME/tools/devdem60/demo/forms/picklist.fmb
ORACLE_HOME/tools/devdem60/demo/forms/picklist.pll
--> another example of a multiple select of values

In this example they make use of two lists.
One showing the products available and the other list shows the selected objects.