Formula Creation on Blob and/or Memo Fields

[M8tt]

I have a Memo Field in an Oracle database which I would like to use in a Formula, I simply want to use the mid function on the field.

The field is not visible in the Report Fields section of the Formula Editor and when I type its code in I get an error message saying that Blob and Memo Fields cannot be used in a Formula.

Is there any way around this problem.

Many Thanks.

 

[rajrev]

HI, thaks for asking this question. I have the same problem!! I'm using SQL Server
Two different tables
t1.empno,
t1.date,
t1.notes (memo)

t2.empno,
t2.case
t2.date,
t2.notes (memo)

In the report, have to display both "notes" in the same column without any douplicates as well as empty record.
Any one know the solution pl. let me know.

Thanks
MK

 

[FVTrainer]

In versions before CR 9, chars and varchars above 254 characters in length are treated as memo fields and cannot be used in formulas. In CR 9, they can be.

There are two possible workarounds. One is to base the report on a stored procedure or view, and in that sp or view use the psql/t-sql equivelant of the mid statement.

Alternatately, in CR 8 and above you can create a SQL Expression using the databases' equivalent string function. It T-SQL, that would be the SUBSTRING function and would look like this in the SQL Expression formula editor:

{fn SUBSTRING(client_notes."Note",1 ,254 )}

Note that in the example above, the field "Note" didn't show up in the report field list because it is a memo field and Crystal thinks it still should exclude it. I had to manually type it in. But it still works.

 

[IanWaterman]

You can not use a memo or blob field in a formula.

Create a view of your table and then use the INSTR command to extract the data you require. You can then then use the view in the same way as you use a table.

Ian

 

[rosemaryl]

Dear M8TT and Rajrev,

Use a SQL Expression! Syntax is database and/or ODBC driver sensitive.

You can type in straight sql or use the functions from the functions list. I find the functions limiting and usually just use the SQL. I find enclosing the entire statment in parentheses helpful to avoid error messages.

Insert/Field Object/Sql Expression

Use the Oracle or Sql equivalents as you would do in PL/SQL or Query Analyzer.

Here is an example using Substring for Oracle:

(substr(Table."memofield",1 ,10))

Note: Your memo field will not display in the list of fields from which to select and must be manually typed in!

Hope that helps,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.