Splitting text from memo field into report 1

[madvalpreston]

Hi

Not sure how to go about this one

We have a table called memo and in here is a text area to add data.

What we have is for example

1804
51630
L677

On the report I want it like this

Die No: 1804
Lot No: 51630
Heat no: L677

What I can work out is how to extract the information.

Line 1 (no matter how long will always be Die No) Line 2 always Lot No etc...... but the lenghts may differ.

Could someone advise how I program this please

Thanks

 

[madvalpreston]

Hi

I am still trying eveerything going to ge this to work.

I created a new formula and added it into the report from the crystal help.

Local StringVar x := "hello";

Length (x) //The formula returns the Number 5

I then ran the report from our system and the number 5 shows. I am completely baffled why the other formula will not show.

Hope you can assist

Thanks

 

[lbass]

Maybe the returns are being created by a line feed, instead of chr(13). Try replacing chr(13) with chr(10) or with a combination of chr(13)+chr(10) or the reverse, chr(10)+chr(13).

-LB

 

[madvalpreston]

Hi

I have tried all combinations but still no luck.

If I change the first row to (10) it does not even show on the crystal report
stringvar array x := split({BAQReportResult.Memo.MemoText},chr(10));

This is how my formula looks now

stringvar array x := split({BAQReportResult.Memo.MemoText},chr(13));
if ubound(x) >= 3 then
"Die No: "+x[1]+chr(10)+chr(10)+"Lot No: "+x[2]+chr(10)+chr(10)+
"Heat No: "+x[3]+chr(10)+chr(10)
Any other ideas please, or perhaps is there another formula we can use to extract the details with (but of course I know this is the preferred method)

Thanks

 

[lbass]

Please place two test formulas like this in the detail section and then report back with the results

instr({BAQReportResult.Memo.MemoText},chr(13))

instr({BAQReportResult.Memo.MemoText},chr(10))

-LB

 

[madvalpreston]

Hi

On the crystal report

chr(13)) comes back with the number 5.00

chr(10)) comes back with the number 0.00

Thanks

 

[lbass]

So you should be using chr(13) in all places. But I think we got off track here. As I understand it, the formula shows up in CR, formatted as you wish, but not in your application? I don't really know how to troubleshoot that. You could try eliminating the variable and see if that makes a difference:

if ubound(split({table.memo},chr(13)))>= 3 then
"Die No: "+split({table.memo},chr(13))[1]+" "+
"Lot No: "+split({table.memo},chr(13))[2]+chr(13)+chr(13)+
"Heat No: "+split({table.memo},chr(13))[3]

You might also want to try eliminating the chr(13) just to see if the formula then comes over, even if this would then remove the returns.

-LB

 

[madvalpreston]

Hi

I have just tried your suggestion and again with no success.... you are correct the code we have tried this far shows ok on the CR but not on our application and of course it may well be the application, however, it is our main business system and we run many CR with formula and thse come through.

Anyhow I tired this formula without varaible

if ubound(split({BAQReportResult.Memo.MemoText},chr(13)))>= 3 then
"Die No: "+split({BAQReportResult.Memo.MemoText},chr(13))[1]+" "+
"Lot No: "+split({BAQReportResult.Memo.MemoText},chr(13))[2]+chr(13)+chr(13)+
"Heat No: "+split({BAQReportResult.Memo.MemoText},chr(13))[3]

(this one shows the correct results in CR but not application)

Then I tried this one without the CHR(13)

if ubound(split({BAQReportResult.Memo.MemoText}))>= 3 then
"Die No: "+split({BAQReportResult.Memo.MemoText})[1]+" "+
"Lot No: "+split({BAQReportResult.Memo.MemoText})[2]+" "+
"Heat No: "+split({BAQReportResult.Memo.MemoText})[3]

(This one does not show on CR or the application but I think from testing yesterday it should be CHR(13))

So I am totally baffled on this. I dont suppose you have any other suggestions. I appreciate you have been patient with me on this one. Thanks

 

[madvalpreston]

Hi LB

I have made a small beakthrough I think. I changed the formula to this

