Select Expert use "Like" formula with multiple values parameter

I have one parameter have to setup for use to enter multiple values. In the Select Expert, I am using "Like" formula below and keep getting the error message.

{Command.Code} like "*" & {?CodeSet} & "*"

How can I make this like formula works with parameter with multiple values?

Thanks,
rowfeiAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
What is the error message?

You can try using this as the formula (assumes that is the only criteria

Local NumberVar Index;
Local BooleanVar bResult := False;

For Index := 1 to UBound({?CodeSet}) do
    bResult := bResult OR ({Command.Code} like "*" & {?CodeSet}[Index] & "*";
bResult

Open in new window

0
 
James0628Connect With a Mentor Commented:
The error is because CR treats a multi-value parameter like an array, so you're trying to concatenate a string and an array, and CR won't do that.  It's the same as trying something like "*" & [ "1", "2", "3" ] & "*" (assuming that ?CodeSet is a string parameter).

 From a test that I just did, you can use Like with a multi-value parameter (or array) that includes values with wildcards.  For example, ( {Command.Code} like [ "*a*", "*b*" ] ) should include any records where Code includes "a" or "b".  But you have to include the wildcards on each value in the parameter, not just add them to the beginning and end of the list of values in the parameter, which is what you were trying to do.

 If ?CodeSet is a string parameter, then the simplest answer would be to have the user enter the wildcards.  For example, they enter "*a*", instead of just "a" (without the quotes).

 Another option would be to have a formula copy the values from the parameter to an array variable (because you can't change the values in a parameter), add the "*"s to each value in the array, and then use ( {Command.Code} like your_array_variable ).

 Or you can add the "*"s to each value and compare them individually, as mlmcc suggested.

 I don't know if either of the last two options has any real advantage over the other as far as performance goes.  Either way, you're looping through the parameter values and adding the "*"s to each value.

 James
0
 
rowfeiAuthor Commented:
Thanks, all.

mlmcc- I do have another criteria in the formula (works fine since this parameter is not multi-value). Please see it below.

{Command.Code1} Like "*" & {?CodeSet1} & "*"

How to combine it with your codes below together?

Local NumberVar Index;
Local BooleanVar bResult := False;

For Index := 1 to UBound({?CodeSet}) do
    bResult := bResult OR ({Command.Code} like "*" & {?CodeSet}[Index] & "*";
bResult

Thanks again!!!
0
 
mlmccCommented:
Not sure you can directly.

Try this


{Command.Code1} Like "*" & {?CodeSet1} & "*"
AND
{@yourFormula}

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.