Need help with calc to count total number of records with particular text phrase

[James Gill]

I'm an extreme noob that's been using Filemaker for maybe a month now so I'm having a little trouble with this calculation. I have a parent table in my database that contains a child relationship containing a field with a specific phrase I need to be able to count the occurrences of. I then want to display the total number of occurrences on the parent table.

How would I do this?

[Fitch]

You'll need to define a calculated field in the child table:

PatternCount(field;phrase)

See PatternCount

Then in the parent table:

Sum(related::calc)

[James Gill]

Here's my next newbie question. If the pattern count is 0 (or blank), the calculation returns a blank value. How do I make it return a 0 value instead of a blank one?

[LaRetta]

If you wish only your child records to display a 0 if no pattern, uncheck 'do not evaluate if all referenced fields are empty' but if you want the parent calc to display 0, even if no children, you can use:

Max ( Sum ( childTable::patternCalc ) ; 0 )

UPDATE: And you can use a combination; ie, if you want the parent calc to display 0 if the parent has children but none have that pattern, only uncheck the child calc. Then the parent calc will display empty if no children or 0 if children but no pattern.

Edited January 12, 2010 by Guest
Added update