21 December 2012

APEX: Dynamic Action in Interactive Report

Yesterday I was showing a colleague how to use a Dynamic Action in an Interactive Report to do an update on the underlying table. To get this to work prior to APEX 4 you would need to write some javascript on the page as well as an Application Process. Mostly I used the method described by Roel Hartman a number of years ago.
Want to take a look at a demo first before the steps to take to make it work? It's right here.For this example, I will use a simple table named TASKS:

Now that the database side is done, we can turn our attention to APEX. To show images in the report, upload some in the Shared Components section.

The screenshot above has been made in APEX 4.2, so if the image looks "new and unfamiliar", now you know why.

Upload the images, and associate them with the Application that you are working with. The images I used are in the links below and I named them 'ok' for the checkmark and 'nok' for the white cross on a red background. There are some really nice looking icons on the site of IconArchive.
Next create an Interactive Report, using the wizard. For the Query use the following:

select id tsk_id
,case ind_complete
when 'Y' then 'ok'
when 'N' then 'nok'
end ind_complete
,what
,complete_before
from tasks

In the above statement I used a case statement to translate the indicator to 'ok' or 'nok', matching the names of the images that I uploaded in the previous step.
When you run the report, it will look like:

Next is to change the Ind Complete column into an image and make it clickable. Make the following changes to the column attributes of the Ind Complete column:

The target page will be page 0 (zero), but it could be any page - we are not going to use this for navigation, just to make the image clickable.
The link text used in the image above consists of two parts; the first is the location of the images, #APP_IMAGES# and the second part is the name of the image that we want to display #IND_COMPLETE#. The latter is a neat trick (at least I think so) instead of using a "hardcoded" name for the image, the current value is used (see the query used for the Interactive Report, column IND_COMPLETE).
For the Link Attributes, we set the ID to the current TSK_ID by using the same syntax #TSK_ID# and add a class to which the Dynamic Action will respond - setCompleted.
When you inspect the report at this time (in the browser using something like FireBug), you will see that the image source is replaced just like we wanted:

Before we can add the Dynamic Action, we need a page item where the current (the task that needs to be set completed) TSK_ID is placed for processing. Just add a hidden item to the page (in my case it's called P8_TSK_ID).
Now the final part to make it all come together; add the Dynamic Action
Right click on "Dynamic Actions", choose "Create" from the context menu.

Enter "Mark Tasks as Completed" for the Name:

Make the Dynamic Action respond to the Click event where the Class name is setCompleted (make sure there is a period before the classname, this is the jQuery class selector syntax)

There are several steps to take in this Dynamic Action, the first is to store the current TSK_ID in the hidden item we created earlier. The value that we want to place there can be retrieved with a JavaScript Expression: this.triggeringElement.id;.
"Fire on Page Load" can be unchecked.

Specify the item for the Set Value action, P8_TSK_ID.

The first part of the Dynamic Action is done, but there are some other TRUE actions to complete the whole thing.
Add another TRUE action to the Dynamic Action and have it execute PL/SQL:

To see the changes made to the underlying data, the report needs to be refreshed. Add another TRUE action to do just this: Refresh the Report:

The last step in the TRUE action is to prevent the navigation to Page 0 taking place. This can be accomplished by the Cancel Event action:

Now the report will respond to clicking the image shown.

And that's it.
Please note that on the Demo page, the procedure used to set the IND_COMPLETE is slightly different from the one described here. The procedure used will toggle the value, so you can mark the tasks completed as well as not completed. This is the source code for the procedure:

create or replace procedure mark_task_completed (p_tsk_id in tasks.id%type)
is
begin
update tasks tsk
set tsk.ind_complete = case ind_complete when 'Y' then 'N' when 'N' then 'Y' end
where tsk.id = p_tsk_id
;
end mark_task_completed;​

UPDATE: Adjust the Event Scope

As Hawk (from the comment below) pointed out: I forgot to mention the setting of the Event Scope. Change the setting for Event Scope to Dynamic and all should work.

40 comments:

Hello: Thank you very much for this, but I have an issue. It works the first time the DA fires, but gives error and appears to try to branch to page 0 the second time DA fires. I put it on cloud in case you can look at it: http://apex.oracle.com/pls/apex/f?p=67201:1 I think it might be "fire on page load" settings of the TRUE actions, but could not get it to work. Any advice? Thanks, Hawk

Thanks for your comments.To fix this you need to set the "Event Scope" to "Dynamic". Most likely it is "Static" now.Navigate to the Dynamic Action in the Page Rendering section, and set the Event Scope accordingly.

Hello! Thank you for great post!! But I have a still issue...When "Event Scope" is "Static", it looks dinamic action is running. After page refresh, it looks good.But when "Event Scope" is "Dynamic", it looks dinamic action is NOT called and just called "Column link"...I'd really appriciated it if you gave me an advice..Thanks!!

Hello: Please disregard my .png printing question. My issue was caused by a print stylesheet I created. I modified it and it all works fine now. I cannot seem to put my comment as a reply, so here it is under another comment. Thanks again, Hawk

This is a really nice way to move javascript code to dynamic actions. However, I could not get the item value set in the dynamic action to be visible to the PL/SQL. I managed to resolve this with a standard htmldb_Get call using a javascript action. This passes 'this.triggeringElement.id);' via 'get.add(pItem,pValue)' to an empty application level procedure 'BEGIN NULL; END;'.

It looks as if the value used in PL/SQL is the submitted (database) value of the item (set by get.add) and the 'Set Value' action is simply setting it at the page level. Am I missing something?

One improvement: In the dynamic action I use 'Submit page', rather than 'Refresh'.Reason: When I 'Refresh' the Interactive Report, the pagination is reset as well.In your demo, try setting 'Rows per page' to 5, then go to rows 6-10.Click a OK/NOK button. It works, but the IR is back to rows 1-5.

it works.. I tested your code (with only changing the STATIC_ID) to the section "Execute when Page Loads" at page level.You can try as well: Go to the Demo-page (at the top of the blogpost), use the console (F12) and enter:

hmm.. maybe my problem is that we using custom theme, i don't know what else can it be..

I workarounded it, by looking into widget.interactiveReport..js. After triggering that event apex executes function _Finished_Loading (wich hides loading circle).I redefined this function with itself + my handler triggering :) not very beautiful, but i don't see any other solution..

