Preserving checked checkboxes in a report

Paul from Oracle recently sent me an e-mail with the following request:

"I am using the report template to display the database table contents. Before each report data in the display I added a checkbox to allow user to select the data. I can get the checked data id from global package array apex_application.G_F01 when user submits the page. However, this array is cleared whenever the user browses the report to the next page using the ApEx navigation. How could I save the checked checkbox when user is browsing the report using "next" or "previous" links? This issue also appears if you change the number of rows displayed. I looked on the APEX forum on OTN and could not find a solution. Any help you can give is greatly appreciated."

I understood the problem exactly. Paul has a report on a page with a checkbox column (presumably using APEX_ITEM.CHECKBOX). A user will check a few boxes and then click the Next link to display the next set of rows in the report. But now when the user goes back to the first page, the original checked checkboxes are no longer checked. This is because the checked checkboxes are not preserved in session state.

I poked around the OTN forum myself and could not find this addressed. I even browsed Carl's and Denes' demonstration applications, and even though there are all kinds of exotic examples, this simple (and seemingly common problem) didn't seem to be addressed - at least not for the common man.

Using Application Express collections and a little AJAX, we should be able to solve this rather elegantly.

In my workspace on apex.oracle.com, I:

1. Started the creation of a new application
2. Added a report, Page Source = Table, Table Name = EMP, Implementation = Classic
3. Clicked the Create Button

When running this application, you should get something that looks like:




Standard stuff, right?

Now, to add the checkbox using the APEX_ITEM.CHECKBOX API, I changed the SQL query and added one line:

select
apex_item.checkbox(1, empno) cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP"
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)


Now, after shuffling the cbox column up in the report display order and clearing out the column header, it should now look like:


Now here's where the fun starts. You can liken Application Express collections to temporary tables - they're an amorphous bit bucket to store vectors of information, in contrast to the scalar declarative item types. The nice thing about collections is that they are tied completely to an Application Express session. As those get cleaned up automatically, so does the data in collections. So we will use an Application Express collection to maintain a list of the EMPNO values of the checked rows.

First, I created an unconditional On Demand application-level process named "UpdateCheckboxValue" using the following code:

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 colleciton, 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;

So this elegant piece of code will take care of creating a collection named EMP_COLLECTION, and then determine if we should add or remove the value from the collection. The name of the process, "UpdateCheckboxValue", is relevant, as we'll use it next.

Next, after glancing at Carl's simple AJAX and On Demand example, I figured out the JavaScript I needed. So I edited the page attributes of page 1 and added the following to the HTML Header section:

<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);
gReturn = get.get();
get = null;
}
//-->
</script>



Now, I have a JavaScript function on my page which will invoke the On Demand application-level process I defined above. All I need to do is hook it up. To do that, I can now modify the call to APEX_ITEM.CHECKBOX to invoke this function in an 'onchange' event:

select
apex_item.checkbox(1, empno, 'onclick="f_UpdateCollection(this)"') cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP"
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)

If you now run the application and click the checkboxes, you may notice a slight lag. That's because, behind the scenes, a request is being made from your browser to the Application Express engine, which is calling the On Demand application-level process and modify data in the collection for your session.

But we're not quite done. We need to somehow "merge" what we have stored in the collection with the query for our report. This can be achieved by modifying the call to APEX_ITEM.CHECKBOX to add the fourth argument which indicates if a value should be checked or not, and also modifying the query itself to perform an outer join against APEX_COLLECTIONS.

select
apex_item.checkbox(1, empno, 'onclick="f_UpdateCollection(this)"',a.c001) cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP", apex_collections a
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)
and a.c001 (+)= empno
and a.collection_name (+)= 'EMP_COLLECTION'


This query will still report on every row in the EMP table, and optionally on the EMP_COLLECTION where the join condition is met. In the reference to a.c001 in the call to APEX_ITEM.CHECKBOX, for those rows in EMP for which there is no match, the resultant value will be NULL. For those rows which do match (a.c001 = EMPNO), then the value of
a.c001 should be the corresponding EMPNO value, and this will result in the checkbox being checked.

There certainly will be a need to clear the collection, so I added a PL/SQL process on the page which will execute on Submit and when the Reset button pressed of:

apex_collection.create_or_truncate_collection(
    p_collection_name => 'EMP_COLLECTION' );
commit;

This will clear the collection if it exists, and create it if it doesn't exist. I easily could have used apex_collection.truncate_collection, but I'd have to account for the case where the collection is not already created.

The complete application is here: http://apex.oracle.com/pls/otn/f?p=blog_20080319:1:0

I completely realize some "expert" is going to flame me, telling me this was solved in 2004 and here's the proof. But I was astonished that I couldn't find an article discussing this type of problem and solution.

Caution: The implementation above can be quite "chatty". Every time someone clicks a checkbox, they will make a request to the Web server and the database. There are other ways to implement this solution, but this will work for reports with and without PPR.