Need to extract multiple data from a field

[sassyx]

Crystal newbie needs assistance. I have a field example customer name that may contain from 0-9 names. I have a formula that contains all the customer names which total about 50. The customer name also has a state assigned to it. I need the formula to extract each name and assign the state. Ex:
CustName - John, Joe, Debbie, Mike, Lisa
State - Michigan
on the next read of the record the contents may be
CustName - Joe, Lisa
State - Ohio
CustName and state changes and I need to list each. Thanks for any help.

 

[synapsevampire]

I'm trying to sort out your logic:

Do you have 2 fields and 1 formula, 2 formulas and 1 field, or?

Please post example data (with field names) and an example of what's in this formula (and it's name so that your explanation describes it as unique, you use cutomer name above for the field and formula, I think...), or formulas, I can't tell if you have a customer and state formula, or...

Then post how you want the data to look.

-k

 

[sassyx]

Hope this help clear what I am trying to do. I have a database with 2 tables. Custname and state. The users can enter as many names as they like in the custname field, separated by commas and assign a state to the custname field. I have a formula for custname that is supposed to search the string entered and assign the full name. Example if custname contains Joe, John then the formula assigns Joe Smith as name and assigns the state. The problem is it will only assign this to one person based on which one it finds first in the custname formula and ignores the remainder of custname. So Joe will become Joe Smith with State of Ohio and John is ignored. I guess I am trying to figure out how to make it loop through custname field.
Formula:
If {Table1.Custname} like ["*Joe*", "*joe*"] then "Smith, Joe" else
If {Table1.Custname} like ["*John*", "*john*"] then "Doe, John" else

 

[synapsevampire]

I think I grasp it now, I'll give you the more elegant solution, you might also use a single loop and a case/if statement, but this is much more fun:

whileprintingrecords;
stringvar array MyArray := split("joe, bob, john","," //replace this split function with your table.field

stringvar array MyLookupArray := split("joe smith,bob jones,john jenkins","," //place your lookup names in here, do not place spaces before or after the commas

stringvar array MyReturnArray;
numbervar x;
numbervar y;
redim MyReturnArray[ubound(MyArray)];
for x := 1 to ubound(MyArray) do(
for y := 1 to ubound(MyLookupArray) do(
if ltrim(rtrim(MyArray[x])) = left(MyLookupArray[y],instr(MyLookupArray[y]," &quot-1) then
MyReturnArray[x] := MyLookupArray[y]
);
);
join(MyReturnArray,",&quot

You probably know that this should be done by using another table as a lookup as opposed to hard coding this into a report.

-k

 

[synapsevampire]

To clarify:

This:

stringvar array MyArray := split("joe, bob, john","," //replace this split function with your table.field

Should be:

stringvar array MyArray := split({table.field},"," //replace this split function with your table.field

-k

 

[sassyx]

When using the formula I receive an error:
"String length is less than 0 or not an integer" and it highlights: left(MyLookupArray[y],instr(MyLookupArray[y]," &quot-1)

I think my problem exists with how the data is entered in the custname field. I should have included earlier as I am sure this is relevant. In the custname field here are sample entries:

Custname = Overcharge - John, Joe, Mike
State = Michigan
Custname = Late Payment - Lisa, Don, Joe
State = Michigan
Custname = Change of Address - John
State = Ohio


Formula:
whileprintingrecords;
stringvar array MyArray := split({RFInfo.Customer},","
//replace this split function with your table.field

stringvar array MyLookupArray := split({@CustName}," "
//place your lookup names in here, do not place spaces before or after the commas

stringvar array MyReturnArray;
numbervar x;
numbervar y;
redim MyReturnArray[ubound(MyArray)];
for x := 1 to ubound(MyArray) do(
for y := 1 to ubound(MyLookupArray) do(
if ltrim(rtrim(MyArray[x])) = left(MyLookupArray[y],instr(MyLookupArray[y]," &quot-1) then
MyReturnArray[x] := MyLookupArray[y]
);
);
join(MyReturnArray,",&quot

 

[synapsevampire]

Replace MyArray code with:

stringvar MyTableMyField := trim(mid({RFInfo.Customer}, instr({RFInfo.Customer},"-&quot+1));

stringvar array MyArray := split(MyTableMyField,"," //replace this split function with your table.field

You may need to do some data integrity checks too, relying upon users to place in text with a dash and commas and expecting 100% data integrity is very optimistic.

-k

 

[sassyx]

That did it. Thanks again for your assistance.