This array must be subscripted 1

[crumpm]

I have a report with 2 parameters, and I want to enter multiple values for both, and use both to search in string fields.

e.g. {?Name} = "Brown","Green","White"
{?Postcode} = "SO9","SO6","SO4"


{?Name} in {Client.Name} and
{?Postcode} in {Client.Postcode}

but i can't find any fomula that will allow multiple parameter values for this search.

Any help greatly appreciated

 

[Madawc]

I don't think there is one. Try three name parameters and three postcode parameters.

It's possible that if you entered the three values in a single parameter, this would work. Never tried it.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[crumpm]

I'm using Crystal 2008 on a windows XP machine, attached to an SQL database.

Thanks

 

[lbass]

If you just want to search for each of those names in each of those postcodes, then you should just set it up like this:

{client.name} = {?Name} and
{client.postcode} = {?PostCode}

-LB

 

[crumpm]

I probably didn't explain that very well, sorry.

The inputs are coming from an Excel spreadsheet in format

"Mr John Smith"
"Mr Bob Jones"

so I extract just the "Smith" and "Jones" element from each record to populate my {?Client Name} parameter, but the field in the data base looks like

"Jones, Bob Mr"

which is why I need the 'in' formula.

Thanks for helping with this.

 

[lbass]

You could do it like this:

left({client.name},instr({client.name},",")-1) = {?ClientName}

Not sure how the postcode displays compared to its parameter.

-LB

 

[crumpm]

I'm sorry again, my example was too simplified.

The Client.Name data base is large and contains differeing entries.

Smith John Mr
or
John Smith Motors


I need to find any record where the entered parameter value is contained anywhere in the Client.Name

I don't even know if this is possible!

Thanks again

 

[lbass]

Okay, try this:

whilereadingrecords;
numbervar i;
numbervar j := ubound({?ClientName});
stringvar x;
for i := 1 to j do(
if {client.name} like "*"+{?ClientName}+"*" and
instr(x,{client.name}) = 0 then
x := x + {client.name}
);
{client.name} in x

-LB

 

[crumpm]

This is really helpful.

It has thrown out a new error, which in a simple formula I could fix, but I can't find the right place to put a limiter in this one.

"A string can be at most 65534 characters long"

Thanks

 

[lbass]

Another approach would be to limit the number of names that can be selected, e.g., six, and then set up the select statement like this:

(
{client.name} like "*"+{?ClientName}[1]+"*" or
(
ubound({?Clientname}) >= 2 and
{client.name} like "*"+{?ClientName}[2]+"*"
) or
(
ubound({?Clientname}) >= 3 and
{client.name} like "*"+{?ClientName}[3]+"*"
) //etc.
)

You would have to use the prompt text to tell users to select up to N number of names.

-LB

 

[gbrudno]

WhilePrintingRecords;
stringvar array test:= {?ClientName};
numbervar i;
stringvar str; //:=test[1];
for i:=1 to count({?ClientNames}) do
(
if i<>count({?ClientName}) then
str:=str&test&","
else
str:=str&test

//i:=i+1;
);
str;

 

[crumpm]

Hi gbrudno

I've tried your formula, but unfortunately it comes up with the same string length error.

I think the problem is that I just have too many names, my current list has 170 names!


For now I've regressed to the manual entry method.
Thanks for your help