Hi @ll,

I have an active link that makes a "set fields" action from the customer form when "Return" in the Company Name field.
The condition to set the fields is 'CompanyName' LIKE (( "%" + $Extern Company Name+$) + "%" )
If "Multiple Requests Match" I have "Display a List".

When I don't write anything in the Company Name field and press "Return", the query to the database (I've activated the Database log) condition is WHERE COMPANYNAME LIKE "%" + " " + "%", and a blank appears in the middle. So the list does not include the companys whose name does not have a blank.

For example "IBM" would not appear, but "IBM SA" would appear in the list because it has a blank in the Company Name.

Remedy 4.05.02, Sun, Oracle 8i.

I hope you can help me.

Thanks in advance and kind regards.


David Arroyo.

Change your condition to

'CompanyName' LIKE (("%" + $Extern Company Name+$) + "%")
OR 'CompanyName' LIKE (("%" + $Extern Company Name+$))
OR 'CompanyName' LIKE (($Extern Company Name+$) + "%")

The reason you are getting the results that you are getting is that you have considered the condition for the match occuring in the middle of the string, and not at the begining and end of the string.

Cheers

Joe DeSouza,
Remedy Tech Consultant,
CyberMAK Information Systems.

----- Original Message -----
From: David Arroyo
To: ARSLIST@LISTSERV.VISTAIT.COM
Sent: Tuesday, April 02, 2002 2:25 PM
Subject: "Display a List" in an Active Link

Hi @ll,

I have an active link that makes a "set fields" action from the customer form when "Return" in the Company Name field.
The condition to set the fields is 'CompanyName' LIKE (( "%" + $Extern Company Name+$) + "%" )
If "Multiple Requests Match" I have "Display a List".

When I don't write anything in the Company Name field and press "Return", the query to the database (I've activated the Database log) condition is WHERE COMPANYNAME LIKE "%" + " " + "%", and a blank appears in the middle. So the list does not include the companys whose name does not have a blank.

For example "IBM" would not appear, but "IBM SA" would appear in the list because it has a blank in the Company Name.

Remedy 4.05.02, Sun, Oracle 8i.

I hope you can help me.

Thanks in advance and kind regards.


David Arroyo.

Not sure what your question is, but I assume you want to return ALL companies if CompanyName is NULL.

One way to do this: Add a Run If qualification to this AL : ExternalCompanyName != $NULL$
Then you could add a second AL that fires when ExternalCompanyName = NULL, but without the Set Field Qualification. (returning all companies)

OR

You could probably just modify the Set Field Qual. to something like:

($ExternalCompanyName$ != " " AND 'CompanyName' LIKE (( "%" + $Extern Company Name+$) + "%" ) )
OR $ExternalCompanyName$ = " "

As you have discovered: In Oracle, in a Query to the Database, NULL is interpreted as " " (in the Run if, NULL is still NULL.)

So in the second Qual I gave you, It looks for matches if the value is not Null, or just returns everything (true) if it is Null.

I didn't test this, but it should work :) I have done similar in the past.

Mark Blankenship

-----Original Message-----
From: David Arroyo [mailto:darroyo@TELENIUM.ES]
Sent: Tuesday, April 02, 2002 6:25 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: "Display a List" in an Active Link


Hi @ll,

I have an active link that makes a "set fields" action from the customer form when "Return" in the Company Name field.
The condition to set the fields is 'CompanyName' LIKE (( "%" + $Extern Company Name+$) + "%" )
If "Multiple Requests Match" I have "Display a List".

When I don't write anything in the Company Name field and press "Return", the query to the database (I've activated the Database log) condition is WHERE COMPANYNAME LIKE "%" + " " + "%", and a blank appears in the middle. So the list does not include the companys whose name does not have a blank.

For example "IBM" would not appear, but "IBM SA" would appear in the list because it has a blank in the Company Name.

Remedy 4.05.02, Sun, Oracle 8i.

I hope you can help me.

Thanks in advance and kind regards.


David Arroyo.

Thanks Mark, finally your solution works!

----- Original Message -----
From: Mark Blankenship
To: ARSLIST@LISTSERV.VISTAIT.COM
Sent: Tuesday, April 02, 2002 3:11 PM
Subject: Re: "Display a List" in an Active Link

Not sure what your question is, but I assume you want to return ALL companies if CompanyName is NULL.

One way to do this: Add a Run If qualification to this AL : ExternalCompanyName != $NULL$
Then you could add a second AL that fires when ExternalCompanyName = NULL, but without the Set Field Qualification. (returning all companies)

OR

You could probably just modify the Set Field Qual. to something like:

($ExternalCompanyName$ != " " AND 'CompanyName' LIKE (( "%" + $Extern Company Name+$) + "%" ) )
OR $ExternalCompanyName$ = " "

As you have discovered: In Oracle, in a Query to the Database, NULL is interpreted as " " (in the Run if, NULL is still NULL.)

So in the second Qual I gave you, It looks for matches if the value is not Null, or just returns everything (true) if it is Null.

I didn't test this, but it should work :) I have done similar in the past.

Mark Blankenship

-----Original Message-----
From: David Arroyo [mailto:darroyo@TELENIUM.ES]
Sent: Tuesday, April 02, 2002 6:25 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: "Display a List" in an Active Link


Hi @ll,

I have an active link that makes a "set fields" action from the customer form when "Return" in the Company Name field.
The condition to set the fields is 'CompanyName' LIKE (( "%" + $Extern Company Name+$) + "%" )
If "Multiple Requests Match" I have "Display a List".

When I don't write anything in the Company Name field and press "Return", the query to the database (I've activated the Database log) condition is WHERE COMPANYNAME LIKE "%" + " " + "%", and a blank appears in the middle. So the list does not include the companys whose name does not have a blank.

For example "IBM" would not appear, but "IBM SA" would appear in the list because it has a blank in the Company Name.

Remedy 4.05.02, Sun, Oracle 8i.

I hope you can help me.

Thanks in advance and kind regards.


David Arroyo.