10 Replies Latest reply on Feb 16, 2009 11:29 PM by davidhead

    Look for and count the occurrence of a specific word in a field

    HalMac

      Title

      Look for and count the occurrence of a specific word in a field

      Post

      I'm looking for a way to search a large text field for a given word, which would be variable, and count the number of occurrences of that word in the field.

        • 1. Re: Look for and count the occurrence of a specific word in a field
          ErichWetzel
            

          Try the PatternCount(text;searchString) function in a calculation field.

          Text is the field you want to check the count in.

          searchString could be a global field which you enter the searched word to count into.

          The function returns the number of times the selected word appears.

           

          Examples:

          Table::textfield = hello Tom

          Table::globalsearchstring = hello

          Table::wordcount = PatternCount(Table::textfield ; Table::globalsearchstring)

          Table::wordcount will show 1 

           

          Table::textfield = hello Tom goodbye Tom

          Table::globalsearchstring = Tom

          Table::wordcount = PatternCount(Table::textfield ; Table::globalsearchstring)

          Table::wordcount will show 2 

           

          Does that do what you need?

          -Erich 

          • 2. Re: Look for and count the occurrence of a specific word in a field
            comment_1
               Note that a 'string' is NOT the same as a 'word':

            PatternCount ( "Tom stomped on an atomic bomb" ; "tom" ) = 3

            If you want to count matching words only, you will likely need to use a recursive custom function (unless you know for certain which punctuation marks can be found in the text).

            • 3. Re: Look for and count the occurrence of a specific word in a field
              HalMac
                 I'll need a "recursive custom function" (whatever that means), as I'll need matching words only. Would you explain?
              • 4. Re: Look for and count the occurrence of a specific word in a field
                comment_1
                   Which version do you have? You need Filemaker Advanced (formerly Developer) in order to create/install a custom function.
                • 5. Re: Look for and count the occurrence of a specific word in a field
                  HalMac
                     FileMaker Pro 10 Advanced.
                  • 6. Re: Look for and count the occurrence of a specific word in a field
                    davidhead
                      

                    You can create a custom function such as:

                     

                    /*

                    PatternCountWord ( text; searchstring )

                    Version: 1.0

                    Author:   David Head, uLearnIT

                    Purpose: counts the number of word occurrences of searchstring in the text

                    Inputs:

                    text = the string to be searched

                    searchstring = the word being searched for

                    Output: count of the occurrences of searchstring as a word in text

                    Example: PatternCountWord ( "His cat was catalogued in the cat catalog." ; "cat" ) = 2

                    */

                     

                    If ( WordCount ( text ) > 0 ; 

                         If ( LeftWords ( text ; 1 ) = searchstring; 1; 0 ) + 

                         PatternCountWord ( RightWords ( text; WordCount ( text ) - 1 ); searchstring )

                       )

                     

                    This is a recursive custom function - it calls itself with progressively shorter strings until all words have been processed. You are using the FileMaker Pro definition of a "word". 

                     

                    • 7. Re: Look for and count the occurrence of a specific word in a field
                      HalMac
                         I copied your information, verbatim, but I can't get it to work. The formula only returns a value of "1" for the very first word even if that word appears more than one time in the text block. It always returns a value of "0" for any other words in the text block.
                      • 8. Re: Look for and count the occurrence of a specific word in a field
                        davidhead
                           When you use the custom function in a calculation, what are you writing? It seems like you may be passing a word where the field should be? Can you post your calculation?
                        • 9. Re: Look for and count the occurrence of a specific word in a field
                          HalMac
                            

                          My database has three fields:
                          Text (a text field that holds the text I want to examine)
                          Count (a calculation field that holds the formula for calculating the occurrence of a specific word)
                          SearchString (a text field that contains the word I wish to find)
                           
                          The Count field formula is:

                          If ( WordCount ( Text ) > 0 ; If ( LeftWords ( Text ; 1 ) = SearchString; 1; 0 )

                          + PatternCountWord ( RightWords ( Text; WordCount ( Text ) - 1 ); SearchString ))

                           

                          PatternCountWord is defined as ( text ; searchstring ) 


                          • 10. Re: Look for and count the occurrence of a specific word in a field
                            davidhead
                              

                            HalMac wrote:

                            My database has three fields:
                            Text (a text field that holds the text I want to examine)
                            Count (a calculation field that holds the formula for calculating the occurrence of a specific word)
                            SearchString (a text field that contains the word I wish to find)
                             
                            The Count field formula is:

                            If ( WordCount ( Text ) > 0 ; If ( LeftWords ( Text ; 1 ) = SearchString; 1; 0 )

                            + PatternCountWord ( RightWords ( Text; WordCount ( Text ) - 1 ); SearchString ))

                             

                            PatternCountWord is defined as ( text ; searchstring ) 


                            OK, so Text and SearchString fields are fine.
                            It seems you have defined PatternCountWord as a custom function. Should be OK if you simply copied and pasted the code given. You are using the two parameters.
                            It seems the problem lies with the Count calculation field. It is a mixture of the custom function and the custom function expression. It should be simply:
                               PatternCountWord ( Text; SearchString )
                            So you are passing the fields into the function. The custom function will perform the required recursion to count the instances of the searchstring. 
                            If this works, fine. If not, can you tell me how you have defined the custom function?