Report checkbox menu aka Google Gmail

This post is the first in a series of articles on how to implement examples from the demo for Pretius APEX Context menu plug-in. If you are a Gmail user then you are familiar with selecting e-mails by their state:

Gmail menu to select e-mails by their type

In the demo application, I’ve implemented the menu that allows you to select rows containing the chosen job.

Pretius APEX Context menu attached to report checkbox

To achieve such a result you will need to implement

  • Classic Report based on emp and dept tables,
  • APEX list representing the first level of the menu,
  • context menu using Pretius APEX Context Menu plug-in
  • dynamic actions handling proper actions taken from menu entries
  • dynamic actions handling interaction with the header and body report checkboxes
  • APEX items storing additional data used by the above dynamic actions (names used in instruction refer to page 1100 which is used in the resulting demo)

Preliminary assumptions

I assume:

  • I won’t show you how to implement checkboxes as font-apex icons – I can do it in a separate post,
  • you will be reproducing instructions at apex.oracle.com,
  • you have access to emp and dept tables,
  • you have installed Pretius APEX Context menu in your application
  • checkboxes state will be held in APEX collection using a simple method (but not efficient on large volume reports)
  • you have created a blank page on which you will implement following instructions

Processes

For this example, you will need collection which will be responsible for maintaining checkboxes state. Because of that, you need to initialize collection when a user enters the page of the first time.

  1. Create a new process Create collection in Before Header point
  2. Change Executions Options / Run Process to Once Per Session or When Reset
  3. Set Success Message / Success Message to Collection Created
  4. Set Source / PL/SQL Code to
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('EMP_CHECKBOXES_CHECKED');

Employee report

Create a Report

  1. Create a new region in Content Body with name Employee report
    1. Change Identification / Type to Classic Report
    2. Change Source / Type to SQL Query
    3. Paste SQL Query
select
  empno,
  ename,
  job,
  hiredate,
  sal,
  comm,
  case 
    when n001 is not null then 'checked'
    else ''
  end checked
from
  emp
left join
  apex_collections
on
  empno = n001
  and collection_name = 'EMP_CHECKBOXES_CHECKED'

Change report attributes

  1. Set attributes of the report
    1. Set Layout / Number of Rows to 5 (so you will have pagination on emp table)
    2. Set Template Options to stretch the report (General / Strech Report)
    3. Set Pagination / Type to Row Ranges X to Y of Z (with pagination)

Customize columns

  1. Go to column CHECKED
  2. Set Identification / Type to Hidden Column

Add checkbox column

  1. Right-click on Content Body / Employee report / Columns
  2. From the menu select Create Virtual Column
  3. Move the column to the first position in columns list
  4. Set Identification / Type to Plain Text
  5. Set Heading / Heading to
<input type="checkbox" class="reportCheckbox"><a href="javascript: void(0)" class="menu"><span class="fa fa-caret-down"></span></a>
  1. Set Heading / Alignment and Layout / Column Alignment to Left
  2. Set Column Formatting / HTML Expression to
<input type="checkbox" name="f01" value="#EMPNO#" class="reportCheckbox" data-job="#JOB#" #CHECKED#>

Create supporting APEX items

To fulfill requirements regarding holding checkboxes state in the collection you need to create 3 APEX items with Identification / Name set to as follows

  • P1100_EMPNO
  • P1100_ALL_ROWS_SELECTED
  • P1100_JOB

All the above items have to be hidden and have Session state protection set to No (they will be changed dynamically by APEX dynamic actions):

  • Set Identification / Type to Hidden
  • Set Settings / Value Protected to No

To create a submenu with available jobs you need to know what jobs are available and for this, you will use new APEX item with JSON generated based on emp table.

  1. Create APEX Item P1100_JOBS
  2. Set Identification / Type to Hidden
  3. Set Source / Type to PL/SQL Function Body
  4. Set Source / PL/SQL Function Body to
declare
  v_cursor sys_refcursor;
  v_result clob;
begin
  apex_json.initialize_clob_output;

  open v_cursor for
    select
      distinct
        job
    from
      emp
    order by
      job asc;

  APEX_JSON.WRITE( v_cursor );

  v_result := apex_json.get_clob_output;
  
  apex_json.free_output;
  
  return v_result;
end;
Result of the PL/SQL code should look like
[  
  {  
    "JOB":"ANALYST"
  },
  {  
    "JOB":"CLERK"
  },
  {  
    "JOB":"MANAGER"
  },
  {  
    "JOB":"PRESIDENT"
  },
  {  
    "JOB":"SALESMAN"
  }
]

You might ask the question why not to use listagg function in a simple query. The reason is that it is my standard that I work with JSON which I can easily parse. When you parse a simple string with separator you have to make sure the separator is not used as part of a value. Also, I won’t have to deal with 4000 characters limitation for listagg function.

APEX List

The plugin creates a context menu based on created APEX list and for my instructions you need to define a simple APEX list with 4 entries.

  1. Go to Shared Components \ Lists
  2. Click Create button
  3. Select From Scratch and click Next button
  4. Set Name to REPORT_CHECKBOX_MENU and click Next button
  5. Create 4 entries All, None, Separator, Job with Target Page ID or custom URL blank
  1. Click Next button and then click Create List button
  2. Go to newly created APEX List

Right now you have a simple list which can be added to our report but it won’t perform any actions – it needs configuration on entry-level which will be handled by dynamic actions.

List entry “All”

Entry All will be selecting all checkboxes based on data in the emp table. It will be done by dynamic action listening to the custom event and you need to set its Target / URL Target to

javascript: apex.event.trigger(document, 'allCheckboxChecked', true);

This code will trigger dynamic action on custom event allCheckboxChecked listening to document HTML node. Additionally, parameter true will be passed and it will be available via this.data

List entry “None”

Entry None will be doing same as All entry but it will be passing parameter false. Set Target / URL Target to

javascript: apex.event.trigger(document, 'allCheckboxChecked', false);

List entry “Separator”

This entry will be displayed as separator and to make it work you have to set User Defined Attributes / 7 to

separator

It will notice the plugin that the entry Separator should be displayed as entries separator.

List entry “Job”

Job entry will be extended with a submenu representing existing available jobs. To make it extendable it has to has a unique ID (in the scope of list entries) which will be used in the plugin implementation. Set User Defined Attributes / 7 to

JOB

And change Target / URL Target to

javascript: void(0);

The plugin implementation

Ok, you have the report with example data, JSON describing available jobs and APEX list defined. It’s time to implement the plugin. Right after this paragraph, you will need to create dynamic actions that will handle menu actions but let us focus on the plugin!

  1. Create dynamic action
  2. Set Identification / Name to Checkbox menu
  3. Set When / Event to Click
  4. Set When / Selection Type to jQuery Selector
  5. Set When jQuery Selector to
th .menu
  1. Set Advanced / Event Scope to Dynamic
  2. Set Advanced / Static Container (jQuery Selector) to body
  3. Select default Show true action
  4. Set Identification / Action to Pretius APEX Context Menu [Plug-In]
  5. Set Settings / List name to REPORT_CHECKBOX_MENU
  6. In Settings check Override Behaviour
  7. Set Settings / Override Behaviour to
var 
  //array that will contain 2nd level entries
  jobs = [],
  //parse JSON describing all available jobs in emp table
  jobArray = JSON.parse(apex.item('P1100_JOBS').getValue()); 

for ( var i in jobArray ) {
  jobs.push({
    "type"    : "action",
    "labelKey": jobArray[i].JOB,
    "icon"    : null,
    "href"    : undefined,
    //jQuery proxy function changing context and extending passed parameters
    "action"  : $.proxy( function( pJob, pMenuOptions, pTriggeringElement ){
      //trigger custom event selecting only rows with given job
      apex.event.trigger(document, "selectByJob", pJob);
    }, window, jobArray[i].JOB )
  });
}

