Sunday, March 18, 2018

Delete a Row of a Report with a Dynamic Action

I was asked recently how I implemented the deleting of a row in my demo application, so I thought I would post the answer here in case anyone else was curious.

To accomplish the deletion of a row with a prompt to the user for confirmation you will need a hidden page item and a dynamic action.

On the page that has your report, add a hidden page item - mine is called P20_DELETE_ID. This item will be used by our dynamic action to hold the primary key of the row on which the user clicked the delete icon.

You need to add a link column to your report for the delete icon with the following attributes:

Link Target: URL
URL: javascript:void(null);
Link Text: <span class="t-Icon fa fa-trash delete-note" aria-hidden="true"></span>
Link Attributes: data-id=#REMOVE#
Link Column

Notice in the Link Text that I added a class called delete-note. This is the jQuery selector we will use to trigger our dynamic action. Also, notice the Link Attributes - the column referenced here, in my case #REMOVE#, should hold the primary key for the row. This value will be used to identify the row that needs to be deleted in your delete PL/SQL statement.

Next, we need a dynamic action that fires on click of our jQuery selector, .delete-note. This action will have four true actions:
Delete Row Dynamic Action

True Action #1: Confirm
Text: Are you sure?
Confirm

True Action #2: Set Value
Settings:
Set Type: JavaScript Expression
JavaScript Expression: $(this.triggeringElement).parent().data('id')
Affected elements:
Selection Type: Item(s)
Items(s): P20_DELETE_ID <---- your hidden page item
Set Value

True Action #3: Execute PL/SQL Code
PL/SQL Code: delete from jsd_notes where id = :P20_DELETE_ID;
Items to Submit: P20_DELETE_ID <---- your hidden page item
Execute PL/SQL Code

True Action #4: Refresh
Selection Type: Region
Region: Notifications <---- your report region
Refresh

And that should do it :)


- Jackie -

8 comments:

  1. Nice work, thanks for posting a VERY clear example.

    ReplyDelete
  2. Hi Jackie, Thanks for sharing this.

    The only issue with this is that any user can easily modifiy the "id" in the html (using developer tools) and delete rows that they should not....

    So, I would add more control in the PL/SQL section to check if the current user can delete the selected record and maybe also use the "ROWID" instead of the "ID", it will be hard for the user to find an existing ROWID...

    ReplyDelete
  3. Good point, but no doubt that would be part of the server validation performed when the DELETE statement is migrated to a PL/SQL package ;p
    This would improve performance, increase security, encourage re-use.

    ReplyDelete
  4. Hi,

    It is nice article. I tried like above steps, Its working for the first time, but its not working for second time.

    please check below link

    https://apex.oracle.com/pls/apex/f?p=26664:2:103876840860910:::::

    Please help ,

    Thank you in advance.

    ReplyDelete
    Replies
    1. Hi Santhosh -

      In the Delete Row dynamic action, the Event Scope should be set to 'Dynamic' instead of 'Static'

      Hope that helps,

      Jackie

      Delete
  5. Yes Jackie, it's working fine now....Thank you very much ....

    ReplyDelete
  6. This is really neat!
    I have been using the Ajax approach from Nick Buytaert:
    https://apexplained.wordpress.com/2012/02/12/instant-row-deletion-from-report/

    which works perfectly fine, even on 5.1.4 but your idea is pretty clever too, and yours because it does a region refresh, the count is always correct!

    Good job!
    Next time I'll use your approach
    Gaspar

    ReplyDelete