• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • Last Modified:

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
0
sthomas76
Asked:
sthomas76
  • 6
  • 6
  • 2
1 Solution
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now