Jumping directly to a detail page when a search returns just one result

It’s common to build a custom form and report in ApEx to allow users to search data. If the search returns just one result, it might, depending on the nature of the application, be sensible and more intuitive for the user to be taken directly to the details for that one hit rather than being shown a list of Search Results containing just one entry and then having to click that one entry to see its details. This is a method for doing just that: Jumping straight to the details for an entry if it is the only one returned by a search.

1) Add a hidden item to your Search Results page called: PX_SINGLE_MATCH_PERSON_ID

2) If the SQL for your query is currently something like:

SELECT person_id
, surname
, first_name
, city
, score(1)
FROM persons
WHERE contains(concat,:PX_SEARCH_STRING,1) > 0

Change it to be something like:

SELECT '<input type="hidden" id ="thePersonId" value="'||person_id||'">'||person_id
, surname
, first_name
, city
, score(1)
FROM persons
WHERE contains(concat,:PX_SEARCH_STRING,1) > 0

Essentially, we just need a HTML form item of some kind with a known ID to hold the primary key ID of the row returned so that in the next step we can reference it with javascript.

3) Add the following to the Region Footer of your Report Region (again on your Search Results page):

<script language="javascript">
if (#ROWS_FETCHED# == 1) {
$x('PX_SINGLE_MATCH_PERSON_ID').value = $x('thePersonId').value;
doSubmit('SINGLE_MATCH_FOUND');} </script>

4) Create a branch of type: Branch to Page or URL. Construct the URL it goes to (whatever your “Person Detail” page is. You can reference &PX_SINGLE_MATCH_ID. to set whatever item you need to on your “Person Detail” page).

E.g. If your “Person Details” page is page 10 and the item on page 10 which holds the unique ID for the person’s details to be viewed is :P10_PERSON_ID then your branch URL would be something like:

f?p=&APP_ID.:10:&SESSION.::::P10_PERSON_ID: &PX_SINGLE_MATCH_ID.

Make this branch conditional upon :REQUEST = ‘SINGLE_MATCH_FOUND’

That’s it.
You could achieve the same result in different ways (e.g performing a count on the same SQL query then conditionally redirecting). The advantage of the way outlined above, from a performance point of view, is that the SQL query never needs to be run twice.

Related forum thread here: http://forums.oracle.com/forums/thread.jspa?messageID=1602139