if ubound(split({BAQReportResult.Memo.MemoText},chr(13)))>= 1 then
"Die No: "+split({BAQReportResult.Memo.MemoText},chr(13))[1] //+" "+
//"Lot No: "+split({BAQReportResult.Memo.MemoText},chr(13))[2] +chr(13)+chr(13)+
//"Heat No: "+split({BAQReportResult.Memo.MemoText},chr(13))[3]

So it just looks at the first line of the code and made it >=1
This then brings this out in my CR

DIE No : 1804

And in my aplciation it actaully shows as

Die No: 1805
51360
L677

So by doing this it lets the information through to the application, so it must be somehting to do with the formula. As soon as I try more than 1 line then it stops briging the information to the aplication unless I make it all chr(13))[2] but put 1 instead of 2.

Any ideas, I will keep trying to play with the formula.

Thanks

 

[lbass]

You can't use the split function without adding a value to split on.

Maybe it is the split function that isn't working, since it appeared not to honor the split. So try a test formula like this and see if it shows up in the application:

"ABC"+chr(13)+"def"+chr(13)+"GHI"

-LB

 

[madvalpreston]

Hi

Tested this and it show in the application like this

ABC
def
GHI

Thanks

 

[lbass]

It appears that the split() function is the problem. Are there ever more than three elements in the field?

Also, can you test the following to see if it passes and what it returns?

extractstring({table.memo}," "," ")

-LB

 

[madvalpreston]

Hi

There would never be more than 3 elements in the field.

I tried this formula

extractstring({BAQReportResult.Memo.MemoText}," "," ")

This did not return any value at all.

Thanks

 

[madvalpreston]

Hi LB

Do you have any other ideas on this at all or perhaps another way to achieve our goal.

Thanks

 

[lbass]

Okay, try this--although I'm not sure about the availability of these functions outside of Crystal either:

stringvar x := {table.memo};
stringvar y := left(x,instr(x,chr(13))-1);
stringvar z := right(x,len(x)-instrrev(x,chr(13)));
"Die No: "+y+" "+
"Lot No: "+mid(x,instr(x,chr(13))+1,len(x)-2-len-len(z))+chr(13)+chr(13)+ //-2 to remove chr(13) in x
"Heat No: " + z

This tested out for me.

-LB

 

[madvalpreston]

Hi

I tried putting in this formula (I changed the x to u as I think the variable was used elsewhere in t he report)

stringvar u := {BAQReportResult.Memo.MemoText};
stringvar y := left(u,instr(u,chr(13))-1);
stringvar z := right(u,len(u)-instrrev(u,chr(13)));
"Die No: "+y+" "+
"Lot No: "+mid(u,instr(u,chr(13))+1,len(u)-2-len-len(z))+chr(13)+chr(13)+ //-2 to remove chr(13) in x
"Heat No: " + z

I get an error message now saying

"String Length is less than 0 or not an integer"

I tired an IF statement like

If stringvar u <= 0 then 0 or stringvar y <= 0 then 0 or stringvar z <= 0 then 0 or else

I then complained about the 0 so I did it with "0"

But I still got the message about the string length.

Any ideas how I can change the code so it ignore blanks or 0

Many thanks

 

[madvalpreston]

Hi

Also I have tried IF strinvar = "" but still get same problem.

Thanks

 

[lbass]

Aren't there two returns (chr(13)) in each memo field? Try removing the -2 and see what is returned. If there aren't two returns, then the formula won't work correctly anyway.

-LB

 

[madvalpreston]

Hi

I took the -2 out but still got same
"String Length is less than 0 or not an integer"

But this is when I run our application, as before it works ok in CR. If I take out the -2 I do get a L on the second row

DIE No: 1804 Lot No : 51360
L
Heat no: L677

With the -2 in it works ok in Crystal but when I run in our apllication i get the string lenght error.

I am not sure what more we can do, there appears to be in some cases clashed between CR and the Vantage system which runs off Progress.

If you have any more ideas I can try next week, other than that thank you very much for all your help on this, it been great.

 

[lbass]

I can't think of any other approaches, sorry. It seems like these are issues, as you suggest, with the other application. There is a Progress forum--forum292. Maybe someone there could help with figuring out acceptable functions/syntax.

-LB