14 Replies Latest reply on Apr 9, 2012 4:35 PM by philmodjunk

# Numeric total of the occurrence of a word in a set of fields

### Title

Numeric total of the occurrence of a word in a set of fields

### Post

I am simply trying to caculate a numeric total from the existence of a particular string (a word) in a set of fields. In other words, I have four fields in my database. If a particular word appears in any of those fields, a number gets incremented that appears in another field. I've tried many things, all to no effect. Help?

• ###### 1. Re: Numeric total of the occurrence of a word in a set of fields

What have you tried, and why did those methods fail?  The difficulty that I see is the usual one, that the text string (word) may appear in the whole field as any of:

department
Part[space]
[space]part[space]
[return]part
part.
part,

etc.

• ###### 2. Re: Numeric total of the occurrence of a word in a set of fields

My case isn't that complicated. All I need is to know if the whole word "Voted" appears. There will be no punctuation, no variation in case, etc.

• ###### 3. Re: Numeric total of the occurrence of a word in a set of fields

So, does the calculation:

PatternCount ( Field1 ; "Voted" ) + PatternCount ( Field2 ; "Voted" ) + PatternCount ( Field3 ; "Voted" ) + PatternCount ( Field4 ; "Voted" )

at least give you the number of times the word 'Voted' appeared in the 4 fields?

• ###### 4. Re: Numeric total of the occurrence of a word in a set of fields

Rereading the question it seems that how many times in a field is not the purpose but if it is in the field, so:

(PatternCount ( Field1 ; "Voted" ) > 0) + (PatternCount ( Field2 ; "Voted" ) > 0 ) +( PatternCount ( Field3 ; "Voted" ) > 0 ) + ( PatternCount ( Field4 ; "Voted" ) > 0 )

The true false returns a 1 or a zero. If not, change to if( > 0 ; 1 ; 0 )

• ###### 5. Re: Numeric total of the occurrence of a word in a set of fields

So far none of the suggested solutions have worked, returning "0" as the solution despite there clearly being cases that should be "true". I want to try the last suggested option by Jack Rodgers, but I'm being really dense and not getting the exact syntax that FileMaker Pro expects. Could someone please spell it out for me?

(PatternCount ( Election1 ; "Voted" ) > 0) + (PatternCount ( Election2 ; "Voted" ) > 0 ) +( PatternCount ( Election3 ; "Voted" ) > 0 ) + ( PatternCount ( Election4 ; "Voted" ) > 0 )

Sorry to be such a Filemaker newb!

Roy

• ###### 6. Re: Numeric total of the occurrence of a word in a set of fields

There is no fundamental difference in the two methods: Jack's will directly return a true or false condition; the one I posted would have then been included in a simple IF statement to see if it was greater than zero.  I wanted to make it as simple as possible to see the workings of it, especially as there was the possibility I had misunderstood your question.

The syntax in both cases is exactly as posted; just replace Election1 or Field1 (etc) with the correct field names.

• ###### 7. Re: Numeric total of the occurrence of a word in a set of fields

Well, neither one works - they both return "0" despite how many fields have the word. To be specific, here is the exact use case. I have four fields which can contain various text strings. If a field among these four has the string "Voted" in it, I want to count that as "1", which should then contribute to a total of anywhere from "0"(min) to "4" (max). This doesn't strike me as logically difficult, but as you can see getting Filemaker Pro to do it is. Thanks a lot for your suggestions!

• ###### 8. Re: Numeric total of the occurrence of a word in a set of fields

Are the fields of type text or number when you check the types in Manage | Database | fields? They should be of type text. The patterncount function does work as Jack and Sorbsbuster have described here so we need to figure out why it doesn't in your particular case.

• ###### 9. Re: Numeric total of the occurrence of a word in a set of fields

Yes, the fields are all type "Text" and FWIW they are indexed. The field where I'm hoping a total of 0-4 appears is a calculation field. Using either Sorbsbuster's or Jack's formulation produces a zero every time, even when it should be a 1, 2, 3, or 4. I'm mystified.

• ###### 10. Re: Numeric total of the occurrence of a word in a set of fields

Compare your file to this working demo (In .fp7 format. If you are using version 12, it should convert cleanly.): http://www.4shared.com/file/sjhW83fR/VotedCounter.html

• ###### 11. Re: Numeric total of the occurrence of a word in a set of fields

I am SUCH an idiot!!!! I was trying to evaluate the WRONG FIELDS. I'm SO SORRY to have wasted your time. Jack and Sorbsbuster had it right all along. Sheesh. Sorry folks, and thanks for hanging in there with me until I GOT A CLUE.

• ###### 12. Re: Numeric total of the occurrence of a word in a set of fields

And thanks A LOT to PhilModJunk for posting the working demo. That was the necessary bit to make me realize something was seriously wrong with what I was trying to do.

• ###### 13. Re: Numeric total of the occurrence of a word in a set of fields

Obviously something is terribly wrong on your end if my suggestion doesn't work...  :)

First veryify you are using the correct spelling...  :)

If (patterncount ( fieldx ; "yourword" ) > 0 ; 1 ; 0 )

This will return a 1 if one or more instances appear in the field. I thought what I suggested returns a 1 or zero but maybe it returns 'true' or 'false' which doesn't help at all.

The revised IF returns either a one or a zero, for sure, which is what you want. You can plug it into my suggestion.

• ###### 14. Re: Numeric total of the occurrence of a word in a set of fields

Jack,

in FileMaker expressions, a boolean value that returns True will evaluate as 1 and False will evaluate as zero so your assumptions were correct.