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

kstahlAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
Is that all there is in the memo field?

You can use the SPLIT function to put the individual lines into an array.  Array is 1 based

In a formula SplitAddress
WhilePrintingRecords;
Global StringVar Array AddressLines;
AddressLines := Split({YourMemoField},chr(13));
""

You can then use the array in other formulas to get at the individual lines say to display 1 line
ExecuteAfter({@SplitAddress});
Global StringVar Array AddressLines;
AddressLines[1]

To check how many lines
ExecuteAfter({@SplitAddress});
Global StringVar Array AddressLines;
UBound(AddressLines)

mlmcc
0
 
mlmccCommented:
How is the data formatted so it can be separated?

mlmcc
0
 
kstahlAuthor Commented:
It looks like it has carriage returns after each line
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
Is there more to the address like city and state?

mlmcc
0
 
kstahlAuthor Commented:
No, City and State are in different fields
0
 
kstahlAuthor Commented:
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.
0
 
mlmccCommented:
Sorry.  It should be

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

mlmcc

0
 
kstahlAuthor Commented:
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.
0
 
kstahlAuthor Commented:
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];
0
 
mlmccCommented:
If you want to display it all why not just put the field on the report?

mlmcc
0
 
kstahlAuthor Commented:
I have to segregate the address into separate fields to import correctly into another system.
0
 
mlmccConnect With a Mentor Commented:
You display this way

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

mlmcc
0
 
kstahlAuthor Commented:
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]
0
 
mlmccCommented:
Where did you put the formulas?

mlmcc
0
 
kstahlAuthor Commented:
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.
0
 
mlmccCommented:
Can you examine the memo fields to see what is really separating the "lines"

mlmcc
0
 
James0628Connect With a Mentor Commented:
If you put SplitAddress in the group header and there are multiple detail records in the group, then the AddressLines array will only get the value from CRP2supp.fmstreet from the first record in the group.  That doesn't explain why the formulas wouldn't work when SplitAddress was in a detail section.  I just wanted to make sure that you didn't leave SplitAddress in the group header, unless you only want the value from the first record in the group.

 Apart from that, the formulas seem OK.  I might suspect a problem with the variable, but if you're getting a value in AddressLines [ 1 ], then the variable is being set.

 Have you set the format for the field with the AddressLines [ 1 ] formula to "Can Grow"?  If the address field is not being split, the whole address should be in AddressLines [ 1 ], and you should be able to see it, if the field is set to "Can grow".

 If the address is not being split, there's a good chance that the character separating the lines is a Line Feed, instead of Carriage Return.  In SplitAddress, try changing Chr (13) to Chr (10).

 If that doesn't work, and assuming that the character that's used to split the lines is some "non-displayable" character (not a letter, number, punctuation, etc.), then you can try the following formula to see what those characters are:

Local NumberVar cnt;
Local StringVar chars;

if Length ({CRP2supp.fmstreet}) > 1 then
  for cnt := 1 to Length ({CRP2supp.fmstreet}) do
    if not ({CRP2supp.fmstreet} [ cnt ] in " " to "~") then
      chars := chars + CStr (cnt, "#") + " - " +
       CStr (AscW ({CRP2supp.fmstreet} [ cnt ]), "000") + ", ";

chars


 That scans the field looking for any characters that are not between " " (a space) and "~", which includes all of the "normal" characters (letters, numbers, etc.) in your basic ASCII character set.  For any characters not in that range, it outputs the character position in the field and the ASCII value.

 Even if changing Chr (13) to Chr (10) appears to solve the problem, I'd suggest trying that formula.  One other reason that using Chr (13) might appear to not be working would be if the memo field actually used both Carriage Return and Line Feed to separate the lines (the standard DOS text format).  If that was the case and you split the field at Chr (13), the lines after the first one would begin with Chr (10) - a Line Feed - and would appear to be empty, unless you set the "Can Grow" format option on those fields.  If you did, you'd see a blank line, and then the address line below that.  If you split the field at Chr (10), the split would appear to be working (you'd see the address lines), but the first lines would actually have a Carriage Return (Chr (13)) at the end, which you may not want, especially if you're trying to export the data for import into something else.  The formula above would show you if the field includes both Carriage Return and Line Feed.

 James
0
 
James0628Commented:
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
0
 
kstahlAuthor Commented:
Changing to CHR(10) worked!   Thank you!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.