get certain parts from memo field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15142
Printed Date: 23 May 2024 at 8:14am
Topic: get certain parts from memo field
Posted By: campagnolo_1
Subject: get certain parts from memo field
Date Posted: 13 Dec 2011 at 5:07am
|
Greetings,
I have a bit of an issue creating a formula that will give me certain parts of a memo field. HTe field displays a configuration list for a garment, but I don't want all the quantities and part numbers. All I want is the description - basically everything after the "--" in each line. I have done something simillar in Excel but can't for the life of me figure out how to do it in Crystal.
Any ideas?
Thanks in advance and below is a sample of the memo field.
Chris
Configuration List
1.000 EA (0.00) YCUSTOMCOLOR --Custom Color Choice
1.000 EA (100.00) YGFER-RELSOCMD --GFER with MD GTX Socks, SM-XL Suits
1.000 EA (0.00) YLATEXNECK4 --Large latex neck gasket (ZCUFNEK4BK)
1.000 EA (0.00) YGTXWRST3-4W4 --Gore-Tex Inner Wrist Cuff Gasket Prep-one PAIR-MD-LG-WL
0.000 EA (0.00) NOALTERLEGS --No Leg Alteration Chosen
0.000 EA (0.00) NOALTERSLEEVE --No Sleeve Alteration Chosen
0.000 EA (0.00) NOALTERRISE --No Rise Alteration Chosen
0.000 EA (0.00) NOALTERTORSO --No Torso Alteration Chosen
1.000 EA (0.00) NOZIPFLAP --NO Zipper Flap Option Chosen
0.000 EA (0.00) NOSUSPENDERS --No Suspenders Chosen
0.000 EA (0.00) NOREFTAPE --No Reflective Tape Option Chosen
0.000 EA (0.00) NOLEFTSLVPKT --No LEFT Sleeve Pocket Option Chosen
0.000 EA (0.00) NORIGHTSLVPKT --No RIGHT Sleeve Pocket Option Chosen.
0.000 EA (0.00) NOELBOWPATCHES --No Elbow Patches Option Chosen
0.000 EA (0.00) NOEMBROIDERY --No Embroidery Option Chosen
0.000 EA (0.00) NODIGITIZECHRGE --No Charge For Digitizaion Required
0.000 EA (0.00) NOHEATTRANSFER --No Heat Transfer Option Chosen
0.000 (0.00) ZZYES --SPECIAL ORDER
|
Replies:
Posted By: comatt1
Date Posted: 13 Dec 2011 at 6:11am
|
gonna have to build an array, then do a mid(right)) combo function
When I did it, the list was static, you will have to populate an array with a loop to count lines.
I can help a little more if you wanna private, but others may have easier recommendations
//shared stringVar array outcom := makearray(" "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ");
shared stringVar array outcom := makearray(" "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ");
local numbervar x;
local numbervar y;
local numbervar z;
local numbervar w;
local numbervar wx;
shared numbervar cntlines;
shared stringvar array xstr;
shared stringvar newstring;
//shared stringVar array comments := makearray(" "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ");
shared stringVar array comments := makearray(" "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ");
shared stringvar array incom;
comments[1] := {%engineeringComments}; // A
comments[2] := {%trafficEngineeringComments}; // b
comments[3] := {%waterUtilityComments}; // c
comments[4] := {%zoningComment}; // d
comments[5] := {%fireDepartmentComments}; // e
comments[6] := {%engineeringMappingComments}; // f
comments[7] := {%engineeringMappingComments-CON}; // g
comments[8] := {%realEstateComments}; // h
comments[9] := {%landmarksCommissionComments}; // i
comments[10] := {%urbanDesignCommissionComments}; // j
comments[11] := {%recyclingCoordinatorComments}; // k
comments[12] := {%policeComments}; // l
comments[13] := {%parksComments}; // m
comments[14] := {%transitComments}; // n
comments[15] := {%assessorComments}; // o
comments[16] := {%planningComments}; // p
comments[17] := {%planCommissionHearing}; // q
comments[18] := {%commonCouncilHearing}; // r
comments[19] := {%assessorComments-CON}; // s
comments[20] := {%architecturalReviewComments};//t
comments[21] := {%ligthingReviewComments}; //u
w := ubound(comments);
for wx := 1 to 21 do
(
newstring := "";
incom := split(comments[wx],chr(13));
xstr := split(comments[wx],chr(13));
y:= ubound(incom);
z := cntlines;
for x := 1 to y do
if len(incom[x]) > 1 then
( xstr[x] := totext(z,0,"","");
newstring := newstring + replicatestring(" ",5 - len(xstr[x])) + xstr[x] + ". " + replace(incom[x],chr(10),' ') + chr(13);
cntlines := cntlines + 1;
z := z + 1);
outcom[wx] := newstring
);
|
Posted By: campagnolo_1
Date Posted: 13 Dec 2011 at 10:43am
|
Found an easier (not necessarily prettier) way:
if InStr((Split( - 0'>{@ToText},chr(10))[2]),"--")>0 then
Mid((Split( mailto:%7b@ToText%7d,chr%2810%29%29%5b2%5d%29,--%29%29+2 - {@ToText},chr(10))[2]),(InStr((Split({@ToText},chr(10))[2]),"--"))+2 )
@toText first converts the memo field to text and then you can query it. Downside is that you have to create a detail section for howere many lines you have in the memo field.
As I said, not pretty but it works!
|
Posted By: comatt1
Date Posted: 13 Dec 2011 at 10:46am
|
setup a shared variable to count the number of "--" and in the details section, split from 1-2, 2-3, until the end.
That make sense?
|
Posted By: campagnolo_1
Date Posted: 14 Dec 2011 at 6:17am
|
I think so...I'll give that a try. Thanks for the idea!
|
Posted By: campagnolo_1
Date Posted: 14 Dec 2011 at 11:44am
|
Here is what I came up with, but it only gives me a "1" as result.
shared numbervar iIndex := InStr( mailto:%7b@ToText - {@ToText }, "--");
shared numbervar cCount:=0;
if iIndex <> 0 then cCount := cCount + 1;
iIndex := InStr( mailto:%7b@ToText - {@ToText }, "--");
cCount
|
Posted By: comatt1
Date Posted: 15 Dec 2011 at 3:32am
|
there is a loop missing here, you will have to load the array from
1 to iIndex := InStr( mailto:%7b@ToText - {@ToText }, "--");
---- however, this code would only show 1 for cCount. unless you didnt add some code.
see what iIndex shows as a value
|
Posted By: campagnolo_1
Date Posted: 15 Dec 2011 at 3:38am
|
INdex shows 65, which is the first instance of "--". After looking at it first thing this morning it was clear to me that there was no loop and that it would simply stop at the first occurence of "--". I'll have at it some more this morning and see what I can come up with. Thanks for the pointers! ;o)
|
Posted By: campagnolo_1
Date Posted: 15 Dec 2011 at 4:32am
|
Ok, change of direction. Instead of trying to count the "--", I changed the formula to count the lines in the memo field: InStr( mailto:%7b@ToText - {@ToText }, chr(10))
I tested it and it gave me the correct amount on several different memo fields.
Now how exactly were you thinking to create the appropriate number of detail fields?
|
Posted By: campagnolo_1
Date Posted: 15 Dec 2011 at 5:55am
|
Created this thinking it would show me all the lines in the memo field, but all I get is the last line (20) of the memo field.
global NumberVar iIndex := 1;
global NumberVar aArray := 1;
global StringVar tText := "";
global NumberVar aAmount := mailto:%7b@lineCount - {@lineCount };
while iIndex <= aAmount Do
(
if InStr((Split( - 0'>{@ToText},chr(10))[aArray]),"--")>0 then
tText := Mid((Split( mailto:%7b@ToText%7d,chr%2810%29%29%5baArray%5d%29,%28InStr%28%28Split%28%7b@ToText%7d,chr%2810%29%29%5baArray%5d%29,--%29%29+2 - {@ToText},chr(10))[aArray]),(InStr((Split({@ToText},chr(10))[aArray]),"--"))+2 ) else
tText := Split( mailto:%7b@ToText - {@ToText }, chr(10))[aArray];
aArray := aArray + 1;
iIndex := iIndex + 1;
);
tText
|
Posted By: comatt1
Date Posted: 15 Dec 2011 at 8:53am
|
??
tText:= tText + Split( mailto:%7b@ToText - {@ToText }, chr(10))[aArray];
not 100%
|
Posted By: campagnolo_1
Date Posted: 15 Dec 2011 at 8:54am
|
Ever closer......of course I didn't pass the data to an array, that's why I only got one line. So I created the array and passed the data into it, but now how do I display "all" the lines? Right now I can only display one line ( [1], [2], [3], etc..).
How can I show all of it?
NumberVar iIndex := 1;
NumberVar aArray := 1;
StringVar Array tText;
Redim tText [{@lineCount}];
while iIndex <= mailto:%7b@lineCount - {@lineCount } do
(
local NumberVar aArray := 1;
if InStr((Split( - 0'>{@ToText},chr(10))[aArray]),"--")>0 then
tText[iIndex] := Mid((Split( mailto:%7b@ToText%7d,chr%2810%29%29%5baArray%5d%29,%28InStr%28%28Split%28%7b@ToText%7d,chr%2810%29%29%5baArray%5d%29,--%29%29+2 - {@ToText},chr(10))[aArray]),(InStr((Split({@ToText},chr(10))[aArray]),"--"))+2 ) else
tText[iIndex] := Split( mailto:%7b@ToText - {@ToText }, chr(10))[aArray];
aArray := aArray + 1;
iIndex := iIndex + 1;
);
tText[1]
|
Posted By: comatt1
Date Posted: 15 Dec 2011 at 9:07am
|
you can always create a variable, loop through the array one element at a time and then create a single text field.
so don't display TEXT1 but a new one, that is coalesced array
|
Posted By: campagnolo_1
Date Posted: 15 Dec 2011 at 9:10am
|
I'm not quite following, comatt1. Could you give me some pseudo code maybe?
Thanks for the help by the way, I really appreciate it!
|
Posted By: comatt1
Date Posted: 15 Dec 2011 at 9:42am
|
NumberVar iIndex2 := 1;
NumberVar ct := 1;
local StringVar tText2:='';
while IIndex2<= mailto:%7b@lineCount - {@lineCount } do
{
tText2:=tText2+chr(10)+tText[iIndex2];
iIndex2:=iIndex2+1;
}
Ttext2;
|
Posted By: campagnolo_1
Date Posted: 15 Dec 2011 at 11:47am
|
I see, makes sense of course. I had found a simpler way by using "join", but a couple of the lines don't align all the way left, so I will give your suggestion a try. Unfortunately now I'm getting a strange error after refreshing the data in the report: "A subscript must be between 1 and the size of the array."
It seems to have to do with the aArray variable because it highlights this line: "if InStr((Split( mailto:%7B@ToText%7D,chr%2810 - {@ToText},chr(10 )) [aArray]),"NO")>0 then". I have tried making all the arrays static, but without success. More investigating tomorrow!
global NumberVar iIndex := 1; global NumberVar aArray := 1; global StringVar Array tText; Redim tText [{@lineCount}]; while iIndex <= mailto:%7b@lineCount - {@lineCount } do ( if InStr((Split( mailto:%7B@ToText%7D,chr%2810 - {@ToText},chr(10 )) [aArray]),"NO")>0 then tText[iIndex] := "" else if InStr((Split( - 0'>{@ToText},chr(10))[aArray]),"--")>0 then tText[iIndex] := Mid((Split( mailto:%7b@ToText%7d,chr%2810%29%29%5baArray%5d%29,%28InStr%28%28Split%28%7b@ToText%7d,chr%2810%29%29%5baArray%5d%29,--%29%29+2 - {@ToText},chr(10))[aArray]),(InStr((Split({@ToText},chr(10))[aArray]),"--"))+2 ) else tText[iIndex] := Split( mailto:%7b@ToText - {@ToText }, chr(10))[aArray]; aArray := aArray + 1; iIndex := iIndex + 1; ); join(tText)
|
Posted By: campagnolo_1
Date Posted: 19 Dec 2011 at 7:08am
|
Ok,
after another day of banging my head against the wall and scouring through forums I have to say that I'm at the end of my witts.
Just to recap real quick as to what I'm trying to do:
1. I have a memo field that I converted to Text.
2. I want to go through this text field create an array that holds the values for each line of this text field.
3. Then I want to go through the created array line by line (value by value) and check for the following conditions:
a) If there is a "No" in the line show a blank line " ".
b) If there is a "--" in the line, split the line at "--" and only show everything to the right of "--" until the chr(13)
c)If neither of the two previous conditions are met, just show the line.
4. The result of each condition gets put into a second array and then shown in the report.
Here is my (new) code that I created:
shared NumberVar x := 1;
shared NumberVar Array lLength := mailto:%7b@lineCount - {@lineCount };
shared NumberVar arrLen := UBound(lLength);
shared StringVar Array mMemo;
Redim mMemo [arrLen];
shared StringVar Array tText;
Redim tText [arrLen];
while x <= arrLen Do
(
mMemo := Split( mailto:%7b@ToText - {@ToText }, chr(10)[x]);
if InStr(mMemo[x],"NO") > 0 then
tText[x] := "" else
if InStr(mMemo[x],"--") > 0 then
tText[x] := Mid(mMemo[x],(InStr(mMemo[x],"--"))+2) else
tText[x] := mMemo[x];
x := x + 1;
);
join(tText);
Help!
(Thank you!)
All I get now is the very first line of the array.
|
Posted By: campagnolo_1
Date Posted: 20 Dec 2011 at 4:23am
|
And here it is:
shared NumberVar x := 1;
shared NumberVar arrLen := mailto:%7b@lineCount - {@lineCount };
shared StringVar Array mMemo;
Redim mMemo [arrLen];
shared StringVar Array tText;
Redim tText [arrLen];
mMemo := Split( mailto:%7b@ToText - {@ToText }, chr(10));
while x <= arrLen Do
(
if InStr(mMemo[x],"NO") > 0 then
tText[x] := ""
else if InStr(mMemo[x],"--") > 0 then
tText[x] := Mid(mMemo[x],(InStr(mMemo[x],"--"))+2)
else
tText[x] := mMemo[x];
x := x + 1;
);
join(tText);
My mMemo array declaration (or rather the way I tried to fill it) was off.
Thanks comatt1 for all your help and guidance!
This problem is solved!
|
|