APEX

I have been tossing this around for a while now and can’t seem to come up with a solution from examples and docs. I know I'm missing something, but I don't know what. I am hoping one of you can shed some light on what I missing to get me back on track or on a new track if this one is wrong.

I am trying to select multiple records in a report then go to a second page to add a single status and comment for the group of selected records. The status, comment, and some of the original report data (like primary key) will be merged into a secondary table once I get the collection working. I would do this all on one page but I also have a number of criteria selections on the first page that I think would become too busy if I also added the status and comment fields.

In order to create this report, I think I need to use a collection to hold the selected records. I can get the PK and associated fields from a simple SQL report into a collection but I can only get the PK from PL/SQL. I can use the PK to pull the data from the database again but I hate the thought of hitting the database again for data I have on the screen. I am using PL/SQL to allow the WHERE clause to dynamically change based upon the criteria selected.

For the collection, I am using the method from Joel Kallman’s blog (http://joelkallman.blogspot.com/2008/03/preserving-checked-checkboxes-in-report.html) to preserve the rows as they are selected. It works great for the item listed in the apex_item.checkbox item but I can’t find an answer to how to also bring some of the other fields from the report into the collection.

I have posted a simplified example on OTN for your review/comments. It does not contain the criteria mentioned above to eliminate complexity from the example. I am using APEX version 3.2.0.00.27.
Workspace: bobs
Username: guest
Password: abc123
Application: Multi Select Edit – 60803
Page 1 will give you a menu to select the SQL or PL/SQL version of the pages.

I would also love to be able to have the multi select working at the heading of the checkboxes in the PL/SQL report but I can’t get it to work properly. The boxes will all show as checked but the data doesn't get put into the collection unless the checkbox for each row is individually selected. This feature is not in the example since it is a lower priority.

The basics of the example PL/SQL report are as follows._

The report source is:
DECLARE
q VARCHAR2(32767); -- query

BEGIN
q := 'select apex_item.checkbox(1, empno, ''onclick="f_UpdateCollection(this)"'',a.c001) cbox, '||
' "EMPNO", '||
' "ENAME", '||
' "JOB", '||
' "MGR", '||
' "SAL", '||
' "DEPTNO" '||
' from "EMP", apex_collections a '||
' where '||
' a.c001 (+)= empno '||
' and a.collection_name (+)= ''EMP_COLLECTION'' ';

RETURN q;
END;

The HTML Header for the page contains the function that is called from the onclick statement.
<script type="text/javascript">
<!--

function f_UpdateCollection( cb ){
var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=UpdateCheckboxValue',0);
get.addParam('x01',cb.value);
get.GetAsync(function(){return;});
get = null;
}
//-->
</script>

And the Application Process that populates the collection is:
declare
l_value varchar2(4000);
l_seq_id number := 0;
l_collection_name constant varchar2(30) := 'EMP_COLLECTION';
begin
--
-- Get the value of the global which will be set in JavaScript
--
l_value := wwv_flow.g_x01;

--
-- If our collection named EMP_COLLECTION doesn't exist yet, create it
--
if apex_collection.collection_exists( l_collection_name ) = FALSE then
apex_collection.create_collection( p_collection_name => l_collection_name );
end if;

--
-- See if the specified value is already present in the collection
--
for c1 in (select seq_id
from apex_collections
where collection_name = l_collection_name
and c001 = l_value) loop
l_seq_id := c1.seq_id;
exit;
end loop;

--
-- If the current value was not found in the collection, add it. Otherwise, delete it from the collection.
--
if l_seq_id = 0 then
apex_collection.add_member(
p_collection_name => l_collection_name,
p_c001 => l_value );
else
apex_collection.delete_member(
p_collection_name => l_collection_name,
p_seq => l_seq_id );
end if;
commit;
end;

The final report is a simple select from the collection.
select *
from apex_collections
where collection_name = 'EMP_COLLECTION'



Thank you for your time.
Bob