Sunday, September 13, 2015

Report with Checkboxes (an update)

Supposed you have a report with checkboxes. Once the user selects all the rows, they can submit the page and the application would process the rows. Sounds pretty simple and straight forward however they're some additional 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). Same applies if a user uses the IR filters.

If you follow this blog and the above sounds familiar, that's because it is. I wrote about this problem a very long time ago. A lot has changed since 2009 and an update on the original post is long overdue. Here's the updated (and simplified) solution.


Create Item to Hold List of IDs


Create a hidden APEX item which will contain a comma delimited list of all the IDs that are to be checked off (in this example it will be P1_EMPNO_LIST). Be sure to modify the Value Protected attribute to No. This is critical as this item will be updated via AJAX and can not have any hashing/security applied to it.

If you are loading this from a cross reference table you can use the following query in a Before Header process. This query will load all the employees from the Accounting department.
select listagg(e.empno, ',') within group (order by e.empno)
from dept d, emp e
where 1=1
  and e.deptno = d.deptno
  and d.dname = 'ACCOUNTING'

Create IR Report with Checkboxes


Create an IR with the query below. Note the p_attributes value. This is critical as we need to identify the checkboxes that should be monitored.
select 
  apex_item.checkbox2(
    p_idx => 1,
    p_value => e.empno ,
    p_attributes => 'class="empno"',
    p_checked_values => :p1_empno_list,
    p_checked_values_delimiter => ',') checkbox,
  e.ename, 
  e.job
from emp e
In the report attributes set the Page Items to Submit to P1_EMPNO_LIST. Each time the report is refreshed (pagination, filters, sorting, etc) the active list of selected values will be submitted.

Create Dynamic Action (DA)


Create a new DA with the attributes in the below image. This DA will append/remove the comma delimited list of IDs in P1_EMPNO_LIST. Note the jQuery Selector value must match what was used in the IR query above.


Configure a True action as shown below (JS code follows).

var
  //Checkbox that was changed
  $checkBox = $(this.triggeringElement),
  //DOM object for APEX Item that holds list.
  apexItemIDList = apex.item(this.affectedElements.get(0)),
  //Convert comma list into an array or blank array
  //Note: Not sure about the "?" syntax see: http://www.talkapex.com/2009/07/javascript-if-else.html
  ids = apexItemIDList.getValue().length === 0 ? [] : apexItemIDList.getValue().split(','),
  //Index of current ID. If it's not in array, value will be -1
  idIndex = ids.indexOf($checkBox.val())
;

//If box is checked and it doesn't already exist in list
if ($checkBox.is(':checked') && idIndex < 0) {
  ids.push($checkBox.val());
}
//If box is unchecked and it exists in list
else if (!$checkBox.is(':checked') && idIndex >= 0){
  ids.splice(idIndex, 1);
}

//Convert array back to comma delimited list
apexItemIDList.setValue(ids.join(','));

Demo


That's all that's required. Now each time a checkbox is checked/unchecked P1_EMPNO_LIST will be updated to reflect these changes. The checkboxes will persist each time the report is refreshed. You can see the checkbox implantation in this demo.

Considerations


This solution is fairly simple to create an manage however it does have one small caveat. If the list of checked items is very large (more than 4000 characters) you may run into some varchar2 issues. In most cases this shouldn't be an issue but if it is you should test first.

To process the list of comma delimited list you can use the apex_util.string_to_table function and loop over the table values. If you want to use the comma delimited list in a query the following example should work (again it does have varchar2 size limitations).
select regexp_substr(:p1_empno_list,'[^,]+', 1, level) empno
from dual
connect by regexp_substr(:p1_empno_list, '[^,]+', 1, level) is not null

17 comments:

  1. Nice example and simple instructions.
    I also wrote about this topic. My solution UX is a little different; the main thing is select all checkbox in the header and click anywhere in the row to select. This makes the JavaScript quite different; larger. The general idea of using page items to submit is the same.
    If checkbox2 can handle a larger list of selected items than instr, which I used, then it is well worth the overhead to have the f01 values submitted.
    http://hardlikesoftware.com/weblog/2015/07/24/apex-interactive-report-checkbox-row-selection/

    ReplyDelete
    Replies
    1. Agreed that using a page item which gets submitted with report refresh is a lot easier to manage/maintain than f01/collection. In most cases the checked items is less than 4000 so our solutions will work.

      I like your implementation to handle clicking anywhere on the report (for others readers you should check out John's link above).

      Delete
  2. Won't this mean than items that have been checked, remain checked after a filter is applied that excludes some of the previously checked items?
    If a page process is to act on the checked rows then it will need to account for this.

    ReplyDelete
    Replies
    1. Yes you're correct. If a filter is applied or pagination occurs the items that were checked remain checked, despite the user not visibly seeing them. I'm not sure what you mean by "If a page process is to act on the checked rows then it will need to account for this". The code currently handles this situation.

      Delete
  3. Martin,
    This works great in Chrome and firefox but doesn't seem to work in IE8.
    The javascript doesn't update the p1_empno_list item...

    Any thoughts?

    ReplyDelete
  4. Ahh, fixed it...
    It was the old javascript indexOf issue with IE...
    I added this to the top of your script.

    if (!Array.prototype.indexOf) {
    // replicate the indexOf functioality so it will work for IE
    Array.prototype.indexOf = function(obj, start) {
    for (var i = (start || 0), j = this.length; i < j; i++) {
    if (this[i] === obj) { return i; }
    }
    return -1;
    }
    }

    http://stackoverflow.com/questions/1744310/how-to-fix-array-indexof-in-javascript-for-internet-explorer-browsers

    ReplyDelete
  5. I tried your solution, but the checkbox does not show as checkbox but as plain text.

    The problem is that my column is of type 'Plain Text' and I cannot select checkbox in Identification > Type

    ReplyDelete
    Replies
    1. In the checkbox column attributes change the setting Security > Escape special characters: Set it to No

      Delete
  6. Thank you !

    What should I set as Source for the P1_EMPNO_LIST hidden item.
    None of the options seem right.

    Regards

    ReplyDelete
    Replies
    1. Entirely up to you. You can set it to null/static then define the value using the first query in the article.

      Delete
    2. How can I make select limit to 1 ? Thanks

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hey there , first of all thanks for the tutorial it helped me alot!
    I tried to enhance my table with a 'check-all' checkbox in the column name of the checkbox column similar to this :

    http://jeffkemponoracle.com/2012/11/15/select-all-unselect-all-checkbox-in-interactive-report-header/

    but whenever i check the "check all" box it does set all check boxes on "checked" but it does not store any id's or values for each checked box.

    The javascript code im using for the Check-ALL box is

    if ( $( '#selectunselectall' ).is(':checked') ) {
    $('input[type=checkbox][name=f01]').attr('checked',true);
    }

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

    with #selectunselectall beeing the "check-all" box and the affected items is .empno .

    Also i'm using Apex 4.2.5


    ReplyDelete
  9. Thank you Martin for this great insight.
    Can you please guide me on displaying the checked rows on the next page, as you did in the previous post.
    Thank you once again.

    ReplyDelete
  10. Hi, to deal with the "select all" you can create an additional DA with properties like the checkAll DA and javascript Condition $( '#selectunselectall' ).is(':checked').
    Here you call a plsql action to set the list

    ReplyDelete
  11. Hi,
    Can you suggest how to update a column using IR report? My requirement is to store the updated value with reference to an unique id of the existing table in a different table. We are using Apex 5.0.

    ReplyDelete