6 Replies Latest reply on Apr 10, 2012 1:02 PM by philmodjunk

    Multiple Criteria Sort doesn't work properly



      Multiple Criteria Sort doesn't work properly


      I am using Filemaker Pro 11 and have a database ending in .fp7. It was originally created in fp5. I have done this kind of multiple criteria sort a million times with this database, but after upgrading to FM 11 the 2nd criteria doesn't work correctly. A single sort does fine, but when I add the second factor, several of the items in that list are out of order. Most of them are fine, but there are several names in each category that are not alphabetical, and they are at the end of the list. Any ideas what's wrong?

        • 1. Re: Multiple Criteria Sort doesn't work properly

          Can you post a list illustrating the items that aren't sorting as expected?

          A few observations that may help:

          .fp7 is the correct file extension for all files created/converted by Filemaker versions 7 through 11. The file format hasn't changed since version 7.

          Text fields index differently in newer versions of Filemaker than file maker 5. Punctuation characters that were ignored in version 5 are indexed in Filemaker 11 and this could affect sort order.

          It's possible for a field's index to become corrupted finds and sorts that fail to produce the expected results are a common indicator of this problem.

          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: Multiple Criteria Sort doesn't work properly

            None of that worked, but I figured it out. It turns out that the primary criteria had some entries with a TRAILING SPACE that I couldn't see without clicking in the field. So the database was creating 2 criteria: example - member followed by memberSPACE. The entries in memberSPACE had correct alphabetical order in the subcriteria. Problem solved.

            • 3. Re: Multiple Criteria Sort doesn't work properly

              I am new to Filemaker, but have worked just like you on other vairous databases. I am too having the same issue, but I didn't understand how you managed to fix the problem. I work for a small engineering company and we need to sort first by date paid then by job date to create a report. anyother suggestions?

              • 4. Re: Multiple Criteria Sort doesn't work properly

                Trailing spaces should not be an issue in fields of type date. When you examine these fields in Manage | Database | Fields, are they defined as type "date"?

                • 5. Re: Multiple Criteria Sort doesn't work properly


                  Phil, you are correct, but I cannot get them into the correct order. can you help?  I need them sorted so I only them sorted very specifically.

                  • 6. Re: Multiple Criteria Sort doesn't work properly

                    The following works in both FileMaker 12 and also previous versions:

                    Select Sort Records... from the Records menu to open the Sort Records Dialog.

                    Click "clear all" if it is not greyed out.

                    Select your date paid field by clicking in on the left hand side, then click Move. The default sort order option is ascending so you should not need to change it, but you can click the field in the right hand list and then click an option if you need to.

                    Now select your job date field and click move to add it to the list beneath date paid.

                    Click Sort and your records will now be sorted first by date paid and then within each date for date paid, they'll be further sorted by job date.


                    Date Paid    Job Date  (dates are mm/dd/yyyy)
                    4/1/2012     3/5/2012
                    4/1/2012     3/31/2012
                    4/2/2012     2/1/2012
                    4/2/2012     2/28/2012
                    4/2/2012     3/6/2012

                    In FileMaker 12, I've read that you can do a multi field sort directly on a table view layout, but I haven't tried that yet...

                    Such sorts can also be scripted to make the sorting happen automatically or when a button is clicked.