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:
- Hide/Show depending on the value
- 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
- You can learn more helpful tips like the ones above by visiting the Traust Consulting Blog.
References
- https://jackiemcilroy.blogspot.com/2018/03/delete-row-of-report-with-dynamic-action.html
- http://www.grassroots-oracle.com/2015/12/tutorial-include-action-button-in-report.html
- https://stackoverflow.com/questions/28299582/oracle-apex-interactive-report-conditional-column-link-display#28300786
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.
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.