Apex static links, dynamic links, links in reports, without forgetting globalization

In the last days i've been working a lot with links embedded in reports.
I had some old applications where, for some reason, i had stuffed HTML fragments in views, probably because that was the fastest way to get the job done.

But unfortunately that is also the fastest way to make a view unreadable!

So i took my time and i went back to those applications in pursue of a different, cleaner and more manageable, way of doing the same things.

The result is that now i have a better understanding of modularization as far as Oracle Application Express is concerned and i can list the pros and cons of each method.

Let's first describe the different scenarios in ascending order of complexity along with their best matching solution (in my humble opinion, of course):

1. The simplest case is a list of static links. It doesn't matter if they are apex page links or external URLs, in Apex there is a component called List (what a coincidence!) and this is the best fitting solution. The list entry editor allows you to create conditional entries and this makes the list somewhat dynamic, however this page component is based on a list of static data, i.e. we cannot populate the list using a query. Last but not least, the text portion of the links can be easily translated into different language using Apex built-in globalization features.

2. A similar scenario is represented by a list of somewhat static links, built dynamically though, basing on a SQL query. A row, among other columns, displays a link to a page or an external URL, either as a text link or an icon.
Apex comes with declarative attributes for such column types and they work very well as far as the relationship one column-one link is obeyed.
A typical case is to provide a user with an edit link represented by an icon and the Apex Builder itself is full of such examples. Note that there is the possibility of defining a condition for the presence of the column, but that will affect the whole column, not the individual links. Also in this case Apex takes care of the globalization aspects and will include the translatable portions of the links in the exported XLIFF file.

3. A slightly more complex situation is when you want to make this link optional on a record per record basis. In order to add this functionality, we need to build the link using the individual components (the link text, the link URL and optionally the attributes like TITLE, ALT and so on). For instance, suppose that we want to display the link to page 1000 only if value of column FLAG is Y:

SELECT
id,
decode(flag,'Y', 1000, null)
as page_id,
decode(flag,'Y','click here to do something',null)
as action,
decode(flag,'Y','CLICK_ON_LINK',null)
as req,
null as link,
...
(other columns follow probably)
...
FROM some_table;

As you see the DECODE functions comes in handy in these situation.

Then, we go to the report attribute pages and we uncheck the Show option for the three columns called page_id, action and req.
Last, we click on the edit icon of column link and we put together all the pieces as pictured below.


In order to make this report translatable however, we need to change it slightly, replacing the translatable string constant with a proper function call (in bold text) and let Apex do the rest for us:

SELECT
id,
decode(flag,'Y',1000, null)
as page_id,
decode(flag,'Y',apex_lang.message('LINK_TO_P1000_MSG'),null)
as action,
decode(flag,'Y','REQUEST',null)
as req,
null
as link,
...
(other columns follow probably)
...
FROM some_table;

Then we just need to go to the translatable messages repository and add as many translations as we need.

With these basic concepts in mind, we can build up even more complex reports, with multiple links or alternative links per row, basing on non-trivial decision trees.

On this subject, one more remarkable thing in the picture above is the way of passing the parameters. Let's look at a further complication of this scenario:

we have a triple choice of links depending on a tri-state flag (Y,N and null)
If flag = Y then we want to display link1, if N then display link2, otherwise don't display the link at all.
Not only, let's suppose that link1 points to page 1000 and it requires the setting of an item called P1000_ID, whereas link2 is for page 2000 and it requires a different item called P2000_ID, and we also want translations for the different links:

SELECT
id,
decode(flag,
'Y',1000,
'N',2000,
null)
as page_id,
decode(flag,
'Y',apex_lang.message('LINK_TO_P1000_MSG'),
'N',apex_lang.message('LINK_TO_P2000_MSG'),
null)
as action,
decode(flag,
'Y','REQUEST1',
'N','REQUEST2',
null)
as req,
null as link,
...
(other columns follow probably)
...
FROM some_table;

