Formula to format a memo field in crystal reports

I have a memo field that's data is formated like this:

Thomas, John (ACE Contruction), Smith, Mike (ABC Electric, Inc.), Miller, Bob (Johnston, Steve W. & Company)

I would like it to be formated like this:

John Thomas, ACE Construction; Mike Smith, ABC Electric Inc.; Bob Miller, Steve W. Johnston & Company
sthomas76Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
There is no easy wat to do this.

Can you guarantee the data looks like

LastName, FirstName (Company), repeat
Any known upper limit to the number of name sets

I'll try to build a formula to get what you need.

mlmcc
0
James0628Commented:
If the format is consistent, most of it might be doable, but I think changing the order in company names (eg. "Johnston, Steve W. & Company" becomes "Steve W. Johnston & Company") is going to be a problem.  Even if you can handle a name like that (and I have my doubts about that), some company names with a "," should not be changed.  Unless you can identify which company names should _not_ be rearranged, you'll end up with things like "Weebles, Inc." becoming "Inc. Weebles".

 James
0
mlmccCommented:
Good catch James.  I missed that one when I looked at the output.  I fiugred I could do the other but the names in the ( ) will have to re,ain as is.

mlmcc
0
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

mlmccCommented:
Here is a formula to do the first part

'Thomas, John (ACE Contruction), Smith, Mike (ABC Electric, Inc.), Miller, Bob (Johnston, Steve W. & Company)'

is

John Thomas, ACE Contruction; Mike Smith, ABC Electric, Inc., Bob Miller, Johnston, Steve W. & Company

To get the last one correct will require a lot of work and analysis of the field since the easy way would flip ABC Electric into Inc ABC Electric

mlmcc
WhilePrintingRecords;
Local StringVar DatabaseField := {YourDatabaseFIeld};
Local StringVar Array CompanyArray;
Local StringVar Array IndividualCompanyName;
Local StringVar Array IndividualNames;
 
Local StringVar ResultString := '';
 
Local NumberVar i;
 
CompanyArray := Split(DatabaseField,'),');
For i := 1 to UBound(CompanyArray) do
(
    If ResultString <> '' then
        ResultString := ResultString & '; ';
    IndividualCompanyName := Split(CompanyArray[i],'(');
    IndividualNames := Split(IndividualCompanyName[1],',');
    ResultString := ResultString & IndividualNames[2] & ' ' & IndividualNames[1] & ', ' & IndividualCompanyName[2];
);
 
ResultString := Left(ResultString,Len(ResultString) - 1);
ResultString

Open in new window

0
James0628Commented:
Yeah, I thought you might have missed the company part.  :-)  It does really complicate things.

 James
0
sthomas76Author Commented:
I added that fomula and did a check on it after adding my database field and I get. "The remaining text does not appear to be part of the formula."  It highlighted everything after StringVar on the 2nd row.  So it highlighted DatabaseField and everything down.
0
sthomas76Author Commented:
Question when in crystall and looking in the field explorer I can see my field I want but I can browse data. And when I go in to database expert and go to links I  see my database but the field is not in there.  Is there a reason for this.
0
mlmccCommented:
Did you replace {YOurDatabaseField} with the real field for the report?

mlmcc
0
sthomas76Author Commented:
Yes I did
0
sthomas76Author Commented:
Ok it is working I had it on basic syntax.  It looks like there is a space before the first name on the first contact and two spaces between the frist and last name on the records after the first contact
0
sthomas76Author Commented:
Is there any way to have each person and company on one line
John Thomas, ACE Construction
Mike Smith, ABC Electric Inc.
Bob Miller, Steve W. Johnston & Company
0
mlmccCommented:
Sure, add chr(13) after each line

mlmcc
0
sthomas76Author Commented:
Everything is working, but it look like this

John Thomas, ACE Construction
Mike  Smith, ABC Electric, Inc.
Bob  Miller, Johnston, Steve W. & Company

Do you know why there would be two spaces behind Mike and Bob?
0
mlmccCommented:
I don't know why it is picking up the spaces but they are in the names when they get split out.

Simply TRIM the fields before they are concatenated together.

Corrected formula is below

mlmcc
WhilePrintingRecords;
Local StringVar DatabaseField := 'Thomas, John (ACE Contruction), Smith, Mike (ABC Electric, Inc.), Miller, Bob (Johnston, Steve W. & Company)';
Local StringVar Array CompanyArray;
Local StringVar Array IndividualCompanyName;
Local StringVar Array IndividualNames;
 
Local StringVar ResultString := '';
 
Local NumberVar i;
 
CompanyArray := Split(DatabaseField,'),');
For i := 1 to UBound(CompanyArray) do
(
    If ResultString <> '' then
        ResultString := ResultString & Chr(13);
    IndividualCompanyName := Split(CompanyArray[i],'(');
    IndividualNames := Split(IndividualCompanyName[1],',');
    ResultString := ResultString & Trim(IndividualNames[2]) & ' ' & Trim(IndividualNames[1]) & ', ' & Trim(IndividualCompanyName[2]);
);
 
ResultString := Left(ResultString,Len(ResultString) - 1);
ResultString

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.