Can not view text field in formula editor

[gallobask]

The database I use has some fields that are text_value/memo fields. They do not come up in the formula editor. Is there any other way to use these fields in crystal?

 

[lbass]

What version of CR are you using? What do you want to do with the memo field?

-LB

 

[gallobask]

Sorry, version 8.5. These memo fields coincide with procedure codes. Its a free text description of a hard coded procedure - for example there are two coded procedures on a case one for a hernia and one for a removal of a lesion. The text field for the hernia would say "Repair right inguinal hernia" and the lesion one would say "Removal lesion right upper thigh". I would like to combine both fields on the report so I do not have 2 separate line items so I can export the report to excel. But I don't see any way to write a formula for this if the field doesn't show in the formula editor!

Get this:
Repair right inguinal hernia
Removal lesion right upper thigh

Would like this:
Repair right inguinal hernia Removal lesion right upper thigh

 

[lbass]

Yes, you can't use memo fields in formulas before 9.0 except using isnull() I think, but you can use them in SQL expressions. I don't have 8.5 anymore so can't test this, but try creating a
subquery {%memo} in the SQL expression editor, like this, where you manually type in the field name, since it won't be in the field list:

Either try:

substr(table.memo,1,254)

or:

{fn substring(table.memo,1,254)}

To determine the appropriate punctuation on the field, first add in a field from the field list to see how it displays, and then replicate that punctuation within the substring. Then I think you can reference the SQL expression in a formula and you would do it like this:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x;
if not isnull({%memo}) then
x := x + {%memo}+ "; ";

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@display} for the group footer:
whileprintingrecords;
stringvar x;
if len(x)>1 then
left(x,len(x)-1)

Then suppress the group header and detail section.

-LB

 

[gallobask]

You have attained god/goddess status in my book!!!!
It works!!! I have struggled with this for years!!! Someone wrote me a view to try and do this and it slows the reports down so they are almost unusable!!! That's why I kept looking for another way to do this.

You have no idea how valuable this it!!! I have dozens of reports I can now add this as a subreport and get rid of that stupid view!!!!

THANK YOU THANK YOU THANK YOU!!!
I'm giving you a virtual hug right now!!!

 

[gallobask]

Well almost there.
When I query on only one case it works great! If I query on more than one case the formula editor box for the accum pops up and says "A string can be at most 254 characters long."

I even created it as a subreport (thinking this is only pulling for each case reord) but I still got the same message.

Is there a way around this?

 

[lbass]

Sorry, I totally forgot about that limitation--you really should upgrade. It is very difficult to handle lengthy strings in versions before 9.0. Do you really want to show the entire memo field? Is an individual memo field ever longer than 254 characters? What is the largest anticipated length of the accumulated memos in the group footer?

-LB

 

[gallobask]

An individual memo field is never longer than 254 characters. The number of actual characters in the field can vary from 25 to 150. The accumulated for the larger case could easily exceed the 254.
We are upgrading next year - guess I may have to wait. Darn - I was sooo excited!

Thank you for your help. I still learned something!

 

[Turkbear]

Hi,
Depending on your database a Memo type field could hold many more than 255 characters - in SqlServer and Access, for instance, they can hold up to 65,536 characters.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I am asking about the length of the final string to be displayed in the footer because it affects the solution.

-LB

 

[gallobask]

Yes LB is correct, the final string could very well be more than 250. In fact in testing it will work fine till it comes to a record that exceeds the 250.

 

[lbass]

Again, what do you estimate to be the maximum length in the group footer?

-LB

 

[gallobask]

I found one at 325, counting characters and spaces. I don't think we'd go beyond 400.

 

[lbass]

Please see: thread767-1489553 for a detailed description of the approach to use--along with an even earlier thread, if necessary.

-LB