3 Replies Latest reply on Jan 8, 2009 3:55 PM by johnhorner

    Find on 3 or more fields



      Find on 3 or more fields


      I'm trying to perform a find on several fields. What I have is a database of songs on file and want to enter into one field for example "Kenny" and have the search look in the "Song Name", "Artist" and "Album" fields. I am using FMP 8 Advanced. I have searched the forum and none of the things I found seem to work. I would say I am a beginner that has been using FMP for 10 years or more. Any ideas much appreciated. 

        • 1. Re: Find on 3 or more fields

          The search field you need to use will be a calculated concatenation of the three fields in question:


           = Song Name & " " & Artist & " " & Album


          Search that field and you will return records that match for any of the three fields. 

          • 2. Re: Find on 3 or more fields

            Thanks I was trying it from the wrong direction. Trying to combine the search instead of a new field to search in.


            • 3. Re: Find on 3 or more fields

              i see that you already received a good solution from ulearnit.  i also wanted to offer a slightly different solution. instead of creating a new field calculation field, you could create a single global field (e.g. "search criteria_global") that you would type your search criteria (e.g. "kenny") into.  you could then create a script to run that would go into find mode, place your search criteria into the first of the three fields you would like to look in (using the "set field" script step), perform the find, and then do the same in the other two fields except for these two fields rather than "perform find" you will use "extend found set so it will add to any records found in the other fields.  if you are using filemaker 10 you could also have it execute the search as soon as you hit enter using a script trigger.  anyway, here is a rough approximation of the script:


              enter find mode

              set field "song name":"search criteria_global"

              perform find

              enter find mode

              set field "artist":"search criteria_global"

              extend found set

              enter find mode

              set field "album":"search criteria_global"

              extend found set


              this solution might create less "overhead" (i.e. not calculating a field in every record) and might be a little faster searching (even though you are essentially executing three searches using the script, it is searching indexed records.  you can, of course, index the calculation field used by the other solution but it would then increase file size.  i suppose which method is more appropriate for your situation would depend on how large your database is, how often you need to search it, and whether file size vs speed is an important consideration.


              good luck!


              john h.