parse through memo field to extract only certain lines/parts of lines 1

[campagnolo1]

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 := {@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({@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);

All I get now is the very first line of the array.

Help!

(Thank you!)

 

[RTag]

Just handle this inside the database. What is the database type?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[RTag]

... or try this:


shared NumberVar x := 1;
shared NumberVar arrLen := {@lineCount};
shared StringVar Array mMemo;
Redim mMemo [arrLen];
shared StringVar Array tText;
Redim tText [arrLen];
mMemo := Split({GetData;1.a}, 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);

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[campagnolo1]

Brilliant!

I guess I wasn't too far off but just made a mistake as to where and how to fill my mMemo array? I replaced your ({GetData;1.a} with {@ToText} and it works just fine. Ther eis just a minor formatting glitch, but I think I can figure that out myself. I believe it has to do with the chr(10).
What did you mean by handling it inside the database? We're using FoxPro (.dbf) tables.

Thank you very much for your help!

Chris

 

[RTag]

Yes, you were very close

Usually database performance is much better than CR performance and it is easier and faster to process the data inside the database. Unfortunately I cannot help with FoxPro.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[campagnolo1]

RTag,

you have helped me a lot already and hopefully our company will soon be switching to SQL databases, which I personally like a lot better!

Cheers,

Chris

 

[campagnolo1]

Well, it seems I have celebrated to soon. After some more formatting of the report I have tested it on different order numbers and except for the original order number I now get the "A subscript must be between 1 and the size of the array." error. When I click OK the formula editor highlights "mMemo[x]" in "if InStr(mMemo[x],"NO") > 0 then".

What the #&$%^!! Why does thi not happen on the order number I used originally but every other one?

???

 

[RTag]

You may need to verify the database

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[campagnolo1]

RTag,

I actually made sure that the tables were current and the database was verified when I got the error for the first time. I have looked in the tables on some of the order numbers, but everything is pretty much identical (the memo field is pretty much the same every time). I honestly have no clue what could be different from one order to the other. I'm still digging through it and will post if I find something out of the ordinary.

Thanks for your suggestion!

Chris

 

[RTag]

the length of the field may be different

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[campagnolo1]

RTag,

I think I found the problem. In some of the orders there are items that don't get configured and therefor the memo field is empty. In my particular order that works I happen to only have 2 configured items. I will do some modifications and let you know what happens.

 

[campagnolo1]

The empty memo field wasn't the problem either.
The length of the field shouldn't matter since I'm sizing the array dynamically by using the line count. Here is the formula I'm using for that:
NumberVar l := Instr({@ToText}, chr(10));
l;
How can I check for the length of the field?

 

[RTag]

Can you show the current formula ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[campagnolo1]

shared NumberVar x := 1;
shared NumberVar arrLen := {@lineCount};
shared StringVar Array mMemo;
Redim mMemo [arrLen];
shared StringVar Array tText;
Redim tText [arrLen];
mMemo := Split({@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);

I also checked the length of the memo field, and for the order that works it is 1,483 characters, for one of the ones that don't work it's 1,224 characters.

 

[RTag]

what is the Memo data for which the report is failing?
Can you publish it here ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[campagnolo1]

Would you need the actual report or a screenshot? It has customer information on it that I couldn't publish, but with a screenshot I could crop that part out.

 

[campagnolo1]

Found one more anomaly:
On the order that works, the line count is 20 and that corresponds to the amount of lines in the Configuration List. On the order that doesn't work the line count is 20, but on the Configuration List are only 15 lines. Since I'm sizing my array using the line count, that might screw things up. Even though the array is now bigger than the actual lines could that still mess things up?

 

[campagnolo1]

Yup, I changed the array size to 15 and the report works flawlessly. So my problem is my line count formula, which seems to always return 20.

 

[campagnolo1]

Ok, changed the line count formual to:
StringVar Array text:= Split({@ToText}, CHR(10));
NumberVar a := ubound(text);
a
and all is well! Trtied 6 different orders and they all came out right.

Thanks RTag for sticking with me and listening to my rambling!