Formatted Text with Dynamic Actions

This is a quick note on how to use Dynamic Actions in Oracle APEX 4.0 to display unstructured and formatted text from the database based on user selection. This was a bit more cumbersome in the previous versions of APEX.

Image by Jesper Rønn-Jensen under Creative Commons License.

If you have ever let the users get at the Rich Text Editor items (HTML Editor in the previous versions), then you probably have som text with HTML-markup stored in the database. Below is a description on how to get and display the text based on browser events. The example below will display a description of a department when the user clicks department name in a SQL Report (quite stupid really, but serves the purpose).

Demo Application
I have updated my demo application with the example, see it in action here:http://apex.oracle.com/pls/apex/f?p=45420:5. Download the application here: http://apex.oracle.com/pls/apex/f?p=45410:DOWNLOAD.

Prepare the Report
Create a region of type SQL Report based on table DEPT:

select deptno, dname from dept

Modify the DNAME-column in the report:

• Set Link Text to #DNAME#
• Set Link Attributes to id="departmentName#DEPTNO#" (this will uniquely identify each anchor element, and give the ability of extracting DEPTNO from the department name)
• Set Target to URL
• Set URL to # (this really does nothing)

Add an item to the report region, this will eventually hold the DEPTNO of the chosen department from the report.

• Right click the department report region and choose Create Page Item
• Select Hidden
• Give the item a name (P5_DEPTNO)
• Leave the rest default (you might want to reconsider Source Used, if you set it to "Always, replacing any existing value in session state", and leaves Source Value blank, you ensure the item starts with no value when the page is displayed).

Create Region to Display Text
For the sake of simplicity, the text will be displayed in it's own HTML Region

Right click Regions and choose Create

• Choose HTML
• Choose HTML again
• Give it a descriptive title: Department Description
• Create a div to display the text, set Region Source to:
• <div id="departmentDescription"></div>

Create the Dynamic Action
The first we need to do, is to create a Dynamic Action to get the DEPTNO from the chosen department name, and stuff it into P5_DEPTNO.

Right click Dynamic Actions and choose Create
Choose Advanced
Give it a name (onclick - Department Name)
Set the values as seen below, this means the Dynamic Action will fire when anchors with id's starting with departmentName is clicked

Set Action to Set Value, uncheck Fire on Page Load (we only want it to fire when clicked), set the Set Type to Javascript, and Javascript Expression to

this.triggeringElement.id.replace('departmentName','');

This will extract the DEPTNO from the id of the element clicked (by replacing departmentName with nothing).

Set Selection Type to Item(s), and transfer P5_DEPTNO to the right hand side

The final result should look something like this:

The next step is to get and display the desired text from the database. In order to do this we must create a new True Action to the same Dynamic Action.

• Right click the Dynamic Action and choose Edit
• Click Add True Action
• Set Action: SetValue
• Uncheck Fire on Page Load
• Set Set Type: PL/SQL Function Body
• Set PL/SQL Function Body to code below:

declare
   l_ret varchar2(32000);
begin
   for r in (select dep.*
               from dept dep
              where dep.deptno = :p5_deptno)
   loop
      l_ret := 'HTML formatted return value for <b>'|| r.dname ||'</b> located in <i>'|| upper(r.loc) ||'</i>.';
   end loop;
   return l_ret;
end;

• Set Page Items to Submit: P5_DEPTNO
• Set Escape Special Characters: No
• Set Selection Type: jQuery Selector
• Set jQuery Selector: div[id=departmentDescription]

Finally edit the Dynamic Action, and set Event Scope: live. This is to ensure the onclick event is attached to Department Name even after PPR refresh of the report.

A Word of Caution
Even though APEX takes care of escaping/encoding your HTML according to JSON specification, this will bloat your return message. There seems to be the standard PL/SQL 32k limit to the JSON-message (Large texts resulting in ORA-06502).

Take care what you return in your message, switching off escape special characters leaves it pretty much wide open to anything. HTML in JSON is a debated issue.

In short: Keep it small and neat :-)

More on Modal Pop Ups

I previously wrote a post on how to use the new APEX 4.0 native features to conjure modal inline dialogs. It was followed by a brief (very brief on my part, to say the least) discussion on how to achieve the same functionality for a create button. In this post I will elaborate a bit on how to do exactly that.

The solution sketched out below definitely has potential for improvement, but can serve as a diving board for the interested.

Demo Application
If you are curious, or just down right bored with long posts, then I have a running copy of the demo application here: http://apex.oracle.com/pls/apex/f?p=45420:3. The demo application can be downloaded here: http://apex.oracle.com/pls/apex/f?p=45410:DOWNLOAD. To keep the examples as clean as possible, I have created a new page with the Create-button, so you have both alternatives available.

The Quick Solution
And what is wrong with a quick solution? Personally I feel the we programmers (me definitely included) are very good at complicating things. The rest of this post is dedicated to a more generic approach, but in my original comment I suggested this:

• Create an anchor-tag (link) anywhere on the page
• Set href attribute to the URL of your edit form page, let primary key item values remain blank (but be sure to pass them)
• Set the id attribute to callModalDialog

