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.

linkbuilder1

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:

  1. Add some hidden items to the page to store the parameters for each target page;
  2. Add a Branch to the page for each target page; and
  3. 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:

linkbuilder2

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:

branch1
branch2

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

Using LetsEncrypt on Amazon Linux
APEX Plugin: Password Strength Estimator

Comments

  1. Really useful, very clear and well written

  2. 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.

  3. what is the way for opening modal window?

    • 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

  4. 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

    • 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?

  5. 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?

    • 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.

  6. 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.

    • 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

  7. Jeff,

    Thanks for your quick response and accurate solution! Will try to implement your modal window solution as well.

  8. 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?

    • 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.

Leave a Reply

Your email address will not be published / Required fields are marked *