Tuesday, January 20, 2009

APEX Report with checkboxes (advanced).

Note: This solution was updated to account for APEX's new features and APIs. See the updated post here: http://www.talkapex.com/2015/09/report-with-checkboxes-update.html

A colleague of mine wanted to build a report with check boxes. Once the user selected all the rows, they would click submit and the application would process the rows. Sounds pretty simple and straight forward however he did have some extra requirements:

- The report is an interactive report
- There may be up to 10,000 records in the report
- When the user "scrolls" through the report (i.e. uses pagination), if they checked off a box it should remain checked the entire time (i.e. if they check an row in the 1st 15 rows, then view rows 16~30, then go back to rows 1~15 it should remain checked)

So here's the example on how to do it. The working example can be found here: http://apex.oracle.com/pls/otn/f?p=20195:500

Create an application item F_EMPNO_LIST. Note you can use a page item as well...

Setup IR:
SELECT apex_item.checkbox (1,
                           empno,
                           'onchange="spCheckChange(this);"',
                           :f_empno_list,
                           ':'
                          ) checkbox,
       empno, ename, job
  FROM emp

Once the report is setup set the row display to 5 (you'll need it for this example)

- Add an HTML region and add the following code: (Note: the jQuery call is not needed... for now)


CHECKBOX List:

&F_EMPNO_LIST.

Now create an application process (on Demand) called: CHECKBOX_CHANGE
-- Application Process: CHECKBOX_CHANGE
-- On Demand...

DECLARE
  v_item_val NUMBER := apex_application.g_x01;
  v_checked_flag VARCHAR2 (1) := apex_application.g_x02;
BEGIN
  IF v_checked_flag = 'Y' THEN
    -- Add to the list
    IF :f_empno_list IS NULL THEN
      :f_empno_list := ':' || v_item_val || ':';
    ELSE
      :f_empno_list := :f_empno_list || v_item_val || ':';
    END IF;
  ELSE
    -- Remove from the list
    :f_empno_list := REPLACE (:f_empno_list, ':' || v_item_val || ':', ':');
  END IF;

  -- Just for testing
  HTP.p (:f_empno_list);
END;

- On the post page create a query to view data (you can process how you need/want)
select *
from emp
where instr(:F_EMPNO_LIST, ':' || empno || ':') > 0

Please note there may be better/faster ways to implement the SQL code etc.

Here are some links on the x01 code that I used:
http://carlback.blogspot.com/2008/03/new-stuff-2-x01-and-friends.html and http://carlback.blogspot.com/2008/04/new-stuff-q.html

38 comments:

  1. Hi Martin

    Nice article.
    It's the first time I've visited your site - keep up the good work.

    ReplyDelete
  2. A very good article. I had a similar requirement and could use this as an example. One issue I have though is with the caching of values. The older values for the :f_emono_list seem to remain even after we go to different screen and comeback again. If you could elaborate on that, it will be great.

    cheers,
    Rams

    ReplyDelete
  3. Hi,

    Thanks for all the feedback!

    Rams, they're a lot of options to reset :f_empno_list. Here's a simple solution:

    Let's say you're report is on P10. Have a page computation (on P10) that sets F_EMPNO_LIST to null on page load before header.

    This may not work in all situations. If you have other requirements please let me know and I can post some alternate suggestions.

    Thank you,

    Martin

    ReplyDelete
  4. Hi Martin,

    thanks for the response. I did exactly the same to clean it up on loading. The issue is, when I go to the next page in the tabular form, the value shown on page for :f_empno_list disappears. When I select a row, it shows up again with all the values selected including the ones from the previous page. Thats fine. But when I go back to the previous page, f:_empno_list disappears again, and none of the rows are shown as selected. I select a row here, then :f_emp_no shows up again. Am not sure if I am missing something here. Any suggestion from you would be of great help.
    Thanks.

    cheers,
    Rams

    ReplyDelete
  5. Martin,

    please ignore my last post. I had the javascript function on the same region as the report. I moved the function to a separate html region, and now things are working fine. I added a computation to initiate the list with null. This works perfect now.
    Thanks a lot.

    cheers,
    Rams.

    ReplyDelete
  6. Martin,

    I replied to a post of your in the Apex Forum yesterday, but I wasn't sure if you were watching the topic. Sorry to intrude but this is the only other way I thought of to contact you for an answer.

    The reply is here:

    http://forums.oracle.com/forums/thread.jspa?messageID=3259152

    Thanks,

    Stew

    ReplyDelete
  7. WOW...So simple! You also helped me to finally figure out how the apex_item.checkbox works as well!!

    Thank you!!
    Janel

    ReplyDelete
  8. Hi,
    Nice article : i have the same constraints for checkboxes and pagination.
    Does this tip needs APEX 3.1+ or can we do this with Apex 3.0, thanks

    ReplyDelete
  9. Hi martin

    Where do the $v and $x functions come from in your javascript code : APEX? jQuery?

    In my case, on APEX 3.0, i got an error : function is not defined

    Thanks for your help

    ReplyDelete
  10. Anonymous wrote:

    > Where do the $v and $x functions come from in your javascript code : APEX?

    Yes, they're from Apex. They're Javascript functions that Apex provides. Check the Apex API documentation, though it's pretty brief.

    ReplyDelete
  11. > Does this tip needs APEX 3.1+ or can we do this with Apex 3.0?

    Yes this works with Apex 3.0

    ReplyDelete
  12. Hi Martin - you just saved me heaps of effort and showed me how these controls work. Thanks for the great explanation - Awesome!

    ReplyDelete
  13. very good article. I was just wondering how you would put in a row selector, so you can tick one item and they would all become ticked. For example if you had a page with 20 visible rows. Any thoughts?

    thanks

    -Marc

    ReplyDelete
  14. Hi Marc,

    Do you mean that you'd like to have a "Select All" checkbox at the top?

    Martin

    ReplyDelete
  15. I would love to have a "Select All" checkbox at the top. :)

    ReplyDelete
  16. Hello Martin,
    I followed the steps explained in the blog...Everything is working fine uhtill I select 150 rows and hit go. I am getting ORA-01460: unimplemented or unreasonable conversion requested error. Any thought?

    Suman

    ReplyDelete
  17. Hi Suman,

    I'm not sure why you're having this issue. Can you replicate on apex.oracle.com and email me some login credentials (my email is at the top right corner of this page).

    Martin

    ReplyDelete
  18. how can you do this using a select box instead of a check box. that is the current issue i am having. do you have any solutions?

    ReplyDelete
  19. Hi Andrew,

    Instead of using apex_item.checkbox to produce the check box you can use apex_item.select_list Here is a link to the APEX_ITEM API http://download.oracle.com/docs/cd/E17556_01/doc/apirefs.40/e15519/apex_item.htm#CACEEEJE They're several methods to generate a select list.

    Martin

    ReplyDelete
  20. Hi Martin,
    Does this work with apex 4.0?
    I followed the steps, but it doesn't seem to work.

    Angela

    ReplyDelete
  21. Hi Angela,

    I haven't tested this in 4.0 so I'm not sure. You could try to use Dynamic Actions to get similar results.

    Martin

    ReplyDelete
  22. Hi Martin,
    Thanks for your code. I copied and modified your code and tried to display the empno in multiple lines... meaning, select the line number(radio button) and then check the "checkbox". The code works and displays empno according to the selected line. The only issue is every time I have to refresh (press F5) the page to see the selected value in the corresponding line. I want to display the value the moment checkbox got checked like yours. The lines are "Display only" items.

    Please help me if you can.
    Thanks again,
    Rajan.

    ReplyDelete
  23. Really nice Martin and it's a big help.

    I got everything above working in Apex 4, but it would be nice to have a button that clears all the checkboxes that have been checked so you can reset the screen.

    Thanks a lot!
    Jon

    ReplyDelete
  24. Prashanth Raju (APEX Developer - Stanford )February 9, 2012 at 4:35 AM

    Excellent work martin. Its really helpful.

    Thanks
    Prashanth raju

    ReplyDelete
  25. Hi Martin

    really nice work!
    Do you have any idea how to make a button which selects all the checkboxes?

    Thanks!
    Belinda

    ReplyDelete
    Replies
    1. Hi Belinda,

      Search the internet or APEX forums for Dynamic actions. You can easily do that with a button.

      Martin

      Delete
  26. Just one question:

    How to load jQuery without using google link?

    Application is in NO Internet network :)

    ReplyDelete
    Replies
    1. You need to download that jquery file and store it in your apex directory. After that you need to change the source src="http://www.google.com/jsapi" to where you have the jquery file.

      Delete
  27. hi Martin,
    I have a requirement to execute a pl/sql block when a checkbox (like in the example above) is checked ,without the page been submitted.
    I am able to do that, but the issue is I need to pass the checkbox value to the procedure. I tried getting the value in apex_application.g_f01 item, but looks like it is only populated when the page is submitted.
    Is there a work around to it?
    thanks a lot!
    Debyani

    ReplyDelete
    Replies
    1. You'll need to explicitly pass that value using an AJAX call. Your best bet will be to do this using a dynamic action.

      Delete
  28. Hi Martin,

    I am trying to create IR report region with the example you have given. But I am getting error: 'Query cannot be parsed, please check the syntax of your query. (ORA-04044: procedure, function, package, or type is not allowed here)'

    can you please tell how to resolve this?

    I am using apex version 4.2.2.00.11.

    Thanks in advance!

    ReplyDelete
    Replies
    1. Did you try to run your query in SQL*Plus? You'll need to verify your syntax.

      Delete
  29. Very helpful article!
    I use it to add records to a table. When user click add button selected rows are added to table and javascripcode executed (as dynamic action)

    $('input[type=checkbox][name=f01]').attr('checked',false);

    This marks all checkboxes as unchecked but ... :f_empno_list is not cleared and

    begin
    :f_empno_list := null;
    end;

    doesnt work
    What would be the best way to reset the f_empno_list?
    Could you give me any advise?

    ReplyDelete
    Replies
    1. I'm not sure why :f_empno_list := null; did not work. Are you sure the ajax request actually went through?

      Delete
  30. Hi Martin,

    if you keep the info about which checkbox is ticked on the client-side (let's say a hidden DOM element), should you not achieve better performance? Doing the server-cycle every time a checkbox is ticked seems like an overkill to me.

    MM

    ReplyDelete
    Replies
    1. It's stored on the server side so that it supports pagination. For example lets say your report has 100 rows but you're own displaying 15 at a time. On rows 1~15 you check off 3,4,5. You then paginate to rows 16~30 and check off row 20. If you paginate back to rows 1~15 you'll still see 3,4,5 checked off since we stored it on the server and it's part of the query.

      Delete
  31. Hello Martin, does it works in Apex 5? Can you provide screens maybe? I was trying without success in Apex 5.. Thanks for hints and help.

    ReplyDelete
    Replies
    1. There's an updated link at the top of the page. That should work in APEX 5.

      Delete