asked on

Crystal Reports Extract lines from memo field

I'm sure this is have been answered somewhere, but I have a memo field in SQL.

I need to create the correct formulas to extract up to four lines from it in Crystal.

This is an address field and does not always have the same number of lines:

For example, this would be one memo field in my SQL database:

ATTN: Accounts Payable
Kevin Thomas
P.O. Box 127821
Suite 212

I need this to be in four separate fields in my report

Mike McCracken

How is the data formatted so it can be separated?

mlmcc

kstahl

ASKER

It looks like it has carriage returns after each line

Mike McCracken

Is there more to the address like city and state?

mlmcc

kstahl

ASKER

No, City and State are in different fields

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

kstahl

ASKER

Okay, I created a formula field called SplitAddress in my report with this entry:

WhilePrintingRecords;
Global StringVar Array AddressLines;
AddressLines := Split({CRP2supp.fmstreet},chr(13));
""

I created another formula field called Address in my report with this entry
ExecuteAfter({@SplitAddress});
Global StringVar Array AddressLines;
AddressLines[1];

It gives me an error when saving the formula field above saying "The remaining text does not appear to be part of the formula."

Sorry, never used an array before in a report.

Mike McCracken

Sorry. It should be

EvaluateAfter({@SplitAddress});
Global StringVar Array AddressLines;
AddressLines[1];

mlmcc

kstahl

ASKER

Okay, is there any way to get all the AddressLines to display regardless of how many are in the array?

Some addresses have 1 line while others have 4. I can't display all four fields for every record.

AddressLines[2] will error out if only 1 is in the array.

kstahl

ASKER

Actually even when I segregate for one address, this isn't working.

I have an address with three lines in the memo field. The first line will display.

These two show no data.
EvaluateAfter({@SplitAddress});
Global StringVar Array AddressLines;
AddressLines[2];

EvaluateAfter({@SplitAddress});
Global StringVar Array AddressLines;
AddressLines[3];

Mike McCracken

If you want to display it all why not just put the field on the report?

mlmcc

kstahl

ASKER

I have to segregate the address into separate fields to import correctly into another system.

SOLUTION

Mike McCracken

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

kstahl

ASKER

It's still not displaying the second and third record.

The count will say 2 lines or 3 lines correctly, but only AddressLines[1] shows the first line of data

EvaluateAfter({@SplitAddress});
Global StringVar Array AddressLines;
If UBound(AddressLines) >= 2 then
AddressLines[2]

Mike McCracken

Where did you put the formulas?

mlmcc

kstahl

ASKER

I've tried putting them all in the details segment.
I've also tried putting the SplitAddress in the group header with the formulas in the detail segment.

Mike McCracken

Can you examine the memo fields to see what is really separating the "lines"

mlmcc

SOLUTION

James0628

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

James0628

Oh, I forgot to mention, if you use that formula that I posted, when you put it on the report, set the field to "Can Grow". Depending on how many "non-displayable" characters there are, the string it produces could get pretty long, so set "Can Grow", just to make sure that you can see everything.

James

kstahl

ASKER

Changing to CHR(10) worked! Thank you!

James0628

You're welcome. Glad I could help.

James