OTN Discussion Forums : Conditional Redirect ...

Dr.Dimitri

Posts: 700
Registered: 12/08/05

Conditional Redirect
Posted: Dec 16, 2006 1:47 AM

Hi,

I have the following Problem: I'm using Oracle text to search for customers. The user has one simple textfield where he can search for zip-code, name, city etc. - works greats.
But I want to improve this a little: If the criterias matches exactly for one customer the page should redirect directly the the detail form.
At the moment a report is created where the matched customers are listed, but it simply isn't logical to the user (and to me too) that he get's a list with only one customer he has to click on again.

So how can I determine if my query has more then one result and decide which action should take place - either show a new Report or redirect to the detail page.

Dim

andrew.tulley@o...

Posts: 261
Registered: 11/30/05

Re: Conditional Redirect
Posted: Dec 16, 2006 3:17 AM

in response to: Dr.Dimitri

Reply

Doc,

Here's some steps which form one solution for you:

1) Add a hidden item to your 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:

<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). Make this branch conditional upon :REQUEST = 'SINGLE_MATCH_FOUND'

It's a method I've only just thought of and haven't fully tested it but I'm pretty sure it should work.

Hope that's of some help,
Andy

Dr.Dimitri

Posts: 700
Registered: 12/08/05

Re: Conditional Redirect
Posted: Dec 16, 2006 3:34 AM

in response to: andrew.tulley@o...

Reply

Hey that sounds great. I will test this on monday, but I think it should work

Thanks a lot

Dim

andrew.tulley@o...

Posts: 261
Registered: 11/30/05

Re: Conditional Redirect
Posted: Dec 16, 2006 3:43 AM

in response to: Dr.Dimitri

Reply

Dim,

I'd appreciate if you could let me know how you get on with this, if it works well or not.

Thanks
Andy

Dr.Dimitri

Posts: 700
Registered: 12/08/05

Re: Conditional Redirect
Posted: Dec 19, 2006 12:53 AM

in response to: andrew.tulley@o...

Reply

Hi Andy,

it works perfect (I'm using document.getElementById instead of your $x() - is this a special trick? I don't know JS very well)
The only problem I have is, that there is an additional column in the report now. Do you know a way not to show this column?

Dim

Dr.Dimitri

Posts: 700
Registered: 12/08/05

Re: Conditional Redirect
Posted: Dec 19, 2006 12:57 AM

in response to: Dr.Dimitri

Reply

I named the colum with a " " so it's (nearly) not visible. This should be ok.

Dim

andrew.tulley@o...

Posts: 261
Registered: 11/30/05

Re: Conditional Redirect
Posted: Dec 19, 2006 3:59 AM

in response to: Dr.Dimitri

Reply

Dim,

Glad it works.

$x() is an ApEx-specific (from version 2.2 I think) wrapper around the native Javascript getElementById function. Before ApEx 2.2 this was called html_GetElement(). It will do the same thing.

If you run your page and look at the HTML source you'll see a line like:

<script src="/i/javascript/htmldb_html_elements.js" type="text/javascript"></script>

The $x() and html_GetElement() functions are defined in that htmldb_html_elements.js file.

To get round the problem of there being an extra column that you don't want, you could concatenate <input type="hidden" id ="thePersonId" value="'||person_id||'"> with an existing column that you do want. So if you are already displaying a column called surname, for example, you could have

SELECT (surname||'<input type="hidden" id ="thePersonId" value="'||person_id||'">') as surname...

That way you won't have the extraneous column.

Andy

Tonnie

Posts: 71
Registered: 11/13/00

Re: Conditional Redirect
Posted: Dec 19, 2006 4:47 AM

in response to: andrew.tulley@o...

Reply

can't you use an On Load: Before Header branch, with the right criteria in the conditions?

andrew.tulley@o...

Posts: 261
Registered: 11/30/05

Re: Conditional Redirect
Posted: Dec 19, 2006 5:07 AM

in response to: Tonnie

Reply

Tonnie,

Yes you could do that.

So were you thinking something like this pseudo-code for the condition?

select count(*)
INTO my_counter
FROM persons
WHERE contains(concast,:SEARCH_PARAM) >0;

If my_counter = 1 then return true
else return false;
end if;

The trouble is: wouldn't this mean that everytime the WHERE clause in the source for the report region was changed, this Before Header Branch condition would also have to be changed to reflect this? Wouldn't it also mean that, if more than one row is returned by the query, you are essentially running it twice: once in the branch condition and once in the report region itself?

Don't know if they're particular big issues (maybe not!)... just thinking out loud.

Andy

Dr.Dimitri

Posts: 700
Registered: 12/08/05

Re: Conditional Redirect
Posted: Dec 19, 2006 12:18 PM

in response to: andrew.tulley@o...

Reply

Hi Andy,

thanks for you're suggenstion, I will concatenate the new column with another one.

@Tonnie
I don't want to do an extra count. The search is pretty fast on a 15 million row table, but doing it twice would put significant (and avoidable) extra load on the machine. The current solution is absolutely ok.

Dim

deepa.j.rao@ora...

Posts: 60
Registered: 04/30/07

Re: Conditional Redirect
Posted: Jun 21, 2007 5:57 AM

in response to: andrew.tulley@o...

Reply

Hi Dim,

I want to perform something similar to this , but my region type is PL/SQL function body returning sql query...it gives me an error when i try to add the input type tag in the select stmnt.My select stmnt is somethin like this
q1 := ' Select col1, col2 , col3, col4, col5
from table1
WHERE 1=1 ';...........retuen q1;
I have other search criteria's which i concat with this above string.
Here col1 is the primary key....
Can u guide me out here.

Thnks in advance,

Message was edited by:
user572147