Of course, #REGION_STATIC_ID# is in the template. It just div-based, not table.

Sorry, i forget to say, my problem is in my own application, not in the one in article. I just have APEX 4.2, IR on page and need to execute my js code after refresh."Not working" means if i create Dynamic Action on page wich runs javascript - it works perfectly. If i'm binding my own handler for "apexafterrefresh" event - it doesn't fire and my code is not executed.

I am following your example to fit for a classic report. For the PLSQL true action, how do i get the value 'Y' or 'N' to the PLSQL block. I am placing my PLSQL code directly in APEX. Thank you for your time

This looks a really neat method for toggling images, cheers. As such, I've followed your instruction and it seems to work for me, to a fashion!!!! The only changes I have made is to record the row id (e.g. tsk_id) to a collection (via the PL/SQL true action). However, rather than the value being added (e.g. 1,2,3.....100) to column c001, the actual name of the original id column (e.g. #tsk_id#) is being repeatedly added. What am I missing? Oh, and I'm using APEX v 4.1, therefore I am unable to set the event scope to "Dynamic", I only have the options "bind", "live" or "once".

So, you're using a collection as the basis for your report and you're using the APEX_ITEM package to display the content of the collection? Then you should be able to get the actual values, instead of the name of the column.. Can you setup an example on apex.oracle.com?

Hello,I have created a Dynamic Action, with the property "When page is loaded".I am trying to obtain values from a PL/SQL procedure or function, but, the values are charged just when I refresh the page (Using F5 or linking to another page).

Could you help me?

What is the best solution, to charge the data from a database when I want to set a textbox with these values at the moment of loading the main page?

How can I pass to a page item a date field created with APEX_ITEM.DATE_POPUP(1,rownum, spd_cns_ck_transcript_date,'mm/dd/yyyy',10) ? I need this second client side value to pass to page item DA and execute table update with procedure that accepts two parameters: record ID which I have and this date ?

What I did was: I created 2 page items to store the values of record_id (coming from IR query - unique identifier), and transcript_date which needs to be updated to my underlying table.In the report I created a link to page 1 (same as the IR) from a hidden report column from where I pass to the page items below the values of the record id and date as follows:P1_SPD_REC_ID - #SPD_REC_ID#P1_TRANSCRIPT_DATE - #APEX_DATE_01_01 (the item id of the apex_item.I created a DA with Dynamic scope which calls the following pl/sql to update the underlying table.BEGINstu_reinst_petition.update_transcript_date(p_rec_id=>:P1_SPD_REC_ID, p_trans_date=>:P1_TRANSCRIPT_DATE);END;But the value of the apex item is not obtained and the update does nothing. My question all along is how to get the value of the apex item from the client side. It has to be some javascript function that needs to get the record id from the IR and the value entered by user in the apex_item.date_popup2 field.Any tip would help.

Followed your directions and the interactive report is working. How can I set the 'OK'/check marked rows to disabled. Whether the row displays as check marked initially, or the user clicks the white cross thus switching it to 'OK', I don't want the user to be able to edit an 'OK'/check marked row, ever.

I need the function to update fields in an unapproved row. The function updates the approved_date field and update_user field. That is working correctly: the record is being updated and the icon turns into the checkmark. I have left the system supplied edit column (pencil icon) as the first column to take them to a page where they can actually change fields in the record.

I want to somehow disable the pencil link and the checkmark link of the 'Approved Records'. I only want links on the white cross 'NOK' records. If the user clicks the pencil it takes them to the next page. If the user clicks the white cross the record is set to 'Approved' and is then disabled and they can no longer do anything to it.

Sorry, I realize this is not really the intent of your subject here, but it's the closest thing I've found to what I need to do. This is my first application, and I'm struggling.

No worries.. Why don't you change the item on the page where you can change the fields to "Display Only"? There are probably other fields that you need to be able to modify on that page. If you don't want the user to only change the approval state of the records, remove the pencil and the page that it points to.

Since column ‘Ind Complete’ executes dynamic action, ‘Mark Tasks as Completed’, as triggered by the ‘Click’ event, how can I additionally execute a completely unrelated procedure when a field in a different column is clicked? Can I tie a dynamic action to a click in a specific column? Thank You!!

it only triggers a dynamic action, because the Ind Complete column has "setComplete" class defined on it.Clicking on any other column does not trigger the DA.If you want to trigger a different DA for a different column, use a different class to trigger the DA

Sorry, I'll have to go read about classes; I don't know what they are, but I guess that explains why the action on my 2nd column didn't work, since I used "setComplete" on it as well as the approval column. Thank You!

About Me

Self-employed under the name allAPEX, mainly in The Netherlands. Presented at National and International Conferences. Oracle ACE Director for Database Development. Trainer for SQL and PL/SQL. Married, two children, likes to Barbecue.