Embedded Crystal formulas in text strings 1

[gregoriw]

Would this work? Store a large text string in a SQL server database table with the hopes of "tagging" a certain part of it with the name of a formula (stored from the RPT file) so that when the text string is displayed it shows the main string text characters plus the true value from the formula? This would be something like a mail-merge operation, using the value of a formula to fill parts of the text with a correct name. I tried putting the formula name in the body of the string (as shown below) but it didn't work.

I know that you can put a textbox on the report and paste a formula name into the stream of the textbox characters and it will properly display that value at runtime. But I was wondering how to do it with a value from the database. I also know that you can pass in new values to a formula from outside Crystal to make alternate values appear. But how can a formula be used as such in a mail-merge type capacity by using the value of the formula field on the report.

I have four reports that basically use the same main text string, but I need to display different company names based on other criteria. So I was hoping to find an easy way to save the main text once and just insert the formula value from each of the four reports into the same database string.

Example text: "Please make payment to the @FromCompanyName location before the end of the month."

I need the value of the formula @FromCompanyName to be displayed inside the text string when printing the report. Any suggestions would be helpful.

Gregoriw

 

[dgillz]

Write a formula like this:

+"Please make payment to the "&{FromCompanyName}&" location before the end of the month."

{FromCompanyName} itself does not have to be a formula, it could be a datasbase field, a parameter field, etc.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[Ngolem]

@DisplayStatement

EvaluateAfter(@FromCompanyName);
WhilePrintingRecords;

"Please make payment to the " + @FromCompanyName + " location before the end of the month.";


The EvaluteAfter() function is necessary only if @FromCompanyName is in the same section as this formula. The only problem you may have with this is if the CompanyName is over 160 chars long



Jim Broadbent

 

[gregoriw]

I did not want to put the text string ("Please make payment...&quot into a Crystal formula but rather wanted to keep it on the database where other operators can modify the text they way they need it to be (through a separate letter generator program).

I just want the users to be able to insert a "tag or formula identifier" into this text so that when the report picks up the main string from the database, it would then automatically insert the company name to use (because it would be stored in a formula on the report itself).

Thanks,
Gregoriw

 

[Ngolem]

I suppose you could do it....But I wouldn't....too much trouble when this is easily created in Crystal itself...The formula would have to exist in their reports for it to be useful....so why not create the formula? Also they would have to know exactly how this formula would be named...a maintenance headache in years to come

Jim Broadbent

 

[synapsevampire]

Consider leaving Crystal out of the equation and let the SQL Server return the proper data (full text) from the database using the Replace function based on whatever you pass through from the report.

Otherwise, I'd stick with just dropping the text field into a text box, and then create formulas to be used to determine which name gets inserted, and drop the formulas into the text box at the appropriate place.

If you supplied an example of how things change you might get a better answer, generally changing a company name is based on the field in a table, not a hard coded rule.

-k

 

[IdoMillet]

In the string within the database, use something like:
--------------------------------------------------------
"Please make payment to the &&@FromCompanyName&& location before the end of the month."
--------------------------------------------------------

In Crystal, use the REPLACE() function to replace &&@FromCompanyName&& with @FromCompanyName

Within the same formula, you can cycle through all possible REPLACE scenarios (all poossible formula names) to make sure the user is free to specify any number of functions within the same string (stored in the database).

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Same idea, Ido, except I'd just let the database do the work rather than CR, it's faster.

-k

 

[gregoriw]

Thanks for all the great suggestions...and I truly appreciate your responses. Yet I just found out that I will not be able to manipulate the text string because it is *not* a true String data-type. It is a MEMO/TEXT SQL Server datatype and Crystal Reports 8.0 cannot handle any formula operations with a MEMO datatype...doesn't even show up in the DataExplorer.

So I guess I'll have to find another way to handle my task. Perhaps needlessly having to clone several copies of the same RPT file to supply a report to the 4 different companies. Or better yet, use synapsevampire's idea and construct the final text string outside of Crystal and pass it in through a formula.

Thanks again.

 

[IdoMillet]

You can CAST the MEMO field into a VARCHAR with a lenght of no more than 254...

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[gregoriw]

Blasted string length limitation!!!! I can't use CAST because every one of 45 possible text messages has a length of over 1200 characters. Makes me wonder if I can even pass a text string that large (after manipulating the proper company name in the memo) to a Crystal Formula? Wouldn't the formula abort because the string length will be >254 characters?

If so, that'll mean I'll either have to clone multiple RPTs needlessly just to insert a proper company name OR create a temporary SQL table with the updated text plus company name (generated outside of Crystal) and then build the report from the temporary table.

Gregoriw

 

[IdoMillet]

Time to switch to CR 9.0 where that limitation is removed... )

- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

You won't need a formula from the database, use a select with a SQL Server Replace to change the text.

Not very complicated to do, and it can be done in a View which you can pass SQL through from within Crystal.

-k