4 Replies Latest reply on Jul 25, 2012 12:02 PM by BrandonTerrell

    Date Field Not Sorting Chronologically



      Date Field Not Sorting Chronologically


      There may be an obvious solution here, but for some reason when trying to sort my records in table view by a date field the order coming up is not chronological at all.  Attached is a screenshot of the wacky sort I am getting when done by date (descending).  The field is set up as a Date field, and I can't figure out any other reason why the sort would be coming out this way so any help would be appreciated.  Thanks!


        • 1. Re: Date Field Not Sorting Chronologically

          It's not even sorting like text or the June dates would list before the others shown here.

          I wonder if the field's index is corrupted.

          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 or newer, 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: Date Field Not Sorting Chronologically

            Thanks for the quick suggestion!  I tried rebuilding the index of just the field and of the entire file via the Advanced Recovery, and neither had any affect on the sorting.  I have no idea what might be happening here and can't even find any kind of pattern in the sort it is producing to figure out what it might be doing.  Any other ideas of what I might could try to resolve this?  Sorting by any other field (entry number, client name, value) all work just fine, it is only the date that is having issues.


            EDIT:  Just on a hunch I converted the field type to a Timestamp and sure enough that immediately fixed my sorting issues.  However now it is displaying the dates only in numerical format MM/DD/YYYY and adding 12:00 PM to them all.  Is there any way I can force this to display only the date in the format I originally had, or even better to easily remove the Time component and then change it back to a Date field that hopefully would sort properly and could be displayed in the format I want?  I have tried adjusting the data appearance options in the layout inspector but can't seem to find a setting that forces the time to not be shown.  Thanks again!

            • 3. Re: Date Field Not Sorting Chronologically

              What happens if you now change the field back to date?

              Or use Replace field contents with the calculation option and the GetAsDate function to copy the date into a new field of type date?

              • 4. Re: Date Field Not Sorting Chronologically

                Excellent! Running a Replace Field Contents to convert them all to dates only and then changing the field type back to a Date worked perfectly.  I have no idea how I managed to get that happen, but thank you very much for helping me solve this so quickly!