In my sample application that would be: <a id="callModalDialog" href="f?p=&APP_ID.:4:&SESSION.::NO::P4_EMPNO:">Create Employee</a>.

This would work just like the edit links in my original example, but ready to insert a new employee. Like magic :-)

The Elaborate Solution
The elaborate solution uses more Dynamic Actions, a bit more javascripting and a couple of dirty tricks to accomplish the same as the quick solution. The goal is to make a normal create button to have the same behavior with inline modal dialog, as the edit links does.

The explanation below is based on the original modal dialog page in my sample application. To follow the example and repeat the steps, you need to start off with a copy of page 2 in my sample application.

To follow the example, start by creating a region button with the following values (mostly just accept defaults):
Button name: CREATE

The button should take you to the create/edit page, but without the fancy modal dialog.

A Note for Later
If you name your button to something else than Create (or Text Label/ALT-property for the page button in APEX), you must adjust the following JQuery selectors accordingly for it to work. You may also expect some issues when using an image button (the javascript onclick function extraction may fail).

Removing the Original onclick-Event
APEX uses a javascript function to redirect the browser (also when using anchor button template). There are two things that needs to be done when the page loads; store the original create link, and remove the original button onclick event. I use JQuery and javascript native regexp capabilities to achieve this.

To create a Dynamic Action which fires when the page has finished loading, do the following:
Create a new Dynamic Action at the page level

Choose Advanced

Give it a sensible name ("On Page Load")

Choose Event Page Load

Choose Action Execute Javascript Code, and paste in the javascript code below:

/* get original onclick event */
var origAction = $('button[value=Create]').attr('onclick').toString();
/* get link from original onclick event using regular expression */
var link = origAction.match(/(redirect\((\'|\"))([^\'\)|\"\)]*)/)[3];
/* Remove original onclick event */
$('button[value=Create]').removeAttr('onclick');
/* store link as title attribute of button */
$('button[value=Create]').attr('title', link);

Looks a bit Greek? Even if I have actually included comments? If you are not familiar with JQuery, it definitely will. If you are not familiar with regular expressions, even more so. If you are not familiar with javascript at all, you are allowed to test the code, but not use it in production unless you have truly understood what it means :-) There are very good sources on the web for all the knowledge required.

And why, oh, why store the link value in an element attribute definitely not meant to hold a link value!?! It will overwrite any existing title-values, and makes both setting and retrieving code hard to read. On the other hand, it will keep the value with it's element, and support more than one create button on any given page. There are more than one alternate way of doing this, but hey, feel free to bring suggestions :-)

Anyway, click Create and you are done.

If you run your page now, clicking the Create button will not do anything (the onclick event was removed, but not replaced).

That was the hard part!

Calling the Dialog
The edit dialog Dynamic Action is already in place, all that has to be done is to adapt the existing Dynamic Action to include the new create button.
Edit the Modal Dialog Dynamic Action
Include: button[value=Create] as the JQuery Selector expression (total expression will now be: a[id^=callModalDialog],button[value=Create])

Edit the True Action Javascript Expression to be:

/* prevent default behaviour on click */
var e = this.browserEvent;
e.preventDefault();
/* Find page link */
var link;
if (this.triggeringElement.tagName=='A') {
   link = this.triggeringElement.href;
} else if (this.triggeringElement.tagName=='BUTTON') {
   link = this.triggeringElement.title;
}
/* Trigger JQuery UI dialog */
var horizontalPadding = 30;
var verticalPadding = 30;
$('<iframe id="modalDialog" src="' + link + '" />').dialog({
   title: "Edit Employee",
   autoOpen: true,
   width: 700,
   height: 300,
   modal: true,
   close: function(event, ui) {apex.event.trigger('#P3_AFTER_MODAL','select',''); $(this).remove();},
   overlay: {
       opacity: 0.5,
       background: "black"}
}).width(700 - horizontalPadding).height(300 - verticalPadding);
return false;            

The difference from the original javascript code is the extra bit extracting the link to use, which differs from a normal anchor element, and our button with the special id attribute.
Click Apply and you are done.

The Rest?
Is the same as detailed in my last post. I have updated the dialog page in the sample application to include Create and Delete buttons, but that is it.

Quick or Slow?
Quick sound promising! Simplicity rules! There are some draw-backs, of course. Like where to put the link to find it later (a Display Only item properly named perhaps?)? Like the need to style a link as a button? Like the need to place the link in a region template position? It all adds up.

The generic (more elaborate) approach will work with any create button (well, not with the anchor template). Drop the Dynamic Actions onto the page, and it will work. On the other hand, the generic approach involves more code, more code is harder to maintain and more prone to breaking. Generic code requires a delicate hand (ie takes time), and is generally harder to read than code created for a specific task. This is all in the eye of the beholder, but on more than one occasion I have had the pleasure of revisiting my own old code and though: I didn't need to do that... I digress, I know. Besides, that is too great a topic to just be delegated to a digression :-)

So, should you use the quick link (pun intended :-))? My answer (being a consultant) is a definitive: It depends!

Enjoy :-)