Count words in field

[zmanthao]

Hi,
I'm new to Crystal Report and need your help with this:

I have a field call: Fruit<Memo> that previews this:
Apple
Apple
Apple
Apple

I want to tally up the number of apples so that it show in the preview. Total would be 4 so how do I do it so that the # 4 shows in the preview. What is the formula to do this? Thanks in advance.

Zman.

 

[dgillz]

From the design window, right click on the field in question and select insert, summary, and make it a count summary. This will give you a count of how many apples, praged, peaches, etc. Do this again only select grand total instead of summary and you will get eh grand total number of records for your report.

No formula is needed in either case. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[AyJayEL]

dgillz

Is that what Zman wants? I think he may have a memo field with Apple, Apple, Apple, Apple in. If this is the case I don't think you can query a memo field (not 'til Crystal 8.5 or so.)

If I am wrong about the memo field please ignore me! Learn something new every day *:->*

 

[dgillz]

Andy,

Actually you are correct, if this is a memo field I do not know what the resolution would be. I do not believe you can do this in Crystal 8.5 either. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[AyJayEL]

I don't know if this knowledgebase article might help. It's worth looking at though.

http://support.crystaldecisions.com/kbase/c2009954.asp

Learn something new every day *:->*

 

[AyJayEL]

I also found this on another website that might help.

Using Memo Fields in Formulas

Our report design team is often asked whether memo fields can be used in formulas and record selection in the Crystal Reports (CR) designer.

Generally, you cannot use memo fields in formulas and record selection in the CR designer.

This behavior occurs because Crystal Reports is created with Microsoft C++. A string field in C++ cannot hold more than 255 characters.

To work around this limitation, you can parse the memo field data into several shorter fields in your database before bringing them into CR.

NOTE: The following example applies to most standard Structured Query Language (SQL) type databases. The following example may not apply to your particular database. Reference your database documentation for details on the syntax of the SUBSTRING or SUBSTR function.

To parse the data, complete the following steps:

Create a View of the table to break the memo field into 2 or more parts before bringing it into the CR designer.


In the database View, write a formula similar to the following using the SUBSTRING() function to break your field into parts:

SUBSTRING(memofield,1,100) as Field1
SUBSTRING(memofield,101,200) as Field2
SUBSTRING(memofield,201,300) as Field3


In CR, set the location of your database to point to this View.


Place the individual fields in a text object or formula in the CR designer to give the illusion of re-uniting the memo field. For example:
{field1}+{field2}+{field3}

You can use the new 'fields' in formulas and record selection in CR.

There is one exception to this rule about being unable to use memo fields in formulas. You can use memo fields in formulas if you are testing for null instances of that memo field. A null value is not a blank space in the database. It is when no value exists in the field.

You can create a formula to return True or False if the memo field is null and use the result of this formula to perform calculations and formatting on a report.

For Example:

To test for a null value and then use that result to count the number of null records on a report, complete the following steps:

Create a new formula similar to the following:

//@Null Field
//remember that the
//memo field
//will not be listed
//in the list of report
//fields. You must
//type it manually.
If isnull({memo.field} then &quot;True&quot; else &quot;False&quot;

NOTE: The memo field name will not be available in the 'Report Fields' list of the formula editor. You must type the field name in manually.


Insert @Null Field into the details section or the report.


Use the results of @Null Field to count null records on the report. Create a second new formula similar to the following:

//@Manual Running Count
//the record is counted
//only when @Null Field
//returns &quot;True&quot;.
whileprintingrecords;
numbervar counter;
If @Null Field = &quot;True&quot; then counter:=counter + 1
else counter:=counter


Insert @Manual Running Count into the details section of the report.

You can also conditionally format report fields and sections based on whether a memo field is null or not. For example:

IsNull ({Product Type.Description})
You can use this type of formula to conditionally format a field, such as suppressing a memo field when it contains a null value.

In this way, you can use memo fields in formulas to test for null values.

Learn something new every day *:->*

 

[synapsevampire]

I think the easiest way is to convert the memo field.

If it's a SQL type of database, convert it using the SQL Expression Fields type, and use something like:

//SQL Server syntax:
convert(varchar(254),substring(MyMemoFld,1,254))

Use as many SQL Expressions as required to adequately parse the memo field.

Now you can do a count or distinct count (and sort and group) on this field using conventional CR means (insert->summary).

If you need to count the individual words within the field, as with:

Apple Pear Plum
Apple Banana PassionFruit
...

You'll need to parse the words out of the field, which is a bit more complicated.

I'll assume that you only need to count the entire contents of the field, so the above should suffice.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com