CODEWITHSUNDEEP
oracle-databaseoracle-apexoracle-apex-5oracle-apex-5.1
Simar Singh
Simar Singh

Reputation: 415

How to create and use a multi-select list in APEX ORACLE?

I have a table called Employees with Employee_id and Employee_Name columns. Now i want to create a page with Checkbox in-front of every Employee Name, select the ones that are needed, store them into a temporary table and use them for further operations. The problem i am facing is to how to create that multi select list and store the select values in thee table. Is there an Item for multi select? If not, how should i do it?

Upvotes: 3

Views: 23913

Answers (4)

Titukedo
Titukedo

Reputation: 950

If the list is too big, I recommend to use the Popup LOV item with the Multiple Values switch activated instead the Select list or the Shuttle, because it has an internal search field for the objects list, doing way easier for the user to find the target values. Also, just as the Select List or Shuttle item, you can set a Separator character for the selected fields.

Upvotes: 0

Madona
Madona

Reputation: 227

There is an option for creating multi select list in oracle apex 5.1.

  1. Create a pageItem of type: 'select list'.
  2. Make the 'Allow multi selection' to 'Yes'.
  3. Write the SQL query for your select list under the 'List of Values' attribute.
  4. Then the select list will be displayed based on our query.

Query format is:

select [displayValue],
       [returnValue]
  from ...
 where ...
 order by ...

Now once you select multiple value from select list(using ctrl+click), these are stored as ':' separated values in the select list page item.

Upvotes: 3

Paweł Prusinowski
Paweł Prusinowski

Reputation: 416

I've created a video some times ago that covers your problem. It's a step by step tutorial how to create checkboxes and process them.

Video is available here: https://www.youtube.com/watch?v=T-LXRMWQbPk

Regards

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

There's the Shuttle item. On the left side, you'd display list of all employees. Item buttons allow you to move all (or only some of them) to the right side of the item. Once you submit the page, list of employee IDs is stored into a table column in a form of colon-separated values, for example

6547:8879:5587:9987

This is a simple way of doing that. However, once you have to actually do something with those values, you have to split them to rows. Not a problem, though. Here's a query:

SQL> with emps (shuttle_item) as
  2    (select '6547:8879:5587:9987' from dual)
  3  select regexp_substr(shuttle_item, '[^:]+', 1, level) one_item
  4  from emps
  5  connect by level <= regexp_count(shuttle_item, ':') + 1;

ONE_ITEM
---------------------------------------------------------------------
6547
8879
5587
9987

SQL>

Or, you could create a tabular form which also displays all employees and has checkboxes at the beginning of every line. You'd then create a process which - in a loop - stores selected values into a temporary table you mentioned. For example:

-- F01 = row selector. If you check 1st and 3rd row, f01.count = 2 (2 rows checked)
--       f01(1) = 1 (row #1), f01(2) = 3 (row #3)
-- F02 = EMP_ID. f02(1) = EMP_ID that belongs to employee in 1st row, 
--       f02(3) = EMP_ID that belongs to emplyee in 3rd row
declare
  l_id number;
begin
  for j in 1 .. apex_application.g_f01.count
  loop
    l_id := apex_application.g_f02(apex_application.g_f01(j));

    insert into temp_table (emp_id) values (l_id);
  end loop;
end;

Upvotes: 7

Related Questions