If P1000_ID contains the same type of information as P2000_ID, then
we can use a common item located in the "calling" page, look at P2_APPLICATION_ID in the picture above, assuming that we are displaying the report in page 2.

It's also perfectly legitimate the usage of application level items for this kind of operations.

Then, in the target page(s), page 1000 in the picture below, we can retrieve the value in the following fashion:


Note that there is no need for special before header computations or before header processes.

This method works well if we can get to these pages only via the report links, because the item gets the value from a known source. Alternatively we could pass all the values for the different items, regardless of the target page:


In the example above i supplied the same ACTION text to the TITLE attribute of the column LINK, but i can add another column in the query in case i need to display a different tooltip message for the link.

In order to display icons instead of text links, we can face increasing complexity depending on the target browser being used to display the page.

For instance, with Firefox 2.0, we can just add the icon path to the SQL queries above.

SELECT
id,
decode(flag,
'Y',1000,
'N',2000,
null)
as page_id,
decode(flag,
'Y',apex_lang.message('LINK_TO_P1000_MSG'),
'N',apex_lang.message('LINK_TO_P2000_MSG'),
null)
as action,
decode(flag,
'Y','REQUEST1',
'N','REQUEST2',
null)
as req,
decode(flag,
'Y','path to icon1',
'N','path to icon2',
null)
as icon_path,
null as link,
...
(other columns follow probably)
...
FROM some_table;

The usage of icons doesn't prevent us from using text links and as a matter of fact, if we supply a value for the ALT attribute, the link will display as text in case the icon is missing. So we can even exploit this feature of Firefox to our advantage, building a link definition that will handle icons as well as text links.

<img src="#ICON_PATH#" alt="#ACTION#" />

In order to display a text only link, it will be sufficient to return NULL as ICON_PATH column and if the presence of an icon based link or a text based link depends on some other column 's value, then you may have to stuff yet another DECODE function to get the job done:

SELECT
id,
decode(flag,
'Y',1000,
'N',2000,
null)
as page_id,
decode(flag,
'Y',apex_lang.message('LINK_TO_P1000_MSG'),
'N',apex_lang.message('LINK_TO_P2000_MSG'),
null)
as action,
decode(flag,
'Y','REQUEST1',
'N','REQUEST2',
null)
as req,
decode(flag,
 'Y',decode(is_link1_icon,'Y','path to icon1', null),
 'N',decode(is_link2_icon,'Y','path to icon2', null),
     null)
as icon_path,
null as link,
...
(other columns follow probably)
...
FROM some_table;

As i said earlier however, this works well with Firefox but fails miserably with Internet Explorer 7.0 that will display an awful icon placeholder along with the ALT text.

So, in order to get the icon based links to work under all circumstances, while still avoiding to hardcode HTML tags in SQL queries, we can use Oracle built-in package HTF.
In the following query i am assuming that we are going to display only icon links or no links, just to avoid unnecessary complications that you are free to reintroduce for your own pleasure ;-)

SELECT
id,
decode(flag,
'Y',1000,
'N',2000,
null)
as page_id,
decode(flag,
'Y',apex_lang.message('LINK_TO_P1000_MSG'),
'N',apex_lang.message('LINK_TO_P2000_MSG'),
null)
as action,
decode(flag,
'Y','REQUEST1',
'N','REQUEST2',
null)
as req,
decode(flag,
'Y',
htf.img('path to icon1', null, apex_lang.message('LINK_TO_P1000_MSG')),
'N', 
htf.img('path to icon2', null, apex_lang.message('LINK_TO_P2000_MSG')),
null)
as link_desc,
null as link
...
(other columns follow probably)
...
FROM some_table;

Note that i removed ALT="#ACTION#" from Link Text because this attribute is now part of the IMG tag generated by function HTF.IMG (see the third parameter containing the call to apex_lang.message).

Finally, be sure to set report's attibute property Strip HTML to No, otherwise the icon won't display.



And this ends the links in reports dissertation.

See more articles about Oracle Application Express or download tools and utilities.