Wednesday, March 19, 2008

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.

13 comments:

Arie said...

Hi Joel,

Very useful entry on a problem that seems to be around forever. Now we have one good reference for a good solution.

When Vikas was still an active member of the forum (and I really hope he is OK) he tried a bit different approach to this problem. He actually changed the pagination mechanism to save the changes of each tabular form view, prior to pagination - http://forums.oracle.com/forums/thread.jspa?threadID=476665 . This way all the changes, on all the columns were saved. I believe your approach can be extended to include more columns as well.

One last remark. In your JavaScript code, you are using the $v() built-in function. I believe this function is available for version 3.1 only.
For those who will try to use your code on earlier versions, they should use the $x('pFlowId').value or html_GetElement('pFlowId').value syntax.

Regards,
Arie.

Carl Backstrom said...

@Arie Vikas is alright I've had IM conversations with him in last week.

@Joel There were a couple 3.1 features that you had there, here's is an example of 3.0 and 3.1 and also it changes the AJAX to asynchronous so the UI works a bit quicker.

In a 3.0 instance create a Application Level Item named TEMPORARY_ITEM and then use this code.

/*
Pre 3.1
*/
function f_UpdateCollection( cb ){
var get = new htmldb_Get(null,$x('pFlowId').value,'APPLICATION_PROCESS=UpdateCheckboxValue',0);
get.add('TEMPORARY_ITEM',cb.value);
get.GetAsync(function(){return;});
get = null;
}

In 3.1 and going forward you don't need the TEMPORARY_ITEM and you can use this code.


/*
3.1 and up
*/
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;
}

Joel R. Kallman said...

Arie & Carl,

Thanks for your review and very helpful comments & additions.

Joel

John said...

Great reel, Joel! We are running ApEx 3.0 (hopefully will be upgrading to 3.1.1 ASAP). Given Carl's comment about using an Application Item, would I need to change my Application On-Demand Process to account for that?

I'm trying to modify this line:
l_value := wwv_flow.g_x01;

to:
l_value := &TEMPORARY_ITEM.;

If you could point me in a better trajectory, I'd greatly appreciate it.

Thanks Again for the wonderful blog!! --John

John said...

Don't ask me why it wasn't working before...but it is now. I may have had some other small typo in the on demand process. But anyway, using ApEx 3.0, you should change the on demand process to utilize the application item that you created.

E.g: change:
l_value := wwv_flow.g_x01;

to:
l_value := :TEMPORARY_ITEM;

Bill said...

Hi Joel,

The code (javascript) seems to work fine with Firefox, but (not surprisingly) not with IE7. Any hints as to why?

Thanks,
Bill Ferguson

Bill said...

Nevermind. It wasn't the javascript, but the on demand process that had the error. I have it working fine now.

Bill Ferguson

Anonymous said...

How can I modify this "Preserving checked checkedboxes in a report" example to include all the fields in the select statement. In addition to Preserving checked checkedboxes, I want to add all the other column values in the select statement to the 'EMP_COLLECTION' collection.

Thanks for posting this example!

Anonymous said...

thank you for this nice post. I was wondering how we can add "select all" button to this solution?

thanks in advanced,

Satya said...

Hi Joel,
I am new and learning apex and trying to implement the collections query in my page. I got this error.
--
1 error has occurred
The report query needs a unique key to identify each row.
The supplied key cannot be used for this query. Please edit the report attributes
to define a unique key column. ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
--
Please help me to resolve this.
Satya

Joel R. Kallman said...

Hi Satya,

You are best to please post your question on the OTN discussion forum at http://forums.oracle.com/forums/forum.jspa?forumID=137.

Thanks.

Joel

Unknown said...

hi Joel,

very good post.

I've tried to everything that you did + changes suggested by Carl.

Can't get it to work at all.

javascript can't seem to run the app level pl\sql code that creates the collection etc.

I'm running 11gr2 and apex 4.22.

any further suggestions will be much appreciated.

thanks.

Joel R. Kallman said...

Hi "Unknown",

I'm not sure what the problem is. Even the example I wrote back in 2008 still works. http://apex.oracle.com/pls/otn/f?p=138:1:0

Joel