Count Text Field "how many occurrences"

[former_member871909]

Hi there, In crystal reports

I am trying to create a formula/ summary of how many times "Warranty" shows up in the Comments column, and a separate count of "Parts".

Ideally having 2 separate formulas to count each item would be great.

What i've tried so far was

formula name: Warranty

if {report.name.comments} = "Warranty" then 1 else 0

Then inserted a Summary with the warranty formula, I tried (Sum, Count, Count distinct) and it was all incorrect.

Help? thank you in advance.

Title: Comments:

[DellSC]

Are you trying to count the number of times each word appears within the field for a single records? Across multiple records in a group? For the whole report?

-Dell

[ido_millet · Accepted solution]

Create a formula like this:

If instr({Report.reportcomments}, "Warranty") > 0 THEN 1 ELSE 0

To get the count of records containing that word, SUM that formula.
Note: do not use COUNT for this because COUNT would also count zero cases)

[former_member871909 · Accepted solution]

I tried as recommended: as you can see the 0 at the top of the workshop header. That is just the formula inserted into the footer of the report.

Then I created a summary after creating the formula using the "sum" and still got 0.

[ido_millet]

Place the formula in the detail section. Observe if it returns 1 for the matching cases. If not, fix the formula.

[former_member871909]

I found out that it was "case sensitive" so as soon as i made it all caps it returned the number properly. Thank you

[former_member871909]

I did not add sections by "groups". So, there aren't necessarily any groups. But my intent is to calculate Number of records that contain "Warranty" then supply a total at the bottom of the report and do the same for the word "Parts".

I tried this formula from reading online how to's but it doesn't work.

Formula 1 Name: Warranty

If {Report.reportcomments} = "Warranty" then 1; Else 0

Formula 2 Name: Parts

If {Report.reportcomments} = "Parts" then 1; else 0

There were 51 total records: of those 51 records, Warranty appeared = 11 times, and Parts appeared 9 times. Then I went to "summary" inserted the new formula. I tried selecting "count", "count distinct", "sum" but none of these worked accurately. When I did "sum" it counted the entire report and instead of just records with "warranty".