Looping through Multi-Value Parameters in Selection Formulas

[CLRMTMN]

I am working on a report and have found myself stuck at this one spot for awhile now.

I am writing a report that recieves a multiple string parameters as input, and then needs to use a LIKE statement to see if there is records that meet the criteria. Just a few issues:

Here's what I have:

numbervar i = {#myparamcount};
While i < 0 DO
(
     {DBTable.Field} LIKE "*"+{?MVParam}[i]+"*";
     i = i - 1;
);

I have tried to use an IN statement as well. Having *'s is important, because the user will input a unique string.

Any ideas on how to make this work? I've seen some post on using a JOIN with MV parameters, but I'm not sure that's what I am looking for.

 

[smcnulty2000]

I've used multiple string parameters with both a Like and an equal statement like this:


if {GISData.Tag_Business} = {?Area To List}
then 1 else 0


And it returns the value of 1 if any or multiple of my values in the parameter are equal. I have used this in both a formula and a record selection statement. When I did a look at the SQL query, the parameter had become an OR statement with all the values I had put in present.

This also works with an array. That is, you could drop an array in the above statement in place of the parameter.

So it seems to me that what you might try (and I don't know if this would work) is to extract each value into a non-array string variable, attach the asterisks, and dump it into an array. You could then see if a like statement works


{DBTable.Field} LIKE myarray


The only mystery here is whether the asterisks will survive all of this manipulation.

Of course, you could just have the user input the asterisks themselves. But that's the easy way out.

Scottm.

 

[synapsevampire]

Try something like:

Numbervar x;
stringvar MyLikeData;
for x := 1 to ubound({?multistringparm}) do(
MyLikeData:= MyLikeData+"%"+{?multistringparm}[x]+"%,"
);
left(MyLikeData,len(MyLikeData)-1)

Note that I use the percent sign, not the asterisk because this is passed to the database, and needs the wildcard for your database, which of course you didn't share for some reason, nor the version of Crystal, both of which can impact the solution.

The trick will be in getting the sql to pass to the database, which the above may not do, some reformatting adn trickery are probably still in order.

-k