3 Replies Latest reply on Nov 23, 2009 4:35 AM by relayer

    Find search term across fields and produce found set



      Find search term across fields and produce found set


      I've asked this before and accepted an answer too quickly, so apologies and here goes again.  

      I have an old database in Microsoft Works.  The database was written by someone else and keywords one might use to search with may (or may not) be in any one of four fields. While Works is as old as the hills and clunky in many ways, the one great thing for my purposes is that I can type in a search term, click 'All records' and it will find and bring up all the records with that term whatever the field is.  

      I cannot see a simple way of doing this in FM.  I have to type the term in one field, search, then do it again in another field etc.

      Any ideas, anyone? 

      The answer I had been given was to use the Find/Replace option.  The problem with this is it only finds the next instance of a term.  It does not produce a found set. Perhaps I wasn't specific enough about this before.

      If the solution involves scripting please make your instructions idiot-proof as I know nothing about this and really don't want to have to spend hours learning how just for this one thing - its all I'll ever need the database to do. 

        • 1. Re: Find search term across fields and produce found set

          If you want a found set, you have two options, at least one of which was suggested in your original thread if I remember correctly.


          Option 1: No scripting required:


          Define a calculation field that concatentates all your fields into a single string and search it for keywords:

          cSearchText : Field1 & " " & Field2 & " " Field3...

          Place this field on your layout, enter find mode, type your keyword in this new field and peform the find.


          Option 2: No calculation field, script required:


          Define a field, gSearchText and use field options to set it up with the Global Storage Option


          Write a Script:

          Enter Find Mode[]

          Set Field [YourTable::Field1; gSearchText]

          New Record/Request

          Set Field [YourTable::Field2; gSearchText]

          New Record/Request

          Set Field [YourTable::Field3; gSearchText]

          Perform Find[]


          For more fields, simply add more New Record/Request--Set Field pairs of script steps.


          How it works, When you enter find mode--either by hand or in a script--Filemaker creates a special blank record called a "request" for you to enter search criteria. If you create several such "request" records before performing the find, Filemaker will find the records that match the criteria entered in request 1 Or Request 2 OR request 3...


          For those wishing to take this technique further, you can modify the above script to use a loop with Go To Next Field so that you can cycle through all the fields listed on a given layout without referring to any one field explicitly.

          • 3. Re: Find search term across fields and produce found set
               Thanks for that.  Option one works for me. Not very elegant perhaps, but it works.  I didn't bother with the scrip, though if I have time later I may pluck up the courage to explore it.