5 Replies Latest reply on Dec 29, 2016 2:15 PM by siplus

    Sorting Records


      I currently have an issue with such a big delay when I enter a layout with 300+ records and wait for them to be sorted.

      At first, it was taking 20 seconds to sort.


      My Sorting was like this:





      I have the sorting like this now:





      The above sorting cut my sort times to 6 seconds.



      I keep reading that Filemakers issue when sorting records is that it's done on the client side, which requires the client machine to download all records. Hence, why once they are downloaded the first time, sorting after that is almost immediate.


      Is there a way to make this go faster?

      Is there a way to create a sorting table which sorts minimal fields and then is displayed in the proper layout already sorted?


      Imagine if I had thousands of records? It would be a nightmare.


      The records that are being displayed have Container Fields, Text Fields, Date Fields, Calculated Fields (unstored) and text fields.


      Thank you all for your help.

        • 1. Re: Sorting Records

          There's much to say to your post, unfortunately.


          1) Imposing a n sec penalty (in your case n = 30 but anything with n > 0.4 belongs to the dark side) on each and every user entering a specific layout because YOU decided that it's useful is bad.


          Doing related field sorts is bad per se - if sorting is really important you can consider a lot of alternatives.




          staying in the original table, doing the sorts and displaying related data.




          using ExecuteSQL to retrieve related data IDs with a ORDER BY clause and setting a global to the result, a global governing what gets displayed via a relationship.




          people on this list can do wonders if they get a copy of your database together with your needs, so that's the first option I ask you to evaluate.

          • 2. Re: Sorting Records

            The reason for the huge hit is because, as siplus mentioned, you're using related fields to sort. When you do that, not only do the records in the current table have to be downloaded, but also all the records in all the related tables (the ones being sorted on). In your case, based on what you've written, you're sorting on three related tables - which means you have to download all the associated records in all three related tables, plus the current one. This will perform poorly, especially as record counts climb.


            One common strategy for eliminating this issue is to de-normalize the table by copying the related data (just the fields you want to use for the sort) from the related table into the current one. This can be done via lookup or auto-enter calculation. You would also have to mind when the related data are changed and implement some method for keeping those locally copied fields up to date. Script Triggers and triggered auto-enter calculations are two methods.

            • 3. Re: Sorting Records

              I want a T-Shirt as follows:


              On the front:




              on the back:


              "IN FILEMAKER."

              • 4. Re: Sorting Records

                siplus wrote:

                I want a T-Shirt as follows:

                You're telling us now? Take a look at the calendar, why don't you?

                • 5. Re: Sorting Records

                  you still got time, my  birthday is on January 9th