How to delete selected checkbox rows in an Oracle APEX Classic Report using Ajax

Question

Within Oracle APEX v4.2.2, I have a simple classic report that has as a first column, a checkbox f50 setup attached to the table's ID column, which will allow a user to check all or specific rows and remove these records from the report/table.

An example report might be something like:

ID     Col2    Col3    Col4
----------------------------
1      10      20      30
2      5       8       9
3      92      88      12
4      1       2       44
5      95      77      88

The requirement I am after is that I want to perform this whole process of checking the IDs and the removal of these records done without having to submit the whole page but would like it done via an AJAX method using apex.process.server if possible.

UPDATE: Just a bit more background on this requirement based on the report I am attempting to hook this apex.process.server checkbox IDs, i.e.:

ID Report Column above within Report Attributes heading looks like this:

<input type="checkbox"  label="Select Code" onclick="$f_CheckFirstColumn(this)" />

Drilling down into this ID column under HTML Expression is the following:

<input type="checkbox" #ID# value="#ID#" name="f50" id="f50_#ROWNUM#"/>

Region Source:

SELECT A.ID,
       A.REQ_NO COL2,
       A.CODE_ID||apex_item.hidden(20, A.CODE_ID)||apex_item.hidden(30, A.ID) COL3,
       GROUP_VALUE COL4
FROM MY_TABLE A
WHERE A.REQ_NO = :REQ_NO

I believe inorder to have APEX store the values within the apex_application.g_f50.countarray of the IDs to be removed, the page needs to be submitted.

Using apex.process.server, can the ids, as they are checked, be passed as a JavaScript array to an on demand process that will then use these ids to perform the required delete operation?

How can I achieve the above via an AJAX means (no page refresh at all)?

Tom Tom · Accepted Answer · 2014-06-13T05:50:06

Given this query for a report

select 
 "EMPNO",
 "ENAME",
 apex_item.checkbox2(2, 0) check1,
 apex_item.checkbox2(3, 0) check2,
 apex_item.checkbox2(4, 0) check3
from EMP

With EMPNO set to "Hidden" - so it'll generate a hidden input item appended to the last column.

To update a certain record you'll need a PK and a value to update the row with. That is why I'm using EMPNO. I'll pass that to the on-demand process.

function selectorToArray(pSelector){
  function getVal(pNd){
    switch ( pNd.nodeName ) {
      case "INPUT":
        switch ( pNd.type ) {
          case "checkbox":
            return $(pNd).prop("checked");
            break;
          default: 
            return $(pNd).val();
        };
        break;
      default:
        return $(pNd).val()
    };
  };

  var lArray = [];
  $(pSelector).each(function(){
    lArray.push(getVal(this));
  });
  return lArray;  
};

The function selectorToArray will fetch the values for the given selector to an array and get the value. As you might know, you can pass values to a process with x01, x02, ... But there are also arrays: f01, f02,... With the following code you can send values over to the ondemand process:

function sendCheckboxes(){
  var lf01 = [], lf02 = [], lf03 = [], lf04 = [];
  lf01 = selectorToArray("input[name=f01]");
  lf02 = selectorToArray("input[name=f02]");
  lf03 = selectorToArray("input[name=f03]");
  lf04 = selectorToArray("input[name=f04]");

  apex.server.process("PROCESS_CHECKBOXES", {f01: lf01, f02: lf02, f03: lf03, f04: lf04});
};

You can use those just like you would otherwise: loop over them:

DECLARE
  l_pk VARCHAR2(30);
  l_check1 VARCHAR2(30);
  l_check2 VARCHAR2(30);
  l_check3 VARCHAR2(30);
BEGIN
  -- f01: PK 
  -- f02: checkbox values column1
  FOR i IN 1..apex_application.g_f01.count
  LOOP
    l_pk := apex_application.g_f01(i);
    l_check1 := apex_application.g_f02(i);
    l_check2 := apex_application.g_f03(i);
    l_check3 := apex_application.g_f04(i);
    apex_debug.message('Record with PK '||l_pk||': check1? '||NVL(l_check1, 'NO')||': check2? '||NVL(l_check2, 'NO')||': check3? '||NVL(l_check3, 'NO'));
  END LOOP;
END;

In your code, there are 3 item arrays: f20, f30 and f50. f30 holds the row PK value, while f50 is used for the checkbox.
Don't be fooled by the array naming. Apex itself uses the f## arrays for submission, true enough. And your items with name f50 will indeed be in array g_f50 on page submit. You can however also use arrays f01 to f20 (don't think it goes up to 50) for ajax calls! They're a great addition to the variables x01-x20! When using the arrays to send a bulk of values to your process, instead of one-by-one, I think it's most useful to not just send an array of PK values, with a position-matched array of values to interact with. This isn't as valuable when you use a report without pagination though, but still. With pagination, the idea is that you don't really know what set of data was just interacted with. Of 100 records, 10 rows were presented. Of those 10 rows, 6 were checked on render, and on submit only 5 are. Which ones are checked and which ones are unchecked. Knowing which 5 are checked doesn't mean you know the unchecked ones.
When you include a PK column however, you'll always have those 10 rows and you're able to identify clearly which records has been checked or unchecked.
For instance, 10 records in your report will (=should!) mean that 10 values are put in an array (eg l_f01) with the PK value and 10 more values are put in another array (eg l_f02) with eg a checked indicator. So when passing those on to the on-demand process, you'll be able to loop over array f01 reliably, and fetch the checked or unchecked indicator from array f02 with your current index variable used for f01.

Plainly put, you're building up 2 arrays with this sort of value set:

f01 - IDs | f02 - checkeds
----------|---------------
4520      | false
4521      | true
4527      | false
4561      | true
4578      | true

How to delete selected checkbox rows in an Oracle APEX Classic Report using Ajax

1 Answers