Compare multi parameters against string field 1

[AndRos11]

I have a parameter field ({?Training Code} that can accept multiple values like AA, E, FS, F1, Z. What I need to do is have the user selected parameters compared to a string database field {conthist.rectype}. The part I'm having problems with is the database field contains extra information. For example... "LC Survey done ;* AA, E, F1" What I'm searching against will always be after the ";*" part of the database field. So if a search is done for AA, E, and Z then this example record would be excluded but if a search was done for AA and E the record would be selected. Below is what I have for a formuala

numbervar i;
numbervar j:= ubound({?Training Code});
stringvar x;

for i:=1 to j do
(
if {?Training Code} in {conthist.rectype} then
x:= x + {conthist.rectype}+","
);
{conthist.rectype} in x

 

[lbass]

So the rule is that ALL selected parameter values must be present in the record, although there could be other elements also present?

Does the field ALWAYS contain ";*"?

Are the values ALWAYS formatted with a comma and one space between?

Are there any cases where codes overlap, e.g., A and AA?

-LB

 

[AndRos11]

Yes, all the selected parameter values must be present in the record and it is ok if additional elements are present.

Yes, there is always ";*" to mark the beginning of the Training Codes.

yes, there is always a comma space between the codes.

Here are all the possible codes: AA, E, FS, F1, F3, IT, L, ME, ST, CT, SF, SM, WP, Z.

Thanks for your help....

 

[lbass]

stringvar array y := split(split({conthist.rectype},";*")[2],", ");
numbervar k;
numbervar m := ubound;
numbervar i;
numbervar j:= ubound({?Training Code});
stringvar x := "";
stringvar z := "";
for i :=1 to j do (
for k := 1 to m do(
if {?Training Code} = trim(y[k]) and
not({conthist.rectype} in x) then (
x := x + {@field}+","
);
if {?Training Code} = trim(y[k]) then
z := z + {?Training Code}+","
))
;
{conthist.rectype} in x and
ubound(split(left(z,len(z)-1),","))=j

-LB

 

[AndRos11]

Thanks for your help lbass,

I have two more questions. One I wasn't completely sure what about the @field in the following line: x := x + {@field}+",". Should I put the database field there? I notice that my SQL query now doesn't have the "Where" statement. Is there anything I can do to get the where statement issued so it limits the records? Again thanks for all your help...

 

[lbass]

Sorry, I forgot to replace {@field} with the {conthist.rectype} field.

No, there is no way to get this to pass to the SQL--because the database is designed poorly, as you can see. You shouldn't have to do this kind of thing to get basic data. Note that the selection IS being applied, even though you don't see it in your where clause--but it's being applied locally, not on the server, i.e., all records meeting your other criteria are returned locally and then these criteria filter the dataset.

-LB