Tuesday 2 June 2009

Function Returning Links

In Apex you can create links out of your columns in a report. What you currently can't do is to put a condition behind this link and generate it upon that condition - if there is a link then it is there for all the rows. The only workarround for this is to create the link in your SQL. In that case your SQL may look quite clumsy and hard to read. Something like this:

SELECT CASE
WHEN deptno = 10
THEN '<a href="javascript:popUp2('''
|| 'f?p=&APP_ID.:500:&SESSION.::&DEBUG.::'
|| 'P500_DEPTNO:'
|| deptno
|| ''', 700, 700);">'
|| '<img src="#IMAGE_PREFIX#gobut.gif">'
|| '</a>'
WHEN deptno = 20
THEN '<a href="javascript:popUp2('''
|| 'f?p=&APP_ID.:501:&SESSION.::&DEBUG.::'
|| 'P501_DEPTNO:'
|| deptno
|| ''', 700, 700);">'
|| '<img src="#IMAGE_PREFIX#go_search.gif">'
|| '</a>'
ELSE '<a href="javascript:popUp2('''
|| 'f?p=&APP_ID.:502:&SESSION.::&DEBUG.::'
|| 'P502_DEPTNO:'
|| deptno
|| ''', 700, 700);">'
|| '<img src='
|| '"#WORKSPACE_IMAGES#go_button.gif">'
|| '</a>'
END LINK,
ename, job, hiredate
FROM emp

The solution for this problem you can find here. Back in 2006 I created a function for generating links. This function can do the following for you:

- create a link based on a target page you provide, withouth having to code a full apex link,
- replace the link with an image if desired,
- sort the links based on any columns in your source,
- open the target page in the same window or in a popup window,
- add a request to your link,
- reset pagination if required,
- add a checksum to your link.

At the end, your query looks much better if you use that function:

SELECT CASE
WHEN deptno = 10
THEN return_link_fn (p_page => 500,
p_item => 'P500_DEPTNO',
p_value => deptno,
p_image_n => 'gobut.gif'
)
WHEN deptno = 20
THEN return_link_fn (p_page => 501,
p_item => 'P501_DEPTNO',
p_value => deptno,
p_image_n => 'go_search.gif'
)
ELSE return_link_fn (p_page => 502,
p_item => 'P502_DEPTNO',
p_value => deptno,
p_image_n => 'go_button.gif'
)
END LINK,
ename, job, hiredate
FROM emp


2 comments:

Anonymous said...

Hi Denes,
Thanks a lot. I also use function that creates a link, but your function is much better.
Hidden item for sorting is really great solution.
Just one question - why do you need exception when Others?
Without it if something happens you will get error message that could help,
with it you will get just 'Invalid parameter'.

Thanks,
Lev

Rahul said...

I am a novice sir, but where can I find the code to use this return_link_fn function?