//return created JSON
return {
  "JOB": {
    "items": jobs
  }
};
  1. Set Affected Elements / Selection Type to – Select –

Now save and run the page. You should see the report like on the image below.

Right now all you can do is just perform click on the down-arrow icon next to the header checkbox. Clicking on the icon invokes the menu but clicking any menu entry won’t work. It is because entries are triggering custom events which are not (yet) bound with APEX dynamic actions.

Dynamic actions handling Context Menu action

Dynamic Action “Context menu: All/None”

This dynamic action describes what happens when a user clicks on All and None action from the context menu. Because those actions are similar to changing the state of the header checkbox they

  • set property checked (of header checkbox) based on passed value (true/false) to dynamic action (this.data)
  • trigger change event on the header checkbox
  • in result, dynamic action After TH checkbox checked / unchecked is triggered

Follow those steps to implement this dynamic action:

  1. Create a dynamic action Context menu: All / None
  2. Set When / Event to Custom
  3. Set When / Custom event to allCheckboxChecked
  4. Set When / Selection Type to JavaScript Expression
  5. Set When / JavaScript Expression to document
  6. Change default true action Show to Execute JavaScript Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to Region
    3. Set Affected Elements / Region to Employee report
    4. Set Settings / Code to
$(this.affectedElements).find('th .reportCheckbox').prop('checked', this.data).trigger('change');
Dynamic Action “Context menu: job”

Every entry of Job submenu executes this dynamic action by triggering custom event and passing emp.JOB as dynamic action parameter (this.data).

  1. Create dynamic action Select rows by the chosen job
  2. Set When / event to Custom
  3. Set When / Custom Event to selectByJob
  4. Set When / Selection Type to JavaScript Expression
  5. Set When / JavaScript Expression to document
  6. Change default Show true action to Execute JavaScript Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to Region
    3. Set Affected Elements / Region to Employee report
    4. Set Settings / Code to
var report = $(this.affectedElements);

//deselect all visible checkboxes
report.find('.reportCheckbox').prop('checked', false)
//and select only with data-job equal to job
report.find(':checkbox[data-job="'+this.data+'"]').prop('checked', true);

//set item that will be sent via Execute PL/SQL Code
apex.item('P1100_JOB').setValue( this.data );
  1. Create new true action Disable
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')
  1. Create new true action Execute PL/SQL Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Settings / Items to Submit to P1100_JOB
    3. Set Settings / PL/SQL Code to
APEX_COLLECTION.TRUNCATE_COLLECTION('EMP_CHECKBOXES_CHECKED');

--select only rows with given job
for emp in (select * from emp where job = :P1100_JOB) LOOP
  APEX_COLLECTION.ADD_MEMBER(
    p_collection_name => 'EMP_CHECKBOXES_CHECKED',
    p_n001 => emp.empno
  );
END LOOP;
  1. Create new true action Enable
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')

Dynamic actions handling checkboxes state

The plugin demo application does not support remembering checkboxes state. In this detailed implementation guide, I’ve decided to show you one of many ways to remember what checkboxes are checked across all report pages. To simplify implementation I’ve decided to use only native dynamic actions so I won’t bother you with custom AJAX calls.

Following instructions describes 4 dynamic actions to handle checkboxes state in the report header and body.

Dynamic Action “After TD checkbox checked / unchecked”

This dynamic action describes what happens when a user changes state of a checkbox in body report. Its purpose is to

  • disable checkboxes to prevent multiple click events
  • set value of the item that will be used to modify collection state
  • modify collection state using native APEX ajax
  • enable all checkboxes after APEX ajax call ends
  • trigger custom event handling header checkbox state

