Using multiple wildcards in selection formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18005
Printed Date: 04 May 2024 at 6:35am
Topic: Using multiple wildcards in selection formula
Posted By: theotherdodge
Subject: Using multiple wildcards in selection formula
Date Posted: 14 Nov 2012 at 3:51am
|
Is there a way that I can create a parameter that will allow the user to input mulitple search items? For example, the user would enter: gloves,hat,glasses and those terms would search a description field for any of those? I can get it to work for a single wildcard term or even multiple but if the description field contains that search term and another word, it wont pick it up. So basically I need to use a "like" statement... (if {?DescWildcard} <> 'x' then not (IF "," & lowercase({POIT.Description}) & "," IN "," & {?DescWildcard} & "," THEN 0=1 ELSE 1=1) else 1=1) Thanks!
|
Replies:
Posted By: comatt1
Date Posted: 14 Nov 2012 at 5:16am
field like '*' + {parm?} + '*'
you will have to remind users to use wildcards in the parameter statement,
or if you want commas
field like
{field} like '*'+ Replace({parm?},',' ,'*' )+'*'
|
Posted By: theotherdodge
Date Posted: 14 Nov 2012 at 5:37am
|
That didn't work...at least from what I tested... and lowercase({POIT.Description}) like '*'+ Replace({?DescWildcard},',' ,'*' )+'*' When I just put in 1 description, it works fine. But if I put in multiple, it doesn't pull anything. ex: goggles,wax
|
Posted By: comatt1
Date Posted: 14 Nov 2012 at 5:40am
Originally posted by theotherdodge
That didn't work...at least from what I tested... and lowercase({POIT.Description}) like '*'+ Replace({?DescWildcard},',' ,'*' )+'*' When I just put in 1 description, it works fine. But if I put in multiple, it doesn't pull anything. ex: goggles,wax
lowercase({POIT.Description}) like '*'+ Replace({POIT.Description},',' ,'*' )+'*'
this should work
|
Posted By: theotherdodge
Date Posted: 14 Nov 2012 at 6:01am
|
that didn't work..did you mean: lowercase({POIT.Description}) like '*'+ Replace({?DescWildcard},',' ,'*' )+'*' Replace({parameter})....But even that didn't work?
|
Posted By: comatt1
Date Posted: 14 Nov 2012 at 6:03am
what is the input parameter on the field?
you may have to use an array
|
Posted By: comatt1
Date Posted: 14 Nov 2012 at 6:14am
Local NumberVar Array b;
local numbervar count ab:=
LENGTH({?DescWildcard}) - LENGTH(REPLACE({?DescWildcard}, 'Y', ''));
Redim b[ab];
Local NumberVar i;
For i := 1 To ab Do
(
b := ab * i
);
and lowercase({POIT.Description}) in **** have no idea if this can work, DELL LOCK HELP
|
Posted By: theotherdodge
Date Posted: 14 Nov 2012 at 9:46am
|
I can't use an array because of the system that runs Crystal doesn't allow it...
|
Posted By: comatt1
Date Posted: 14 Nov 2012 at 9:53am
(if LENGTH({?DescWildcard}) - LENGTH(REPLACE({?DescWildcard}, ',', ''))=LENGTH({?DescWildcard}) then
lowercase({POIT.Description}) = {?DescWildcard} else
lowercase({POIT.Description}) in [{?DescWildcard}])
if they delineate with commas this should work
this works with a single parm, if descwildcard always will be matt,been,grease
it will work
|
Posted By: theotherdodge
Date Posted: 14 Nov 2012 at 11:00am
|
Thanks comatt1. Still no joy. POIT.Description will usually contain a set of words such as "gas masks" or "safety masks" or "half face masks" or "freight" or "freight charges". The user wants to be able to enter search words in the parameter to pick up those records such as: "mask,freight"
|
Posted By: comatt1
Date Posted: 14 Nov 2012 at 11:07am
|
Originally posted by comatt1
(if LENGTH({?DescWildcard}) - LENGTH(REPLACE({?DescWildcard}, ',', ''))=LENGTH({?DescWildcard}) then
lowercase({POIT.Description}) = {?DescWildcard} else
lowercase({POIT.Description}) in [{?DescWildcard}])
if they delineate with commas this should work
this works with a single parm, if descwildcard always will be matt,been,grease
it will work
(if LENGTH({?DescWildcard}) - LENGTH(replace(REPLACE({?DescWildcard}, ',', ''),' ','*'))=LENGTH({?DescWildcard}) then
lowercase({POIT.Description}) = {?DescWildcard} else
lowercase({POIT.Description}) in [{?DescWildcard}])
I think this may work
|
Posted By: theotherdodge
Date Posted: 14 Nov 2012 at 12:02pm
|
No, still doesn't work. It will find a single word if that description field only contains a single word such as "wax". But if it contains "compound wax" it won't find it. Also, if you enter mulitple terms such as "wax,glasses" it won't find anything regardless of the conditions of the description field.
|
Posted By: DBlank
Date Posted: 14 Nov 2012 at 12:03pm
|
pretty sure the only way is with an array
|
Posted By: theotherdodge
Date Posted: 15 Nov 2012 at 2:11am
|
Well thats a bummer because the application that uses it can't take multiple values for the array.
|
Posted By: DBlank
Date Posted: 15 Nov 2012 at 3:54am
|
just as a test, can you set the param to accept multiple values and have it function with a direct string match on multiple entries?
|
Posted By: theotherdodge
Date Posted: 21 Nov 2012 at 5:39am
|
DBlank, I don't understand what you are saying...? Our accounting system, which uses Crystal for it's reports will not allow for multiple params... The only way to possibly use multiple params is to delinate with a comma. For example: hat,goggles,ear plug
|
Posted By: DBlank
Date Posted: 21 Nov 2012 at 7:33am
|
when setting up a param you can set the option 'Allow multiple values' to true. However it sounds like you have a custom crystal viever in your app that is preventing that. I was hoping if you set the param up like this you could at least test multiple value without the like statement. Sounds like that is not possible though.
|
|