APEX Reports: One Link, Multiple Destinations
Every Interactive Report has an optional set of “Link” attributes that allow you to specify the location where the user should be redirected if they click a link next to a record in the report. You can choose “Link to Custom Target” and use the Link Builder to easily specify the target application, page, item values to pass, and other attributes.
What if the report combines different entities, and you need to direct the user to a different page depending on the type of entity? Or, if you need to direct the user to a different page with different attributes depending on the status of the record?
One method is to generate the URL in the report query using apex_page.get_url (APEX 5+) or apex_util.prepare_url (APEX 4 or earlier), or (God forbid) you could generate the url yourself using string concatenation.
A more declarative solution is to instead use APEX page redirects. This solution involves the following:
- Add some hidden items to the page to store the parameters for each target page;
- Add a Branch to the page for each target page; and
- Add a Request to the link to signal the page that a redirect has been requested.
NOTE
This method does not work as described if you want to navigate to a modal page.
Here’s an example. My page 550 has an interactive report which combines TI records with TRQ records (both of which have a very similar structure). If the user clicks on a TI record they should be redirected to p510 with the corresponding TI_ID, and if they click on a TRQ record they should be redirected to p305 with the corresponding TRQ_ID.
Here’s the link attributes for this report:
Notice that the page now redirects back to itself with the request set to “GOTO_TARGET”, along with the IDs required. My report query has been constructed so that every record will only have a TI_ID or a TRQ_ID, never both at the same time; so the link will ensure that only one of the P550_GOTO_xxx_ID values will be set.
The page then just needs two Branches: one for each target. Conditions on each branch ensures they only get activated if the request has been set, and the branch is selected based on which “GOTO ID” item has been set:
For a normal navigation to this report (e.g. from another page), the request should be blank (or some other value) so none of the Branches should be triggered.
For a relatively simple scenario like this, I like the declarative approach. Each branch can take advantage of the full range of APEX features such as the link builder, security conditions, build options, etc.
Note: this method works just as well for Classic report links as well.
The thing to be mindful of is that the order of the branches, and the condition on each branch, must be carefully selected so that the correct branch is activated in each situation. I’ve shown a simple example which works because I have ensured that only one of the ID parameters is set at the same time. If a record has both IDs, the condition on the first branch “GOTO_TARGET (TI_ID)” will evaluate to True and it will be used, regardless of what GOTO_TRQ_ID was set to.
If there were numerous potential destination pages, with a large number of parameters to pass, I might choose the apex_page.get_url method instead. This is also what I’d usually do if the target was a modal page; alternatively, I’d use a “redirector” modal page as described in the comments below.
Related
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Kevin Payne
26 September 2017 - 3:49 pm
Really useful, very clear and well written
Lilian Sun
14 November 2017 - 12:27 pm
Hi Jeff,
It’s really a smart way to do so. Thank you very much for sharing. It solved my problem when I’m struggling on Javascript and apex_page.get_url issue. Just a little pity that it can not apply to Modal Window.
shital
21 September 2018 - 11:46 pm
what is the way for opening modal window?
Jeffrey Kemp
26 September 2018 - 12:32 pm
Hi shital,
You cannot open a modal window from a page rendering process within a non-modal window.
To use my technique in this case, I would create a “redirector” modal window within which I would implement the conditional redirect. In other words, page 1 (normal window) navigates to page 2 (empty modal window) which has redirects to either page 3 or page 4 (both of which are also modal windows).
I hope this helps.
Jeff
Tom Perry
26 March 2019 - 4:22 am
Hi Jeff
This seems so straight forward, but I seem to be misunderstanding a simple step and keep getting an error Unable to find item ID for item P548213_GOTO_ID in my application
When I create the link in my first IR I assume that the page and the name should be pointing to my target page. Am I misunderstanding this?
Thank you for your thoughts
Jeffrey Kemp
26 March 2019 - 3:02 pm
Hi Tom, the link should specify the name of the “GOTO_ID” item – if that’s on page 548213 then I’d guess the item name will be P548213_GOTO_ID; does that page and the item exist in your application?
Tom Perry
27 March 2019 - 4:45 am
Hi Jeff
Thank you for the quick reply
Yes it is in the application. Is it possible my problem is that my calling report is a 1 to 1 table and I’m trying to pass an ID to a page with many records with the same id?
Jeffrey Kemp
27 March 2019 - 8:13 am
Hi Tom, I don’t know – would you be able to reproduce the problem on apex.oracle.com? If so I would be happy to take a look at it.
Collin McGhee
19 October 2021 - 10:36 pm
Hi Jeff,
Love your straightforward problem and explanation. If my report was based on a SQL query that uses UNION ALL to combine the data (meaning that each record shares the same primary key column nammed “KeyID”), can I still use this method? It seems that because my PK’s have the same name, my hidden page items all get set to the same “KeyID” regardless of the type of record the user selects.
Jeffrey Kemp
19 October 2021 - 11:00 pm
Hi Collin,
There’s always a way – you just need to provide enough information so that your conditions on the page processes can differentiate them. For example, you could add an additional column to your query which tells the page which target to use:
select KeyID, 'typeA' as target_type from ...union all
select KeyID, 'typeB' as target_type from ...
You would have a hidden page item for KeyID and for target_type; the condition on the page processes would be based on the value of target_type.
Jeff
Collin McGhee
20 October 2021 - 11:20 pm
Jeff,
Thanks for your quick response and accurate solution! Will try to implement your modal window solution as well.
Collin McGhee
21 October 2021 - 12:30 am
Jeff,
Sorry for the comment spam on your page, but I still can’t seem to get your modal window solution to work. I created a blank page off the page creation wizard (empty modal), and added one region with the page items necessary for the conditional redirect. Then set my page 1 (normal window) to redirect to itself while passing the required values, which should then trigger the branch to redirect to the (empty modal). When I select the link column on my original page (normal window), the page redirects to itself but does not attempt to branch to the redirect modal window. Which makes me wonder if “You cannot open a modal window from a page rendering process within a non-modal window”, then why would the redirect page be a modal window? The 3 page redirect solution even works when I change all of the pages to a normal windows. I would also like to add that I appreciate the time and effort you’ve put into your solutions, hope that maybe there is another easy fix?
Jeffrey Kemp
21 October 2021 - 8:12 am
Hi Collin, sorry but this method doesn’t work as described if the target is a modal window. There is an alternative technique which I describe in a comment above.
I’ll update the article accordingly.