Implementation is as follows:

  1. Create dynamic action After TD checkbox checked / unchecked
  2. Set When / event to Change
  3. Set When / Selection Type to jQuery Selector
  4. set When / jQuery Selector to [name=f01]
  5. Set Advanced / Event Scope to Dynamic
  6. Set Advanced / Static Container (jQuery Selector) to body
  7. Change default Show true action to Disable
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')
  1. Create new true action Set Value
    1. Set Affected Elements / Selection Type to Item(s)
    2. Set Affected Elements / Item(s) to P1100_EMPNO
    3. Set Execution Options / Fire on Initialization to No
    4. Set Settings / Set Type to JavaScript Expression
    5. Set Settings / JavaScript Expression to
this.triggeringElement.value
  1. Create new true action Execute PL/SQL Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Settings / Items to Submit to P1100_EMPNO
    3. Set Settings / PL/SQL Code to
declare
  v_seq_id apex_collections.seq_id%type;
  v_collection_name apex_collections.collection_name%type := 'EMP_CHECKBOXES_CHECKED';
begin
  --fetch seq_id if given empno via P1100_EMPNO item exists in collection
  begin
    select
      seq_id 
    into
      v_seq_id  
    from
      apex_collections
    where
      collection_name = v_collection_name
      and n001 = :P1100_EMPNO;
  exception
    when no_data_found then
      null;
  end;

  if v_seq_id is null then
    --if empno was now found then add it to collection
    APEX_COLLECTION.ADD_MEMBER(
      p_collection_name => v_collection_name,
      p_n001 => :P1100_EMPNO
    );
  else 
    --if empno was found then remove it from collection
    APEX_COLLECTION.DELETE_MEMBER (
      p_collection_name => v_collection_name,
      p_seq => v_seq_id
    );
  end if;
end;
  1. Create new true action Enable
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')
  1. Create new true action Execute JavaScript Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Settings / Code to
//trigger custom event that handles th checkbox state
apex.event.trigger(document, 'getCheckboxState');
Dynamic Action “After TH checkbox checked / unchecked”

This dynamic action describes what happens when a user changes a state of the checkbox (in the header of the report). When the checkbox is checked you need to fill the collection with all empno values from emp table. When the checkbox is not checked all you have to do is just empty the collection. Because of that, dynamic action is divided into true and false actions – depending on the state of the header checkbox.

It can be implemented using true actions only, but to make it more readable I’ve described approach with division into true and false actions. To make it work with only true actions you would need to add new APEX item storing state of the header checkbox. The item would be sent via Execute PL/SQL Code action to recognize what actions is performed. Depending on value collection would be filled or truncated.

Dynamic action implementation

  1. Create dynamic action After TH checkbox checked / unchecked
  2. Set When / Event to Change
  3. Set When / Selection Type to jQuery Selector
  4. Set When / jQuery Selector to
th:first-child :checkbox
  1. Set Client-side Condition / Type to JavaScript Expression
  2. Set Client-size Condition / JavaScript Expression to
this.triggeringElement.checked
  1. Set Advanced / Event Scope to Dynamic
  2. Set Advanced / Static Container (JQuery Selector) to
body

True actions

True actions will be executed only when the header checkbox is checked after change event.

  1. Change default Show true action to Execute JavaScript Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to Region
    3. Set Affected Elements / Region to Employee report
    4. Set Settings / Code to
$(this.affectedElements).find('.reportCheckbox').prop('checked', true);
  1. Create new true action Disable
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')
  1. Create new true action Execute PL/SQL Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Settings / PL/SQL Code to
APEX_COLLECTION.TRUNCATE_COLLECTION('EMP_CHECKBOXES_CHECKED');

--select all checkboxes by adding all empno from emp table to collection
for emp in (select * from emp) LOOP
  APEX_COLLECTION.ADD_MEMBER(
    p_collection_name => 'EMP_CHECKBOXES_CHECKED',
    p_n001 => emp.empno
  );
END LOOP;
  1. Create new true action Enable
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')

False actions

