Reformat a list of names 2

[smitty691]

Hi

My data table has a field for names of honorees. In the data source table the names in that field are ...

Smith, John; Smith, Jane; Roberts, Sam; Roberts, Karen

What function do I use so that CR XI will display on my report...

John Smith, Jane Smith, Sam Roberts, Karen Roberts

I'm experimenting with split([field], ";") right now but I'm getting errors say the array must be subscripted. I'll keep working with it but was hoping someone has some experience with this.

 

[kray4660]

So you want to replace the ';' with commas and the commas with spaces (after you switch the first and last names)? If this is so. I think it would be a three part process. First (probably with an array). Go through and put each name (last name, first name) in an array. Then go through the array and and switch the first and last names. Lastly, recombine the names with the comma (this is where the join should work). Unfortunately I do not currently have the time to work up code to do this.

 

[smitty691]

Thanks for your input.

What you said about the 3-step breakdown is helpful.

 

[pmax9999]

Unless I have misunderstood what you are trying to achieve here, and assuming the field data is exactly as you have shown, this should work:

WhilePrintingRecords;
Local NumberVar i;
Local StringVar R;

For i:= 1 to Ubound(Split({Table.Field}, '; ')) do

R := R + Split(Split({Table.Field}, '; ')[i], ', ')[2] + ' ' + Split(Split({Table.Field}, '; ')[i], ', ')[1] + '; ';

R := Left(R, Len(R) - 2)

Hope this helps.

Cheers
Pete

 

[smitty691]

Wow pmax9999, it looks like that is going to work. Thanks!

Now I have to figure out what you did so I can help myself next time.

 

[pmax9999]

Thanks smitty691. Glad it helped.

Ubound(Split({Table.Field}, '; ')) determines the number of names in the list - in your example 4.

It then loops through each of those 4 names (as separated by the "; ") selects the first name (ie, the second component of the name - as separated by the ", ") adds a space, then the surname, then a semi-colon and another space.

When it finished looping through initial string the result (in the formula it is contained within the variable "R") is a list of names as required, but has an additional "; " at the end. The final line of the formula removes those last 2 characters.

Hope this helps. I'd suggest you have a play with the Split function. It can be a very useful function.


Cheers
Pete

 

[kray4660]

I forget about using ubound.