5 Replies Latest reply on Nov 16, 2011 10:54 AM by philmodjunk

    Faster Find Option



      Faster Find Option


      Hi Ive been working on a project and it seems I just can't seem to get the results thats I am looking for simply because Im sure I'm not using filemaker to its best. However at the moment I now have a database with over a million records and 100 fields (ALL Indexed). I tried the "perform find" feature but it gets stuck at 95 records found and just keeps searching, I waited over 3 days with nothing to show except its helplessl attempt in trying to contiune to perform the find.

      I would like to know if there is some way to use a script or portal or something to run a faster find on such a large database.

        • 1. Re: Faster Find Option

          What criteria did you specify for your find?

          The index of one or more fields in your database could be damaged. That's sometimes the explanation when a straight forward find or sort does not work as expected.

          To rebuild the index of a single field:

          1. Open Manage | Database | Fields and double click the field
          2. Use either the storage tab or the storage options button to turn off indexing.
          3. Exit Manage | Database, then return and turn indexing back on.


          You can also rebuild all your file's indexes by importing all the data into an empty copy (clone) of your file.

          If you have FileMaker 11, you can use Advanced Recovery options to rebuild your file's indexes:

          1. With the file closed, select Recover from the File Menu.
          2. Select "Use advanced Options"
          3. Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
          • 2. Re: Faster Find Option

            There was one "OR" criteria set for each field each criteria being a specific value  

            • 3. Re: Faster Find Option

              Please describe that in more detail.

              How did you implement the "Or"? Separate Requests? Extend Found set?

              How many different fields?

              Can you give an example of the actual criteria you might use in such a find?

              • 4. Re: Faster Find Option

                The found was like this 

                Field 1 - Omit value =5

                New Request

                Field 2 - Omit value = 10

                New Request 

                Field 3 - Omit value = 4 


                This was done for abiut 100 fields 

                • 5. Re: Faster Find Option

                  Definitely sounds like an approach that will bog filemaker down when you have very large numbers of records and fields. Omit requests run through the entire found set (all the records in your table when the find is nothing but omit requests) omitting records with the specified criteria and you are doing this for 100 fields so the first request starts with all the records in your table, the next request starts with the remaining records and omits any remaining records matching the second criteria and so forth...

                  Can you tell me more about this table and the purpose to this find? (Maybe we can come at it differently by specifying the values you want to keep instead of the values you want to omit.)