Memo fields, parsing, text substitution

[Pudge]

I’m working on an application that requires parsing and doing string substitutions in a large text field. The database is a SQL database. When reading the field, Crystal assigns the field to datatype = memo because it exceeds 255 characters. The intent is to be able to do some substitutions within this large text (memo) field. Crystal does not permit the use of a memo field in a formula.

I am requesting any assistance or advice in how this might be done.

An example:

The memo field :

“The client <M/M lastname> will comply with the treatment plan by attending counseling services on a <frequency> basis for <duration>. <Staff> will provide on call assistance and support on a 24 hr, 7 day week schedule. ………etc…..”

Our need is to be able to replace <M/M> with either Mr. or Ms. Based on the client gender, <lastname> with the clients actual last name (a field in one of the database tables, <Staff> with the staff name (a field in one of the database tables), <frequency> and <duration> with actual values.

I am using Crystal Reports version 8.

Thanks

Pudge

 

[mbarron]

Instead of using the large text field, you can create the correspondence as a form letter using Crystal. Mike

 

[synapsevampire]

mbarron has a sensible approach, but if you intend to maintain the text within the database for the sake of reusability, and having a centralized repository, I'd approach this with a Stored Procedure to just return the correct text for each row.

You can also use a SQL Expression to do the work on the database side, but this might require multiple SQL Expressions, as in:

substring(REPLACE(MyTable.&quot;MyMemo&quot;,'<M/M>',MyTable.&quot;Gender&quot,1,350)

350 chars was arbitrary, subsequent substrings starting at the 350 point, etc.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Pudge]

Thanks for the suggestions.

mbarron: I do not have the option of doing a form letter. We are are actually accessing a large database that maintains client data including treatment planning information.

synapsevampire: To follow up on your suggestion, I would have to do the &quot;parse&quot; in an SQL rather than try to do anything withing crystal reports (via formula). Correct?

pudge

 

[rosemaryl]

Dear Pudge,

SynapseVampire (hi) was instructing you to use SQL Expressions. It is located in the Insert/Field Object menu, just below formula fields. The syntax you use and the functions available depends upon your database (SQL, Oracle).

The example he gave works fine in SQL server.

As does this example.
substring(REPLACE(SMSYSUSERINFO.&quot;USERNAME&quot;,'R','X'),1,350)

You would replace the 350 with the maximum size of the field.

Your issue, I am afraid, will be that your gender table stores M/F instead of Mr. or Ms.

To solve that - you would need to update your gender table with the values (Mr. for Male and Ms. for Female) as you cannot use an if statement in the SQL expression.

Hope that helps,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Ro: And Hi-hi to you. Hope all is as it should be.

You can use a Case statement in the Select to resolve this sort of issue:

select substring(REPLACE(chrPeriodTest, 'month', case intMonth when 8 then 'August' when 9 then 'September' else 'Hmmmm' end),1,350) as Text1 from ats_rpt_period

I didn't use a salutation, because I wanted to make sure I had the syntax right, but this is easily converted to suit the need. Again, I'd address this with an SP, but as you can see, it also works with a SQL Expression.

I'd also suggest that a salutation be stored in Mr./Miss/Mrs./Dr./etc. format, or there exist a relationship to a reference table as some folk prefer a formal salutation (Dr. for instance).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear K,

I have used Case when in SP and Select() Case in Crystal, but I couldn't get the case staement to work in the SQL expression.

Can you elucidate...?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Pudge]

Thanks to all of you for your suggestions. I have tried to insert and SQL expression. However, I am running into the same difficulty. Crystal will not recognize the memo field as an argument in the substring(replace... function. It may be related to my SQL. Don't know.

Until I can get the ability to 'substring' the memo field to strings that I can use the replace function on, I can't do the replacement.

Pudge

 

[synapsevampire]

Pudge: Do the replacement using a SQL Expression on the database side, not in Crystal.

The above example is a working model exactly as it is, just replace the values.

Ro: The case works like:

select Case field
when <value> then <output>
when <value> then <output>
when <value> then <output>
Else <default value>
end
as MyCasedField
from MyTable

If you're using an older version of Oracle (pre 9i), then use the Decode function to handle this sort of thing.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear K, (All is well)

Yes, that is in SQL, I am very familiar with that use.

I thought you were doing this as a SQL expression in Crystal.

I used your exact example (prior to the case) with slight mods for table names and it worked perfectly in Crystal!

Pudge:

In Oracle, I have had issues with using the memo field (which you must manually type in) in the SQL expression if the field itself is not on the report canvass.

So Pudge, place your memo field on the report and suppress and see if that gets the SQL expression to work for you without giving an error. I don't know why it works, but it does for me! (Oracle 8.1.7.1.3)

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Yes, I designed it as a SQL Expression, it works fine.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ro: (Oracle 8.1.7.1.3) doesn't support a Case within a select line, does it?

I used to use the Decode function for Oracle, which is akin to an IIF. It works, but it's uglier.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear K,

I may be having a mental blockage today. I know that the regular SQL expression works fine (prior to the introduction of the case part).

When I use your SQL expression with the Case it throws errors at the when -

Are you using the Case in the SQL expression in **Crystal**?

I have no problems using case elsewhere.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.