Tuesday, July 10, 2007

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.

12 comments:

Anonymous said...

try to
http://htmldb.oracle.com/pls/otn/f?p=31517:23:2534200784087926::NO

Byte64 said...

i see, but i don't understand what i am supposed to do...

Term Papers said...

I have been visiting various blogs for my Term Papers Writing research. I have found your blog to be quite useful. Keep updating your blog with valuable information... Regards

Yannick Staels said...

This post has learned me alot of new things.

One problem though I have with the htf.img. The icon isn't displayed in Google Chrome or Internet Explorer. The path of the icon is correct, but for some reason APEX doesn't add it to the "a" tag.

Running on APEX 4.1.

Thanks!

Yannick Staels said...

This post has learned me much about the possibilities in APEX.

One problem though I have with the use of htf.img. APEX doesn't seem to add the path to the icon to "a" tag. I only see the "a" tag instead of an "a" tag with an "img" tag. The function returns the correct "img" tag, but APEX doesn't show the icon in the report.

I'm using APEX 4.1. Any ideas?

Thanks!

Byte64 said...

Hi Yannick,
would you mind to put the SQL fragment where you call HTF.ANCHOR and HTF.IMG?

It's hard to say without looking at the code...

Regards,
Flavio

Yannick Staels said...

Hi Flavio

I've created a simple report with following statement

"select 1 edit_number, 'description' description, htf.img('/i/menu/pencil2_16x16.gif', null, 'test') icon_path
from dual"

The idea is to display the icon specified in icon_path for the edit_number column by putting #ICON_PATH# in the link text.

When I run this, it shows me no icon for the edit_number column and the correct html tag for the icon in the icon_path column.

Since you mentioned the htf.anchor function, I've been testing and when I use something like

"select htf.anchor('f?p=&APP_ID.:9:&SESSION.::&DEBUG.:9::', htf.img('/i/menu/pencil2_16x16.gif', null, 'test'), 'test') deedinr, 'description'
from dual"

it displays the icon correctly in the edit_number column when I set this column to standard report column.

Is this expected behaviour or is their an easier way?

Thanks for the help.

Regards

Yannick

Byte64 said...

It's expected behaviour that you set "standard report column" when you want to display your own HTML, however if the link you are going to build is not depending on some condition that makes it appear on certain rows and disappear on others, then you can define a link with an icon very easily by clicking on "add a column link" in the Tasks region inside the report definition page.

Regards,
Flavio

Yannick Staels said...

Got it!

Since my link will be showed for certain rows based on a condition, I can't use the simple 'add a column link'.

Thanks for the info!

Regards

Laurent said...

HELLO,

I unearth a little this post.
I work with APEX 4.2.
I try to make a link icon that appears (or not) based on a value of a column.
And the method explained in this post is very interesting. I tried what is explained here.

So, I have a report based on this query:


Select norm_id, aut_id
, aut_label
, decode(check_privileges.CAN_AUT_UPDATED(:APP_USER, ca.aut_id),1, htf.img('/i/ed-item.gif',null,'test'),null) link_desc
, '' but_edit
from e_concerned_authorities ca
where ca.norm_id = 2676

the function check_privileges.CAN_AUT_UPDATED returns 1 when the user can change the record and therefore see the edit link icon.
in the properties of my edit link : section Column link-> Link text, I put #link_desc#
When I execute my page, instead of the icon, I have the text '#link_desc#'. the string substitution is not replaced by its value in the code of my page ?

Have you an idea of the problem?

Thank you.

Laurent

Byte64 said...

Hi Laurent,
yes, I do have some idea, use UPPERCASE letters in the #LINK_DESC# substitutions and see if that fixes your problem.

Regards
Flavio

Laurent said...

Thank you Flavio.
I feel a little bit silly.

Finally, I add "style = "display: #LINK_DISP#" in the link attribute property.
WIth LINK_DISP equal "none" or "inline" to display the element.
and it works well.

Again thank you for this method and for your help.

Laurent

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles