Monday, 14 December 2015

Tutorial: Include action button in report

In reality, this 'add' button could represent any action you would like in a report that would execute PL/SQL upon press of a row level button.

In this example I click on a button in a report to add the row to collection, without submitting the page.

Prepare page

Add Static id to your report region:

Add hidden page item: P2_ADD
Set protected = No if you need to submit the page for other processes.

Create before header process to initialise collection

Add link column

Define a new column in your report that will serve as the link button, it only returns null. For classic reports you could create a virtual column for the purposes of a link.

My report on emp uses this SQL
select e.*, null add_btn from emp e

Modify the new column and set the column type to 'Link'.

Set URL to: javascript:void(0);
Link text: Add
Link Attributes: data-id="#EMPNO#" class="add t-Button t-Button--warning t-Button--simple t-Button--stretch"

The data tag creates an attribute that we can interrogate easily with jQuery, returning the ID of the record.
The classes represent the same classes that would be applied when choosing relevant template options. The 'add' class is added for our dynamic action.

Create dynamic action

Create a new dynamic action that will respond to button press, using on click of a jQuery selector.

Use the following selector to identify add button clicks on your report.
#p2_my_report .add

Add an action to execute the following JavaScript. It sets the page item with the value of the ID set in the data tag.
$s('P2_ADD', $(this.triggeringElement).data('id'));

Don't forget to set this to not Fire on Page Load. this.triggeringElement represents the button pressed, which is generated as the following HTML.
<a href="javascript:void(0);" 
   class="add t-Button t-Button--warning t-Button--simple t-Button--stretch">Add</a>

Other JavaScript options

If other information was required, you could define more data tags, or traverse the DOM to find other values in the row. For IR you would need to first define static ID for the column as SAL. Classic reports automatically use the column alias.

If you were defining a remove function, then a second statement could be added to immediately hide the row from view without needing to refresh the report by locating the surrounding tr tag.
Though this may make pagination feel a little strange, as the number of rows displayed won't add up.

Partial Page Refresh

May 2017 - I've added this section in response to a reader comment. I can't believe I neglected this property in the first place.

You'll find a problem if the report is refreshed due to a range of actions such re-sorting, applying filter, or perhaps invoked as a refresh action in yet another dynamic action - the on click dynamic action on our action button no longer works!

This is easily adjusted using the Event Scope property for the on click dynamic action, using the 'Dynamic' option (formerly 'Live').

Set Dynamic Action Event Scope to Dynamic

This maps to a jQuery setting that, as the item help describes:
Binds the event handler to the triggering element(s) for the lifetime of the current page, irrespective of any triggering elements being recreated via Partial Page Refresh (PPR).
The default option is static perhaps as the lowest common denominator, favouring speed. Most reports would have this adjusted to Dynamic.

The second property allows you to define the surrounding container of what's being refreshed, ie, the region. It's my understanding that you would include the following value.

And this would reduce the search area required to find our particular buttons, but I thought that was the purpose of supplying this as a surrounding ID/repeating class combination.

Execute the PL/SQL

You could then define a second action that executes PL/SQL, including P2_ADD in 'Page Items to Submit', so you can then refer to :P2_ADD as a bind variable in the PL/SQL. Note, you should always explicitly convert any value from session state that is not a string.

Alternatively, you could define an onChange event on P2_ADD which does the same thing. This would allow different UI on the page to invoke the same action.

The onChange dynamic action should only execute when the item is not null, and an action after the PL/SQL should clear the item. This allows the same value to be selected successively, otherwise the value wouldn't 'change' the second time around.
In the screenshot & example below I also refresh the region containing the collection.


So that describes a pattern I use frequently, and some variation of which is asked on the forums all the time. I plan to extend this example to include the collection report as a modal dialog with the ability to add & remove.

Run the demo to see it in action.

If you want to explore this further, you might like my book on jQuery with APEX. </shameless-plug>


Anonymous said...

Hi Scott,
The cover of the book that I recived looks quite different.
Hm... - I think you should (have to) exchange your's at several web locations :-)

Scott Wesley said...

I've updated the blog to use the final image, but I notice many of the online sites still use the first drafted icon. That's out of my control ;p

ruepprich said...

Nice post Scott. Just what I needed.

Scott Wesley said...

No worries. A favourite pattern of mine.

Anonymous said...

Thanks for your tutorial
but there is a problem in it :
when you search in your first report for items then the add button in results doesn't work.
also I tried this with a report that that has paging , when you go to the next page again the add button doesn't work.
do you have any solution for this?

Scott Wesley said...

Hello anonymous visitor,

You've identified something I can't believe I neglected to include. The post will be updated accordingly.

You need to set 'Event Scope' on the dynamic action to 'Dynamic'.