from arslist. I hope this time too...

I have a form A with a field B.
I also have form C with field D. Field D have a seach menu, values from
field B.

Data in field B are:

(Examples)

EXCEL
WORD
MICROSOFT PROGRAM
CAD 2000
PROJECT

Notice that some entries have space, some just one word.

Situation now for users: When they click on the menyarrow field D blank,
only entries with space will been in the list. But if they enter % in the
field every row is visible.
If they enter 'SOFT' just MICROSOFT PROGRAM is matching.

Menuqualification:
( 'Status' = "AKTIV") AND ( 'Artikel_' LIKE ( "%" + $536870940$) + "%" )

Of course, I could skip LIKE and only have 'Status' = "AKTIV", but I want
users to be able to enter just a string.

Is there a way to have field D blank and the meny showing every row, and if
a string is entered, only matching entries shall be in the list ?

Thanks, L ars


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

I think I've seen a similar problem before. Where a "%" + $field$ + "%" seems to resolve to "% %" instead of "%%" when the field is NULL.

Only one suggestion, how about if you give it a default value of "%". Does that help???
You could have a filter which checks for a value "%" and sets it to $NULL$ on submit to stop this corrupting your data but it might just work. Also the pattern match for a single character ("_" I think) might also work.

regards, Barry

-----Original Message-----
From: lars.pettersson@DATA.VATTENFALL.SE
[mailto:lars.pettersson@DATA.VATTENFALL.SE]
Sent: Monday, 10 January 2000 7:44
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Menu - please help !


Hi - almost every time I have asked a question solutions have been found
from arslist. I hope this time too...

I have a form A with a field B.
I also have form C with field D. Field D have a seach menu, values from
field B.

Data in field B are:

(Examples)

EXCEL
WORD
MICROSOFT PROGRAM
CAD 2000
PROJECT

Notice that some entries have space, some just one word.

Situation now for users: When they click on the menyarrow field D blank,
only entries with space will been in the list. But if they enter % in the
field every row is visible.
If they enter 'SOFT' just MICROSOFT PROGRAM is matching.

Menuqualification:
( 'Status' = "AKTIV") AND ( 'Artikel_' LIKE ( "%" + $536870940$) + "%" )

Of course, I could skip LIKE and only have 'Status' = "AKTIV", but I want
users to be able to enter just a string.

Is there a way to have field D blank and the meny showing every row, and if
a string is entered, only matching entries shall be in the list ?

Thanks, L ars


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

EOM

NOTICE - This message contains information intended only for the use of the addressee named above. It may also be confidential and/or privileged. If you are not the intended recipient of this message you are hereby notified that you must not disseminate, copy or take any action in reliance on it. If you have received this message in error please notify postmaster@bhp.com.au.


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

