Memo fields in formulas Aarrgghh!!! 2

[KerryCTI]

I'm trying to build a formula in CR 8.5 but unfortunately the most essential field is a memo field, which you can't use in Crystal. As this is not my own database just one I can access I cannot change the field type anywhere else but in CR. Basically I want a formula field that will do the following:

If IsNull({MemoField}) Then
{Field1}
Else
{MemoField}

Does anyone know of a way to convert this memo field into a string (we only use about the first 12 characters in the field!!!) or any other way to do the above formula?

Thanks.

 

[dgillz]

It cannot be done with a memo field. The use of a memo field in a formula is not allowed, except for the Isnull() test. However I have a workaround.

Place {field1} right on top of the memo field on your report. Then conditionally format {field1} to suppress if the memo field is not null. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[manney]

A memo field can be parsed into 256 byte segments by use of the SQL SUBSTRING function. The resultant field(s) can be tested, concatenated, etc.

The SQL expression is

fn SUBSTRING ({Table.MemoField}, StartChar, EndChar)

This does not work on all systems (depends on the SQL implementation of your database). My database will only provide the first slice (Bytes 1-256, for example).

Seagate sent me a sample report whose title is "Memo Fields in Formulae", which -- I presume -- is on their website. I'd be glad to send it to you if you cannot find it there.

 

[rhinok]

Since SQL Expression formulas are dependent on your database driver, this expression may not be available to KerryCTI.

I actually tested this in a SQL expression when the topic was posted. Since SQL Expressions interact directly with true database fields, I assumed that it would work. My first clue that it was klugey was that I still couldn't see the memo field in the list of available fields. I could enter the SQL Function directly, but my results were terribly inconsistent. The SQL Expression would work once, but then would error out('field not in list', plus others). I couldn't ever get it to work consistently enough to use.

 

[tech45]

I had the same problem. Our workaround was to create a view of the database for the dates needed and then change the memo field in the view to a VarChar field. I could then insert the memo field "view" into the report and use it as any other field in CR. The drawback is that if the report changes over time the view also needs to be updated as well. Not a big deal-just need to remember to do it.

Hope this helps,
H.S.

 

[anangarg]

do one thing
first change the field type in database to text field then use it in crystal report.once u have used it,don't veryfies ur database in crystal report.again change the field type to memo in ur database.then it will work.
notence u again changed the type to memo,don't verify the data base in crystal report.

bye anan