Use parameter in a filter

Hello,

I have a database field called Product.
I also have a parameter called Product.  You can select individually A, B, and C.

So if you select A, B, and C in the parameter, I want the filter to capture all Products with A, B, or C in it.  So for example, it would return records like:

AB
BC
AB
etc.....

Just not sure how to code that.  Thanks for your help.
Joel
Genius123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Use a filter condition following this logic:
{Product} like "*" & {?Product} & "*"

Open in new window

Genius123Author Commented:
Thank you Ido.  I actually tried that.  I get an error:

"This array must be subscripted........"
mlmccCommented:
I thought that wouldn't work so I tried it myself and got the same result.

Try it this way

Local NumberVar Index;
Local BooleanVar Include := False;

For Index := 1 to UBound({?My Parameter}) do
    Include := Include OR     {Product} LIKE '*' & {?Product}[Index] & '*';
Include

Open in new window


mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Genius123Author Commented:
Actually, I just used the + sign instead of & and it worked.  Thank you both.  I will give Ido credit since he was pretty close.
Genius123Author Commented:
Had to use & instead of +.
mlmccCommented:
You said his doesn't work for multiple parameter values.

mlmcc
James0628Commented:
If the parameter allows multiple values, "*" + {?param} + "*" will not work.

 CR treats multi-value parameters like an array.  It's smart enough so that if you use ({field} like {?param}), it does the Like comparison on each value in the parameter (ie. each element in the array).

 As you saw, "*" & {?param} & "*" gives you an error.

 "*" + {?param} + "*" does not give you an error, but what it's actually doing is adding "*" (twice) as another element in the array.  For example, if you entered "A" and "B", you'd end up with "*", "A", "B" and "*".  So, one (or, I guess, two) of the tests ends up being ({field} like "*"), which will match everything.  IOW, the report will always include all values for {field}, regardless of what you enter for the parameter.

 If you need the multiple values, the simplest thing may be to include the wildcard on the parameter values.  IOW, instead of "A", "B" and "C", you'd enter "*A*", "*B*" and "*C*".

 Another option is to use a formula to go through each value in the parameter, add the "*" wildcards to it, and check for a match.  That's what mlmcc's formula is doing.

 James
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
For a multi-value parameter, please use and award the points to mlmcc's solution.
Genius123Author Commented:
Ido is correct.  mlmcc's solution worked.  Can anyone tell me how to re-award points to mlmcc?  I can't figure it out on this newly designed site.
James0628Commented:
FWIW, if the parameter could have more than a few values, I would tweak mlmcc's formula a bit.  The idea is to exit the For loop as soon as you find a match, because, once you find a match, there's no point in checking the remaining parameter values.  If you only have a few parameter values, then doing a couple of extra checks probably won't make any real difference, but if you could have a lot of parameter values, skipping the unnecessary checks might save some time, especially since you're doing a Like string comparison.

Local NumberVar Index;
Local BooleanVar Include := False;

For Index := 1 to UBound({?My Parameter}) do
(
    Include := Include OR {Product} LIKE '*' & {?My Parameter}[Index] & '*';
    if Include then
        exit for;
);
Include

Open in new window


 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.