Splitting Text 2

[mtownbound]

I have a memo field that contains multiple lines of text that I need to split. I understand the "string" [3 to 5] method but should I use stringvar to pull multiple lines? Here's a sample of the memo field:

Date: 20120509
Location: New York, NY
Institution: Wells Fargo

I need to be able to extract the values for each line.


Thanks!!!

 

[Charliy]

If it's already breaking into lines like that when you print it, then you can look for chr(10) or chr(13) as breakpoints.

 

[mtownbound]

But if I want to capture "New York, NY" how do I tell it to trim at the end of the row that starts with "Location:"?

 

[CoSpringsGuy]

Are date: Location: and Intstitution: the only three items always found in the memo field and are they always there?
Do you need these returned as seperate fields?

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[mtownbound]

Yes, the field labels will always be the same label and length, but the position may not be the same because there's a description field before those fields. For example:

Row_ID: 10456
Description: This is just a test of the field
Date: 20120509
Location: New York, NY
Institution: Wells Fargo

Row_ID: 10457
Description: This is just a test of the information that was previously archived but has been restored.
Date: 20120510
Location: Miami, FL
Institution: Bank of America

 

[CoSpringsGuy]

but you only want those three items removed (without labels) from the memo field? and you do want them as seperate fields?

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[mtownbound]

That's exactly what I need.

 

[CoSpringsGuy]

k you will need to find out if the break is a chr(10) or chr(13) so try this:

mDate formula

local stringvar array a := split({yourmemofield},chr(13));
local stringvar b;
b := replace(a[3],"Date: ","");
local datevar mDate :=
date(
tonumber(mid(b,1,4)),
tonumber(mid(b,5,2)),
tonumber(mid(b,7,2))
);

i took the liberty of converting the date to an actual date field as well. If that doesnt work change the CHR(13) in the split function to chr(10)

once that works for you

mLocation formula
local stringvar array a := split({@test},chr(13));
local stringvar mLocation := replace(a[4],"Location: ","");

mInstitition formula

local stringvar array a := split({@test},chr(13));
local stringvar mInstitution := replace(a[5],"Institution: ","");




_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

change {@test} in the last two formulas to your memfield from database...

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[mtownbound]

The formula is okay, but when I preview, I'm getting the following error:

"A subscript must be between 1 and the size of the array."

 

[mtownbound]

CoSprings, I need to update my example. It looked right in the window, but not in the post:

Row_ID: 10456
Description: This is just a test of the field
Date: 20120509
Location: New York, NY
Institution: Wells Fargo

Row_ID: 10457
Description: This is just a test of the information that was
previously archived but has been restored.
Date: 20120510
Location: Miami, FL
Institution: Bank of America

Notice how the multi-line Description changes the row of Date, Location, etc.

 

[CoSpringsGuy]

ahhh ok that sucks! lol .. ok i am in a meeting but we can still fix that.. I will get back to you .. did you determine if it was chr(10) or chr(13)?

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[mtownbound]

It's a chr(13).

 

[CoSpringsGuy]

date formula - let me know how that works for you and I will do the rest .. boring meeting but I have to pretend Im paying attention

local stringvar a := replace({youmemofield},chr(13),"");
local stringvar b := mid(a,instr(a,"Date:")+6,8);
local datevar mDate :=
date(
tonumber(mid (b,1,4)),
tonumber(mid (b,5,2)),
tonumber(mid (b,7,2))
);
mDate

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

Location formula
local stringvar a := replace({yourfield},chr(13),"");
local numbervar b := instr(a,"Institution:");
local numbervar c := instr(a,"Location:")+10;
local stringvar mLocation := mid(a,c,(b-c));
mLocation

Institution Formula
local stringvar a := replace({yourfield},chr(13),"");
local numbervar b := instr(a,"Institution:")+13;
local stringvar mLocation := mid(a,b);
mLocation

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[mtownbound]

They work, but they're also pulling everything after the desired field. For example, @Location returns:

Miami, FL
Institution: Bank of America

 

[CoSpringsGuy]

did you copy my formula or retype. The only way I see that happening is if you didnt spell Institution in the line "local numbervar b := instr(a,"Institution:");" exactly as it would appear in the memo field

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[mtownbound]

I copied and changed the fields, but I'm still testing.

 

[mtownbound]

I got it!! Thanks!!!!

 

[lbass]

stringvar p := {table.memo};
stringvar array q := split(p,chr(13));
mid(q[4],instr(q[4],": ")+2); //4th line

-LB