10 Replies Latest reply on Jan 21, 2014 9:02 AM by bobrace

    A find based on multpile words in a field

    bobrace

      I am trying to do a find on a field that has multple words in it.  Example:

       

      Field is Payee

       

      Data in in this field could be "Nicor payment 100000011" (or a variation of that) and another record could contain "DEPT education ACH transaction" (or a variation of that) in the "Payee" field.

       

      How do I perform a find that would return both of these records? So, another words... is there a way to specify multiple search criteria by going into find mode and in the Payee field, find all records that contain the words "Nicor payment 100000011" or "Nicor GAS Energy Direct Payment 12345698", but also find records that contain "DEPT education ACH transaction" or "department of education 234532".

       

      Thanks

      Bob

        • 1. Re: A find based on multpile words in a field
          erolst

          You want to perform an OR search (i.e. “show me records where my field contains either these words OR that words …").

           

          To do this manually, go to Find mode, enter your first set of search terms, then select New Request, which gives you a new, empty form (or record or row, if you're in List or Table view) where you can enter the next set of search terms; continue until you have entered all your search term sets, then perform the search. (Note that you could also use individual Finds and the Extend Found Set command to achieve the same result.)

           

          Using

           

          Enter Find Mode

          Set Field [ YourTable::yourField ; yourFirstSearchTerm ]

          New Record/Request

          Set Field [ YourTable::yourField ; yourSecondSearchTerm ]

          etc.

          Perform Find

           

          you can also do this in a script.

          • 2. Re: A find based on multpile words in a field
            bobrace

            Thank you for your response.  I understand the "OR" searches and that makes sense, but is there a way to base a find on a set of phrases in a text file with separators, wildcards, using commas to separate such as:

             

            *123cashdepot*, *171stharlemcurre*, *500fastcash*, *94004 mega*, *ab secweb*, *accu payment*, *advance paycheck*, *advanceamerica*, *aeis*, *afty?lifel*, *allmerica pms*, *ameriloan*, *ameristar east*, *apex clearing*, *aps*, *ars*, *bcsf*, *bdpdl services*, *bdpdl*, *bhn tampa bay*, *bill me later*

             

            So the user could open "Find", copy the above text out of a text file, paste it into the "Payee" field (for the search criteria) and have it find all records that match those phrases or words.  This way the text file can be update as needed and when the user needs to perform another find, it would pick up the new criteria (words).  Or can a "Find" be pointed to use a text file for its criteria?

             

            Thanks,

            Bob

            • 3. Re: A find based on multpile words in a field
              erolst

              Turn the search terms into a list and process it within a loop to generate a number of search requests:

               

              Set Variable [ $searchList ; Substitute ( "*123cashdepot*, *171stharlemcurre*, *500fastcash*, *94004 mega*, *ab secweb*, *accu payment*, *advance paycheck*, *advanceamerica*, *aeis*, *afty?lifel*, *allmerica pms*, *ameriloan*, *ameristar east*, *apex clearing*, *aps*, *ars*, *bcsf*, *bdpdl services*, *bdpdl*, *bhn tampa bay*, *bill me later*" ; ", " ; ¶ ) ] // create a list from your field – or sample data, in this case

              Set Variable [ $termCount ; ValueCount ( $searchList ) [

              Enter Find Mode

              Loop

                   Set Variable [ $i ; $i + 1 ]

                   Set Field [ YourTable::yourField ; GetValue ( $searchList ; $i ) ]

                   Exit Loop if [ $i = $termCount ]

              End Loop

              Perform Find

               

              If you have or can calculate the path to a text file, you can import its contents into a field, then use that field as shown above.

              • 4. Re: A find based on multpile words in a field
                bobrace

                Thank you.   I will give that a try.  Should I be leaving the "commas" and the "astericks" in the list?

                 

                Thank you again for your help.

                Bob

                • 5. Re: A find based on multpile words in a field
                  coherentkris

                  The call to substitute changes the commas + singlespace to crlf (pilchrow) so they should be left. The asterisk are a find function and should also be left

                  • 6. Re: A find based on multpile words in a field
                    bobrace

                    Thank you for the clarification and the reply.

                    • 7. Re: A find based on multpile words in a field
                      bobrace

                      As I mentioned, I am new to Filemaker Pro and I need a little assistance.  Could you please tell me exactly how I would set this up?  Does part or all of this go into a script?  Where do I actually write this code?  A step by step guide would be helpful?  Thank you so much for all your help.

                      • 8. Re: A find based on multpile words in a field
                        bobrace

                        Thank you again for giving me a start with this script.  I was able to get it set up, but when I run it, it only finds all occurences of the last item in the list (in the example above "Bill Me Later"). I know there are other items that it should find.  I also know I have this setup identical to what you suggested above (I have gone over your script with with a fine tooth comb). Would you have any idea why it is going through the whole list, but only looking or using the last vaule in the list?

                         

                        Thank you

                        • 9. Re: A find based on multpile words in a field
                          erolst

                          I apologize for giving you an incomplete script and wasting your time; that happens when one codes from memory …

                           

                          You are only finding the last term because that's all the script is looking for, since the loop doesn't add more search requests. Please insert the line in bold and try again.

                           

                          […]

                               Exit Loop if [ $i = $termCount ]

                              New Record/Request

                          End Loop

                          Perform Find

                          • 10. Re: A find based on multpile words in a field
                            bobrace

                            No apologies necesary, are you kidding, I need to apologize to you for being a bother.  Unfortunately, I'm somewhat new to FMP and not yet familiar with scripting.  But,your suggestion of adding "New Record/Request" did the trick.  It works.  Thank you again for helping me out,

                            Bob