Searching a multi-select field

[ltidquist]

I have a field that can have multiple values based on a lookup table. As the codes are selected they will be stored in different orders on different records -- ie Record A can have a value of 01,01.140,02 and Record B can have a value of 09,03,01.140.

Because several of the codes start with '01' I'm having trouble getting only records with 01. Those that have values like 01.140 are also being selected.

Does this make a bit of sense? Any ideas?

Thanks in advance.

 

[Charliy]

You could use INSTR and append a comma to the end of the code you're searching for.

That leaves 2 problems:
1 It won't find your code if it's the last one in the string - you can get around that by doing an additional search for RIGHT({string},2) in this case.

2. You'd still get a false positive for codes like 123.01.

 

[lbass]

I can't tell what you are trying to select. Can you show more sample data and then tell us what records you would expect to be returned if you searched for a specific value?

-LB

 

[ltidquist]

Here is a sample of the available values:

01
01.000
01.1
01.120
01.130
01.140
01.150
01.2
02
03
04

The problem is that records can have multiple values from this list in the field so if I want any records that have the value of 01.1 I would also get those with 01.120, 01.130, 01.140 and 01.150.

Does that help?
Thanks.

 

[lbass]

Okay, but what do you want to do with the record once you know it contains a specific value? Are you trying to look up a description in another table? Since there are multiple values, are you hoping to extract multiple descriptions? Or are you trying to use this value in a selection formula?

-LB

 

[ltidquist]

Oh I understand your question... I just need to select the records that meet my selection criteria.

Thanks.

 

[lbass]

This won't be efficient, but should work:

stringvar array x := split({table.string},",");
numbervar i;
numbervar j := ubound(x);
stringvar y;
for i := 1 to j do(
if ".01" = x then
y := y + {table.string}+","
);
{table.string} in y

You can replace the ".01" with a parameter.

-LB

 

[ltidquist]

Thanks for that but I'm afraid I don't know what to do with the solution you provided me. Do I make this a formula field? This is a bit more advanced than my current Crystal knowledge.

Thanks again.
Laura

 

[lbass]

Go to report->selection formula->record and place it there.

-LB

 

[ltidquist]

Ok, one more question. I've modified the selection to look like this:

stringvar array x := split({Name_All.CSI_CODE},",");
numbervar i;
numbervar j := ubound(x);
stringvar y;
for i := 1 to j do(
if " {?CSI CODE}" = x then
y := y + {Name_All.CSI_CODE}+","
);
{Name_All.CSI_CODE} in y

But it does not prompt me for any codes and returns no results when I run it.

Thanks again.

 

[lbass]

Remove the qutoes:

stringvar array x := split({Name_All.CSI_CODE},",");
numbervar i;
numbervar j := ubound(x);
stringvar y;
for i := 1 to j do(
if {?CSI CODE} = x then
y := y + {Name_All.CSI_CODE}+","
);
{Name_All.CSI_CODE} in y

-LB