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:
create table TASKS
(
  id              number not null,
  ind_complete    varchar2(1) not null,
  what            varchar2(250) not null,
  complete_before date
);

alter table TASKS
  add constraint TSK_PK
  primary key (id);
alter table TASKS
  add constraint IND_COMPLETE_CHK
  check (ind_complete in ('Y', 'N'));
To mark a task as "Done", create a procedure named MARK_TASK_COMPLETED:
create or replace
procedure mark_task_completed (p_tsk_id in tasks.id%type)
is
begin
   update tasks tsk
      set tsk.ind_complete = 'Y'
    where tsk.id = p_tsk_id
   ;
end mark_task_completed​;​
Seed the table with some sample data:
insert into tasks
   (id
   ,ind_complete
   ,what
   ,complete_before)
   select rownum
         ,'N'
         ,'testing' || to_char(rownum)
         ,sysdate + rownum
     from dual
   connect by level <= 10;
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.

42 comments:

  1. 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

    ReplyDelete
    Replies
    1. Hi 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.

      Alex

      Delete
    2. 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!!

      Delete
  2. Thank you very much for the quick response. Do you know of trick to get the .pngs printed when using the browser print()?

    ReplyDelete
  3. 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

    ReplyDelete
  4. this is not working for me. The images are not getting replaced. Am I missing something?

    ReplyDelete
    Replies
    1. possibly, it's hard to debug without being able to look at your code.

      Delete
  5. Hi,

    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?

    Cheers, Graham (4.2.2)

    ReplyDelete
    Replies
    1. Did you fill in the "Page Items to Submit" (image 11)? For this example I did exactly as described in this blog, and apex.oracle.com is currently on release 4.2.2, so it should work.

      Delete
  6. That's it - just upgraded and submitting page items is getting me every time!

    ReplyDelete
  7. Hi Alex,
    nice idea, I'm going to use that in my app as well.

    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.

    Cheers, JP

    ReplyDelete
  8. Hi,

    Can you help me how to save a Interactive report into another tab region which contains all saved reports.When clicked on the report name need to view the saved report.
    Is it possible?

    ReplyDelete
  9. After saving the Interactive report need to add that as a Favourite in another Tab.

    ReplyDelete
  10. Can i bind event handler manually?

    Dynamic actions are working, but this:

    $('#STATIC_ID').on("apexafterrefresh", function () {alert('Boom!')});

    - not. Is there a solution?

    Thanks!

    ReplyDelete
    Replies
    1. 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:

      $('#task-report').on("apexafterrefresh", function () {alert('Boom!')});

      When you click on one of the images in the report (thus triggering a refresh of the report) you will see the alert.

      Delete
    2. 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..

      Delete
    3. I don't understand what is "not working". Is it the refresh itself that is not up to par?

      Does your custom theme include the #REGION_STATIC_ID# for the report template that you're using? An "automatic" refresh can only happen when this attribute is in the report template.

      Delete
    4. 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.

      Delete
  11. Hi,

    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

    ReplyDelete
  12. Very nice! That’s exactly what I am looking for. Thank you!

    ReplyDelete
  13. Hello,

    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".

    I'm getting pretty desperate, so your help would be appreciated.....

    ReplyDelete
    Replies
    1. 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?

      Delete
    2. Hi, i have faced the same problem, the issue is that you're using the link column of the report not a certain column and editing it's link column attributes (as the example did)
      Thanks Alex !

      Delete
  14. 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?

    Thanks.

    ReplyDelete
    Replies
    1. For the scenario that you describe, you don't need a Dynamic Action. Create a Page Process (On Load - Before Header) and execute a PL/SQL procedure to populate items on your page.

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

    ReplyDelete
  16. 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 ?

    ReplyDelete
    Replies
    1. You refer to the item by its index number in APEX_APPLICATION, see https://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_item.htm#AEAPI194

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

      Delete
    3. 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.
      BEGIN
      stu_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.

      Delete
  17. 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.

    ReplyDelete
    Replies
    1. Simply don't implement the functionality, i.e. no pl/sql function.

      Delete
    2. 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.

      Delete
    3. 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.

      Delete
  18. 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!!

    ReplyDelete
    Replies
    1. 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

      Delete
    2. 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!

      Delete
    3. THANK YOU
      THANK YOU
      THANK YOU
      It's working

      Delete
  19. Hi Alex,
    I am trying to create csv file when click the IR using your method, but I am getting the below error.
    "AJAX call returned server error ORA-20876: Stop APEX Engine for Execute PL/SQL Code".

    I am using below mentioned PL/SQL block to create file.
    =======================================================
    declare
    TYPE cur_data IS REF CURSOR;
    lv_cur_data cur_data;
    lv_clob clob;
    lv_blob BLOB;
    lv_sql varchar2(32000);
    lv_cur_line varchar2(32000);
    L_DEST_OFFSET INTEGER := 1;
    L_SRC_OFFSET INTEGER := 1;
    L_LANG_CONTEXT INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    L_WARNING INTEGER;
    L_LENGTH INTEGER;


    begin


    lv_sql := ' select 1 as sno, ''alex'' as name from dual';

    lv_sql := lv_sql || ' union all ';

    lv_sql := lv_sql || ' select 2 as sno, ''pan'' as name from dual';

    -- first clear the header
    HTP.FLUSH;
    HTP.INIT;

    -- create response header
    OWA_UTIL.MIME_HEADER('text/csv', FALSE);

    HTP.P('Content-length: ' || L_LENGTH);
    HTP.P('Content-Disposition: attachment; filename="export_my_table.csv"');
    HTP.P('Set-Cookie: fileDownload=true; path=/');


    OPEN lv_cur_data for lv_sql ;

    LOOP
    fetch lv_cur_data into lv_cur_line;

    HTP.prn(lv_cur_line||' '||chr(13));
    --lv_clob := lv_clob || lv_cur_line;

    EXIT WHEN lv_cur_data%NOTFOUND;

    END LOOP;

    OWA_UTIL.HTTP_HEADER_CLOSE;


    htmldb_application.g_unrecoverable_error := true;

    -- stop APEX
    APEX_APPLICATION.STOP_APEX_ENGINE;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);

    RAISE;
    END;
    =======================================================
    Could you please let me know whether is it possible to run this export from IR.


    ReplyDelete
  20. Hi Alex,
    great post. Thanks.
    FRantisek

    ReplyDelete
  21. https://www.ashishsahay.com/2020/03/master-detail-report-in-apex.html

    ReplyDelete