4 Replies Latest reply on Aug 18, 2016 9:56 AM by philipHPG

    List-View find performance

    jdevans

      I have a List View on which my users are asking for a field to be included. The layout is based on Drawings.

       

      The field called "latest_rev" is on the Drawings table, and its calculation is-  Last(DrawingRevisions::rev_letter). This is so I can show the latest revision letter of a given drawing number.

       

      The relationship is like so:

      Drawings>---<DrawingRevisions

       

      Is there a better/faster way to get this Last(DrawingRevisions::rev_letter) stored in the Drawings table so that the find will be just as fast as other fields based on the parent Drawings table?

       

      I tried to do a second field in Drawings which is an Auto-Enter Calc that has as its calculation: = Drawings::latest_rev. But it doesn't seem to make any performance improvement.


      Suggestions greatly appreciated.

        • 1. Re: List-View find performance
          philipHPG

          The other option is to use a script trigger when a new DrawingRevision is made that updates the corresponding value in the Drawing table. In this way the field in Drawing is a static, indexable field, and no longer dependent on a related table.

          1 of 1 people found this helpful
          • 2. Re: List-View find performance
            jdevans

            This sounds like a great idea and is just the sort of thing I was hoping to achieve. I'm going to give this a try and see how it goes.

            • 3. Re: List-View find performance
              jdevans

              Works like a charm. I actually discovered that I'd already created a script to do just what you suggested, but found out that in my script that does the find for the user was finding on a different field. That was really the essence of the problem. But your idea (when fully implemented) works well.

               

              I also create a second utility script to loop through all records, setting the static indexable field to the latest_revision value.

               

              And the other script is triggered on Save, that updates the static field whenever a drawing gets a new revision letter.

               

               

               

              I have a "filter-as-you-type" field for searching on that Revision Letter and before I made the change, it couldn't keep up with typing. Now it's very fast.

              • 4. Re: List-View find performance
                philipHPG

                Sounds like you have a good set-up.