1 2 Previous Next 28 Replies Latest reply on Dec 14, 2012 10:13 AM by philmodjunk

    Slow Sorting

    Epa

      Title

      Slow Sorting

      Post

           Each heading is a button that sorts records.  When clicked, the sorting occurs instantaneously for all buttons EXCEPT "LAST CONTACT."  As you can see, it is a portal on the layout and I suspect that is the reason.  A box pops up "Sorting: Records x of 184" and it take 10-20 seconds to sort.  Is there any way to speed this up?  Thanks

      Snapshot_10%3A16%3A12_1%3A10_PM.png

        • 1. Re: Slow Sorting
          philmodjunk

               The portal is not a factor, but the relationship on which the portal is based could be.

               What kind of fields are specified in the sort order when the Last Contact header is clicked?

               Are they defined in the same table as the current layout's table or from a related table? (I would guess a related table.)

               In storage options for the field, is it stored or unstored, indexed or unindexed?

          • 2. Re: Slow Sorting
            Epa

                  

                 What kind of fields are specified in the sort order when the Last Contact header is clicked?

                 Date fields from a portal that can be found within each contacts record.  I have a portal that contains a comments field (text field) that has a date field to indicate when the comments were typed.  

                 Are they defined in the same table as the current layout's table or from a related table? (I would guess a related table.)

                 Related, as described above.

                 In storage options for the field, is it stored or unstored, indexed or unindexed?

                 Indexing is on,

            • 3. Re: Slow Sorting
              philmodjunk

                   Is there a lot of records in the contact history table?

                   There may not be any easy way for you to avoid this.

                   One option that comes to mind is to use script triggers to update a "last contact" date field in the parent table any time you add/modify data in the portal to contact history. That would allow you to specify this new field in place of the field from the related table in your sort order and it should be much faster.

              • 4. Re: Slow Sorting
                Epa

                     Acutally you got me thinking.  What if I use a Set Field function to set a field on the parent table to match the "contact date" of the comments in the portal?  But would I be able to make sure the Date that gets used is the date in row 1 of my portal?  If so, maybe I could set a field on the parent table to reflect the second row of the prtal and so on (I currently have the last 3 contact dates showing in a 3 row portal on my layout)

                • 5. Re: Slow Sorting
                  philmodjunk

                       That's exactly what I suggested in my last post. YOu can use a layout based script trigger such as OnCommitRecord to perform a script that uses set field.

                       When you refer to data in a related table such as with the set field script step, the first related record (very possibly the first record in your portal) is the record that will supply the data. It depends on whether you specified the sort order for your portal in portal setup or at the relationship level. This method is simplest to implement if you specify the sort order in the relationship instead of in the portal setup dialog.

                  • 6. Re: Slow Sorting

                         A bit late...it appears to me that you have a portal on each record line?

                         I can sort records based on related fields but how could I sort records based on a portal? This occurs when I create reports. However, most of these related records are the only record which makes it easy.

                         How would we determine the order if there were many related records in the portal? Wold the portal have its own sort order (it does automatically).

                         Typically the idea of 'Last Contact' would be a field that is filled with data via a script....

                    • 7. Re: Slow Sorting
                      philmodjunk

                           @Jack,

                           Presumably this is a portal with a sorted relationship that puts the most recently added portal record first in the portal. A sort order for the layout that specifies this field will specifiy the value of the first related record. So the records would then, slowly, sort by date of most recent contact.

                      • 8. Re: Slow Sorting
                        Epa

                             Created a script: Set Field [Contacts::Activity; Contact History::Contact Date]

                             It sets the field upon committing the portal field, however it seems to not "update"  For example, if I delete the related record dated 10/19/12, it still shows 10/19/12 in my Contacts::Activity field, even though the date at the top of my portal is 10/17/12 (for the latest comment).

                        • 9. Re: Slow Sorting
                          philmodjunk

                               I suggest using OnCommitRecords set on the parent table instead of the portal's. Deleting a portal record doesn't trip the OnCommit trigger for the portal table.

                          • 10. Re: Slow Sorting
                            Epa

                                 But if I'm pulling the data from the portal field, how can I do this?  I added the trigger to the layout (OnRecordCommit).  

                            • 11. Re: Slow Sorting
                              philmodjunk

                                   I had assumed that you were deleting from a layout based on the portal's table rather than from the portal itself.

                                   Do you use a sorted portal or a sorted relationship to list the most recent entry first in the portal?

                                   In order for your set field step to access the correct portal record, you need for the sort to be specified for the relationship. Either that or you use use the max function to access the maximum date of all the related records:

                                   Set Field [Contacts::Activity; Max ( Contact History::Contact Date ) ]

                              • 12. Re: Slow Sorting
                                Epa

                                     Ok, I was using a sorted portal. 

                                • 13. Re: Slow Sorting
                                  Epa

                                       I used the MAX function and it seems to be updating the date on the parent table whenever changes are made, except when a related record is deleted from the portal (it will not change the field on the parent table)

                                  • 14. Re: Slow Sorting
                                    philmodjunk

                                         That action probably isn't tripping the script trigger. I suggest adding a delete portal row button that deletes the portal row and includes either the same set field step or that uses perform script to perform the trigger's script.

                                    1 2 Previous Next