(Default not possible because the field is set no $NULL$ sometimes. This
workaround will work for me.

"%" + $field$ + "%" resolving to "% %" instead of "%%" when the field is
NULL must be a 'bug' ?

L ars

-----Ursprungligt meddelande-----
Från: Cox, Barry BJ [mailto:Cox.Barry.BJ@BHP.COM.AU]
Skickat: den 9 januari 2000 23:54
Till: ARSLIST@LISTSERV.VISTAIT.COM
Ämne: Re: Menu - please help !


Lars,

I think I've seen a similar problem before. Where a "%" + $field$ + "%"
seems to resolve to "% %" instead of "%%" when the field is NULL.

Only one suggestion, how about if you give it a default value of "%". Does
that help???
You could have a filter which checks for a value "%" and sets it to $NULL$
on submit to stop this corrupting your data but it might just work. Also
the pattern match for a single character ("_" I think) might also work.

regards, Barry

-----Original Message-----
From: lars.pettersson@DATA.VATTENFALL.SE
[mailto:lars.pettersson@DATA.VATTENFALL.SE]
Sent: Monday, 10 January 2000 7:44
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Menu - please help !


Hi - almost every time I have asked a question solutions have been found
from arslist. I hope this time too...

I have a form A with a field B.
I also have form C with field D. Field D have a seach menu, values from
field B.

Data in field B are:

(Examples)

EXCEL
WORD
MICROSOFT PROGRAM
CAD 2000
PROJECT

Notice that some entries have space, some just one word.

Situation now for users: When they click on the menyarrow field D blank,
only entries with space will been in the list. But if they enter % in the
field every row is visible.
If they enter 'SOFT' just MICROSOFT PROGRAM is matching.

Menuqualification:
( 'Status' = "AKTIV") AND ( 'Artikel_' LIKE ( "%" + $536870940$) + "%" )

Of course, I could skip LIKE and only have 'Status' = "AKTIV", but I want
users to be able to enter just a string.

Is there a way to have field D blank and the meny showing every row, and if
a string is entered, only matching entries shall be in the list ?

Thanks, L ars


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

EOM

NOTICE - This message contains information intended only for the use of the
addressee named above. It may also be confidential and/or privileged. If
you are not the intended recipient of this message you are hereby notified
that you must not disseminate, copy or take any action in reliance on it.
If you have received this message in error please notify
postmaster@bhp.com.au.


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

When we cam across this same issue, we issued two identical active links with different qualification lines. One to account for the "added" space which ends up in the SQL sent to the database whenever the field on the screen is NULL, and one to account for the wildcard search based upon information provided by the user.

Glen

-----Original Message-----
From: lars.pettersson@DATA.VATTENFALL.SE [ mailto:lars.pettersson@DATA.VATTENFALL.SE]
Sent: Monday, January 10, 2000 2:33 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: VB: Menu - please help !

Barry - I'll try to set fields to "%" on menuchoice in the menu before
(Default not possible because the field is set no $NULL$ sometimes. This
workaround will work for me.

"%" + $field$ + "%" resolving to "% %" instead of "%%" when the field is
NULL must be a 'bug' ?

L ars

-----Ursprungligt meddelande-----
Från: Cox, Barry BJ [ mailto:Cox.Barry.BJ@BHP.COM.AU]
Skickat: den 9 januari 2000 23:54
Till: ARSLIST@LISTSERV.VISTAIT.COM
Ämne: Re: Menu - please help !


Lars,

I think I've seen a similar problem before. Where a "%" + $field$ + "%"
seems to resolve to "% %" instead of "%%" when the field is NULL.

Only one suggestion, how about if you give it a default value of "%". Does
that help???
You could have a filter which checks for a value "%" and sets it to $NULL$
on submit to stop this corrupting your data but it might just work. Also
the pattern match for a single character ("_" I think) might also work.

regards, Barry

-----Original Message-----
From: lars.pettersson@DATA.VATTENFALL.SE
[ mailto:lars.pettersson@DATA.VATTENFALL.SE]
Sent: Monday, 10 January 2000 7:44
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Menu - please help !


Hi - almost every time I have asked a question solutions have been found
from arslist. I hope this time too...

I have a form A with a field B.
I also have form C with field D. Field D have a seach menu, values from
field B.

Data in field B are:

(Examples)

EXCEL
WORD
MICROSOFT PROGRAM
CAD 2000
PROJECT

Notice that some entries have space, some just one word.

Situation now for users: When they click on the menyarrow field D blank,
only entries with space will been in the list. But if they enter % in the
field every row is visible.
If they enter 'SOFT' just MICROSOFT PROGRAM is matching.

Menuqualification:
( 'Status' = "AKTIV") AND ( 'Artikel_' LIKE ( "%" + $536870940$) + "%" )

Of course, I could skip LIKE and only have 'Status' = "AKTIV", but I want
users to be able to enter just a string.

Is there a way to have field D blank and the meny showing every row, and if
a string is entered, only matching entries shall be in the list ?

Thanks, L ars


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

EOM

NOTICE - This message contains information intended only for the use of the
addressee named above. It may also be confidential and/or privileged. If
you are not the intended recipient of this message you are hereby notified
that you must not disseminate, copy or take any action in reliance on it.
If you have received this message in error please notify
postmaster@bhp.com.au.


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

Unfortunately, this is not a bug, according to Remedy. I've already been
bitten by this one, and here is the final reply to a long string of messages
regarding this behaviour:

Hi Dann,

Regarding ticket #237968, the issue that you had escalated (Bug ID
#SW00058247) in which using a qualification such as the following for a
Search/Query Menu:

( 'FirstName' LIKE ($536870913$ + "%" )) AND ( 'LastName' LIKE ($536870915$
+ "%" ))

did not yield the results you were expecting but instead appeared to be a
possible bug in which the Remedy software was issuing the SQL query with the
where clause looking for " %" rather than "%" and since none
of the FirstName records started with a space, the query would return no
rows.

Engineering has investigated and has informed me that this is not actually a
bug but a design decision they made in order to be compatible across all
databases. They have closed the bug as implemented. The decision to pad a
space before the wildcard was made back when they were addressing an issue
that was affecting many customers using a 4.0 User tool against a Server
running an Oracle database (Bug ID #SW00054853).

Bug ID # SW00054853
User Tool incompatibility error. The 3.2 and 4.0 user tools pass different
qualifier structures to the server and this is what is causing zero rows to
be passed back from Oracle when using 4.0 UT, but 4 rows when using 3.2 UT.

In short, the 4.0 UT is placing empty strings ("") in the right hand side of
the != relational operations whereas the 3.2 UT is using a string with one
blank space (" "). This is important when working with Oracle, but not so
with MS SQL server. The ARS Server converts the ARQualifierStruct to
standard SQL.

To resolve the issue we always send " "(space) for empty fields (NULL
character fields) to avoid SQL error on certain databases. The behavior
should now be the same across all databases.



-----Original Message-----
From: lars.pettersson@DATA.VATTENFALL.SE
[mailto:lars.pettersson@DATA.VATTENFALL.SE]
Sent: Sunday, January 09, 2000 11:33 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: VB: Menu - please help !


Barry - I'll try to set fields to "%" on menuchoice in the menu before
(Default not possible because the field is set no $NULL$ sometimes. This
workaround will work for me.

"%" + $field$ + "%" resolving to "% %" instead of "%%" when the field is
NULL must be a 'bug' ?

L ars

-----Ursprungligt meddelande-----
Från: Cox, Barry BJ [mailto:Cox.Barry.BJ@BHP.COM.AU]
Skickat: den 9 januari 2000 23:54
Till: ARSLIST@LISTSERV.VISTAIT.COM
Ämne: Re: Menu - please help !


Lars,

I think I've seen a similar problem before. Where a "%" + $field$ + "%"
seems to resolve to "% %" instead of "%%" when the field is NULL.

Only one suggestion, how about if you give it a default value of "%". Does
that help???
You could have a filter which checks for a value "%" and sets it to $NULL$
on submit to stop this corrupting your data but it might just work. Also
the pattern match for a single character ("_" I think) might also work.

regards, Barry

-----Original Message-----
From: lars.pettersson@DATA.VATTENFALL.SE
[mailto:lars.pettersson@DATA.VATTENFALL.SE]
Sent: Monday, 10 January 2000 7:44
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Menu - please help !


Hi - almost every time I have asked a question solutions have been found
from arslist. I hope this time too...

I have a form A with a field B.
I also have form C with field D. Field D have a seach menu, values from
field B.

Data in field B are:

(Examples)

EXCEL
WORD
MICROSOFT PROGRAM
CAD 2000
PROJECT

Notice that some entries have space, some just one word.

Situation now for users: When they click on the menyarrow field D blank,
only entries with space will been in the list. But if they enter % in the
field every row is visible.
If they enter 'SOFT' just MICROSOFT PROGRAM is matching.

Menuqualification:
( 'Status' = "AKTIV") AND ( 'Artikel_' LIKE ( "%" + $536870940$) + "%" )

Of course, I could skip LIKE and only have 'Status' = "AKTIV", but I want
users to be able to enter just a string.

Is there a way to have field D blank and the meny showing every row, and if
a string is entered, only matching entries shall be in the list ?

Thanks, L ars


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

EOM

NOTICE - This message contains information intended only for the use of the
addressee named above. It may also be confidential and/or privileged. If
you are not the intended recipient of this message you are hereby notified
that you must not disseminate, copy or take any action in reliance on it.
If you have received this message in error please notify
postmaster@bhp.com.au.


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist

use $field$ + "%" in a query, Remedy makes the database do
the string concatenation. It sends the string as
field LIKE ' ' || '%' when the field is NULL. They should
process the concatenation themselves. Change your query to
'Status' = "AKTIV" AND ( $536870940$ = ' ' OR
'Artikel_' LIKE "%" + $536870940$ + "%" )

This will match ignore the LIKE condition when the field is
NULL. You can replace the ' ' with $NULL$ for readability.

-----Original Message-----
From: Dann Cox [mailto:dcox@SD68.BC.CA]
Sent: Monday, January 10, 2000 11:54 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: Menu - please help !


Lars,

Unfortunately, this is not a bug, according to Remedy. I've already been
bitten by this one, and here is the final reply to a long string of messages
regarding this behaviour:

Hi Dann,

Regarding ticket #237968, the issue that you had escalated (Bug ID
#SW00058247) in which using a qualification such as the following for a
Search/Query Menu:

( 'FirstName' LIKE ($536870913$ + "%" )) AND ( 'LastName' LIKE ($536870915$
+ "%" ))

did not yield the results you were expecting but instead appeared to be a
possible bug in which the Remedy software was issuing the SQL query with the
where clause looking for " %" rather than "%" and since none
of the FirstName records started with a space, the query would return no
rows.

Engineering has investigated and has informed me that this is not actually a
bug but a design decision they made in order to be compatible across all
databases. They have closed the bug as implemented. The decision to pad a
space before the wildcard was made back when they were addressing an issue
that was affecting many customers using a 4.0 User tool against a Server
running an Oracle database (Bug ID #SW00054853).

Bug ID # SW00054853
User Tool incompatibility error. The 3.2 and 4.0 user tools pass different
qualifier structures to the server and this is what is causing zero rows to
be passed back from Oracle when using 4.0 UT, but 4 rows when using 3.2 UT.

In short, the 4.0 UT is placing empty strings ("") in the right hand side of
the != relational operations whereas the 3.2 UT is using a string with one
blank space (" "). This is important when working with Oracle, but not so
with MS SQL server. The ARS Server converts the ARQualifierStruct to
standard SQL.

To resolve the issue we always send " "(space) for empty fields (NULL
character fields) to avoid SQL error on certain databases. The behavior
should now be the same across all databases.



-----Original Message-----
From: lars.pettersson@DATA.VATTENFALL.SE
[mailto:lars.pettersson@DATA.VATTENFALL.SE]
Sent: Sunday, January 09, 2000 11:33 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: VB: Menu - please help !


Barry - I'll try to set fields to "%" on menuchoice in the menu before
(Default not possible because the field is set no $NULL$ sometimes. This
workaround will work for me.

"%" + $field$ + "%" resolving to "% %" instead of "%%" when the field is
NULL must be a 'bug' ?

L ars

-----Ursprungligt meddelande-----
Från: Cox, Barry BJ [mailto:Cox.Barry.BJ@BHP.COM.AU]
Skickat: den 9 januari 2000 23:54
Till: ARSLIST@LISTSERV.VISTAIT.COM
Ämne: Re: Menu - please help !


Lars,

I think I've seen a similar problem before. Where a "%" + $field$ + "%"
seems to resolve to "% %" instead of "%%" when the field is NULL.

Only one suggestion, how about if you give it a default value of "%". Does
that help???
You could have a filter which checks for a value "%" and sets it to $NULL$
on submit to stop this corrupting your data but it might just work. Also
the pattern match for a single character ("_" I think) might also work.

regards, Barry

-----Original Message-----
From: lars.pettersson@DATA.VATTENFALL.SE
[mailto:lars.pettersson@DATA.VATTENFALL.SE]
Sent: Monday, 10 January 2000 7:44
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Menu - please help !


Hi - almost every time I have asked a question solutions have been found
from arslist. I hope this time too...

I have a form A with a field B.
I also have form C with field D. Field D have a seach menu, values from
field B.

Data in field B are:

(Examples)

EXCEL
WORD
MICROSOFT PROGRAM
CAD 2000
PROJECT

Notice that some entries have space, some just one word.

Situation now for users: When they click on the menyarrow field D blank,
only entries with space will been in the list. But if they enter % in the
field every row is visible.
If they enter 'SOFT' just MICROSOFT PROGRAM is matching.

Menuqualification:
( 'Status' = "AKTIV") AND ( 'Artikel_' LIKE ( "%" + $536870940$) + "%" )

Of course, I could skip LIKE and only have 'Status' = "AKTIV", but I want
users to be able to enter just a string.

Is there a way to have field D blank and the meny showing every row, and if
a string is entered, only matching entries shall be in the list ?

Thanks, L ars


To Unsubscribe send e-mail to:
body of text is: unsubscribe arslist