False actions will be executed only when the header checkbox is not checked after change event.

  1. Create new False action Execute JavaScript Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to Region
    3. Set Affected Elements / Region to Employee report
    4. Set Settings / Code to
//all checboxes are not checked
$(this.affectedElements).find('.reportCheckbox').prop('checked', false);
  1. Create new false action Disable
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')
  1. Create new false action Execute PL/SQL Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Settings / PL/SQL Code to
APEX_COLLECTION.TRUNCATE_COLLECTION('EMP_CHECKBOXES_CHECKED');
  1. Create new false action Enable
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')
Dynamic Action “Fetch TH checkbox state”

This dynamic action is used to determine whether the header checkbox is checked or not. It is executed by dynamic actions:

  • After TD checkbox checked / unchecked
  • Force fetching checkbox state

It does

  • disable all visible checkboxes,
  • return number of selected rows to item P1100_ALL_ROWS_SELECTED compared to the total row count in emp table. If no rows are selected then 0 is returned,
  • change the state of header checkbox based on returned value (P1100_ALL_ROWS_SELECTED),
  • enable all visible checkboxes.

Dynamic action implementation

  1. Create dynamic action Fetch TH checkbox state
  2. Set When / event to Custom
  3. Set When / Custom Event to getCheckboxState
  4. Set When / Selection Type to JavaScript Expression
  5. Set When / JavaScript Expression to document
  6. Change default Show true action to Disable
    1. Set Execution Options / Fire on Initialization to Yes
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')
  1. Create new true action Execute PL/SQL Code
    1. Set Execution Options / Fire on Initialization to Yes
    2. Set Settings / Items to Return to P1100_ALL_ROWS_SELECTED
    3. Set Settings / Suppress Change Event to Yes
    4. Set Settings / PL/SQL Code to
declare
  v_coll_cnt number;
  v_report_cnt number;
begin
  --get collection members count
  v_coll_cnt := APEX_COLLECTION.COLLECTION_MEMBER_COUNT('EMP_CHECKBOXES_CHECKED');

  --count all rows in emp table
  select count(1) into v_report_cnt from emp;

  if v_coll_cnt = v_report_cnt then
    --all rows are selected, hold number of rows in item
    :P1100_ALL_ROWS_SELECTED := v_report_cnt;
  else
    --not all rows are selected
    :P1100_ALL_ROWS_SELECTED := 0;
  end if;
end;
  1. Create new true action Execute JavaScript Code
    1. Set Affected Elements / Selection Type to Item(s)
    2. Set Affected Elements / Item(s) to P1100_ALL_ROWS_SELECTED
    3. Set Settings / Code to
$('th .reportCheckbox').prop('checked', this.affectedElements[0].value > 0);
  1. Create new true action Enable
    1. Set Execution Options / Fire on Initialization to Yes
    2. Set Affected Elements / Selection Type to JavaScript Expression
    3. Set Affected Elements / JavaScript Expression to
$('.reportCheckbox')
Dynamic Action “Force refreshing TH checkbox state”

This dynamic action is used to force the execution of dynamic action Fetch TH checkbox state after the report is refreshed. In results, it supports handling the header checkbox state after paginating the report.

  1. Create dynamic action Force refreshing TH checkbox state
  2. Set When / event to After Refresh
  3. Set When / Selection Type to Region
  4. Set When / Region to Employee report
  5. Change default Show true action to Execute JavaScript Code
    1. Set Execution Options / Fire on Initialization to No
    2. Set Affected Elements / Selection Type to Region
    3. Set Affected Elements / Region to Employee report
    4. Set Settings / Code to
apex.event.trigger(document, 'getCheckboxState')

The result

Save and run the page. You should have a simple report, with checkboxes in the first column, checkbox in the header and down-arrow icon invoking the context menu. It should look like the GIF image below:

Final result

The result of this implementation guide can be found at apex.oracle.com as demo application associated with this blog.

Ostrowski Bartosz

Oracle APEX Developer @PretiusSoftware

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *