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

    Sorting Records

    JulioSandoval

      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:

      related_table::name

      related_table2::name

      related_table3::name

       

      I have the sorting like this now:

      related_table::name

      related_table2::name

      related_table3::tableID

       

      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
          siplus

          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.

           

          LIKE

           

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

           

          OR

           

          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.

           

          BUT

           

          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
            Mike_Mitchell

            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
              siplus

              I want a T-Shirt as follows:

               

              On the front:

               

              "I DENORMALIZE FOR A LIVING"

               

              on the back:

               

              "IN FILEMAKER."

              • 4. Re: Sorting Records
                erolst

                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
                  siplus

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