For the moment, Dynamic Actions aren’t directly integrated with Interactive Reports or Interactive Grids.  That can be problematic when you want to display dynamic content that will:

  1. Hide/Show depending on the value
  2. Do something when selected

For this blog, I will walk you through the example of managing subscriptions.  The goal is to display an unsubscribe button if a person already has a subscription.  If they don’t have one, the user needs to see a subscribe button.


Table Structure:

personal_info
    personal_info_id       PK

publication_info
    publication_info_id    PK
    publication_category
    publication_name

subscription_info
    subscription_id        PK
    publication_info_id    FK
    personal_info_id       FK
    subscription_type

 

Add a Hidden Item to your Page

  • Name the hidden item: IR_PUB_VALUE

Add an Interactive Report to your page

  • Use the code below for the SQL query:

SELECT
    CASE
        WHEN SUBSCRIPTION_INFO.PUBLICATION_INFO_ID is null then
        '<a href="'||APEX_UTIL.PREPARE_URL('javascript:void(0)')
        ||'"data-id='||PUBLICATION_INFO.PUBLICATION_INFO_ID||'> 
        <span class="t-Button t-Button--simple t-Button--hot t-Button--stretch subscribe-action">
        Subscribe</span></a>' end as "Subscribe", 

    CASE
        WHEN SUBSCRIPTION_INFO.PUBLICATION_INFO_ID is not null then 
        '<a href="'||APEX_UTIL.PREPARE_URL('javascript:void(0)')
        ||'"data-id='||PUBLICATION_INFO.PUBLICATION_INFO_ID||'>
        <span class="t-Button t-Button--simple t-Button--hot t-Button--stretch unsubscribe-action">
        Unsubscribe</span></a>' end as "Unsubscribe",

    PUBLICATION_INFO.PUBLICATION_CATEGORY, 
    PUBLICATION_INFO.PUBLICATION_NAME 

FROM PUBLICATION_INFO
    LEFT JOIN SUBSCRIPTION_INFO
        ON PUBLICATION_INFO.PUBLICATION_INFO_ID = SUBSCRIPTION_INFO.PUBLICATION_INFO_ID
    LEFT JOIN PERSONAL_INFO
        ON PERSONAL_INFO.PERSONAL_INFO_ID = SUBSCRIPTION_INFO.PERSONAL_INFO_ID 

ORDER BY PUBLICATION_INFO.PUBLICATION_INFO_ID

 

Quick Code Breakdown


APEX_UTIL.PREPARE_URL('javascript:void(0)')
  • Once the link is clicked, execute this JavaScript
    • This JavaScript does nothing by itself, but we can use it to trigger a Dynamic Action

data-id='||PUBLICATION_INFO.PUBLICATION_INFO_ID||'
  • The links in the Interactive Report will have the row value of the primary key from the PUBLICATION_INFO table (PUBLICATION_INFO_ID)

<span class="t-Button t-Button--simple t-Button--hot t-Button--stretch
subscribe-action"> Subscribe</span>
  • This makes the link look like a button
  • Subscribe-action within the span tags is just an identifier that I assigned. We will reference it when we create a Dynamic Action

Apply Some Interactive Report Modifications in Page Designer

  • Under the Attributes section of the Interactive Report, select Exclude Link Column in the Link section
  • Select the Subscribe column in your Interactive Report and disable Escape special characters in the Security section
    • Do the same with the Unsubscribe column

Create a New Dynamic Action

  • Call it something like “Subscribe Dynamic Action”
    • I can’t believe my art teacher in elementary school questioned my creativeness
  • Choose a Click event
  • Make the Selection Type a jQuery Selector
  • For the jQuery Selector, make it:
    
    .subscribe-action
    
    • The period at the front of subscribe-action is not a typo
    • .subscribe-action can be found within the span tags of the SQL query for the Interactive Report. If you renamed it something else, remember to change it accordingly
  • Change the Event Scope to Dynamic

Create True Actions for the Dynamic Action

    • Create a Set Value True Action
    • For the Set Type, Choose JavaScript Expression. Add this code:

$(this.triggeringElement).parent().data('id')
  • Under the Affected Elements section, change the Selection Type to Item(s)
  • Choose the hidden page item you created earlier (IR_PUB_VALUE)

Give it a Test

  • Change the hidden item (IR_PUB_VALUE) to a regular text field to see if it is working properly
  • When you change the page item back to a hidden item, remember that you will run into errors if you plan on manipulating the value, unless you turn Value Protected off under the Settings section on the hidden page item

Expand the Code

  • Now that you can display dynamic links and grab the row value, you can add True Actions to the dynamic action to run PL/SQL in order to run insert/update/delete statements or whatever you are trying to do

 

Learn More

References

Requirements

  • APEX 5x or higher

Disclaimer:
We do not take responsibility for any unintended or unwanted consequences in your instance of Oracle, Oracle APEX, or related products as a result of reading our blogs or following our guides.  Though the information is fully tested and generally safe to use, our lawyers really have a thing against admitting potential wrongdoing.  If it makes you feel any better, one time they asked me to put the trash cans outside for pickup.  I know that the city dictates that they have to be a least 4 feet apart, but I put them 3.5 feet apart.  I’ve been checking the mail every day and can’t wait for the fine to arrive.

Join the discussion One Comment

  • I know that alll this Time I was making this in the bad way, I used a JavaScript function to set a Hidden Item with the id Value and then call the Dynamic action to process.
    And for dynamic links, Y printed the complete URL in the Case.
    Thanks for this.