11 Replies Latest reply on Mar 22, 2013 9:35 AM by philmodjunk

    Slow sorting when using calc fields

    EP

      Title

      Slow sorting when using calc fields

      Post

           Layout: Customer names in a list view

           I have a heading that has Sort buttons.  One of my buttons sorts by Amount of orders placed.  This would be easy to set up, however I want to filter out orders that were canceled.  I have 2 summary fields. 1-SumOrders and 2-SumOrdersCanceled.

           I tried setting this up on the layout using a portal with SumOrders in it and a filter to filter out canceled orders.  Problem is, when I click the button to sort by Orders, it doesn't take into account the canceled orders, so the sort is incorect.  I then created a calc field, cSumActualOrders: SumOrders - SumOrdersCanceled.

           Now I can sort by the calc field and it is accurate.  However, sorting is now slow and no longer "instant."  

           Any other methods I can employ?

           Thanks

        • 1. Re: Slow sorting when using calc fields
          philmodjunk

               The fact that you are sorting on a calculation field will not affect the sorting time. The fact that you are sorting on an unstored calculation field will add significantly to the amount of time it takes to sort the records.

               Can you describe your set up in more detail. I'd need to have a clear picture of both your relationships and your layout design before I can suggest any alternative approaches.

               Also, what version of FileMaker do you have? If you have version 12, we might be able to do something with ExecuteSQL().

          • 2. Re: Slow sorting when using calc fields
            EP

                 Patients::_PKPatientID---<Cases::_fkPatientID

                 Contacts::_PKContactID----<CasesReferred::_fkReferralID (this is a table occurrence of Cases)

                 When a new case is generated for a patient, the user selects the referrer from the contacts table.  This enters the Contacts Serial ID into the _fkReferralID field in Cases.  

                 When a user needs to "cancel" a case for various reasons (I do not allow deletion of records in the database), they click a button followed by a prompt and script, which sets Cases::Canceled = "Canceled"

                 In some situations, I filter my portals with the following expression: ...Visible when: Cases::Canceled ≠ "Canceled"  so my sum fields are accurate and show the count of actual cases that werent canceled.  But for 

                 I use a sum field to display the amount of cases referred in a given time period, etc.  No problems here.

                 The problem I am having is when trying to create a sort in list view that recognizes the count of _fkCasesReferred or even _fkPatientID for a Contact while subtracting the count of Cases::Canceled = "Canceled"

                 ---------------

                 I use FMP12 Advanced

            • 3. Re: Slow sorting when using calc fields
              philmodjunk

                   This sounds like something you can set up as a summary report. Such a report would completely avoid any of the issues slowing down your current layout.

                   You can base such a layout on an occurrence of the Cases table. You'd perform a find on this layout to omit all canceled records. The same find can specify other criteria such as a date range.

                   On this layout, a summary field can count the number of records. If you put it inside a sub summary layout part "when sorted by" _fkReferralID, you'll get a sub total showing the number of referrals for each referring contact that were not canceled. If you don't want to list the individual cases in this report, you can remove the body layout part.

                   Two key details:

                   The records must be sorted by the "when sorted by" field or the sub summary part will not be visible.

                   The records must be viewed in list view.

                   Here's a thread with a lot of scripted find examples that you may find helpful: Scripted Find Examples

                   And here's a tutorial thread on Summary Reports: Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Slow sorting when using calc fields
                EP

                     Phil thanks for this.  Problem is, I use the data on Dasboards as well as the list view layout. I guess I can integrate this into my existing list view layout, but it looks like I am stuck as far as the portals on the dashboards go.  I use a portal to display the "Top Referrers" at a glance

                • 5. Re: Slow sorting when using calc fields
                  philmodjunk

                       I suggested a summary report based on very limited information in your original post. It's almost always my first suggestion because it is more generally applicable to many different needs. But it is not the only option.

                       Please describe what you need to see and on what type of layout. A "summary recap" of this same data could be displayed in a large calculation field equipped with a scroll bar by using ExecuteSQL().

                  FMP 12 Tip: Summary Recaps (Portal Subtotals)

                  • 6. Re: Slow sorting when using calc fields
                    EP

                         Basically, I want to display which cntacts referred the most cases.  I will use this data in portals sorted in descending order.  I also have a list view of contacts, where the user can click on different headings to sort the contacts by: Zipcode, Name, # of cases referred, etc.

                         The portals appear on Dashboard layouts and the user is able to scroll up and down in the portal to view the data and click on portal rows to be taken to the contacts record

                    • 7. Re: Slow sorting when using calc fields
                      philmodjunk

                           And the unstored # of cases referred field will represent a potential "peformance hit" as you are sorting on an unstored calculation field.

                           You might experiment with script triggers that update a simple number field in contacts each time a referring contact is selected and each time a Case record is cancelled. This then gives you a stored/indexed field for faster searches and sorts, but now your user interface must be very carefully designed to make sure that errors do not creep into this number field.

                      • 8. Re: Slow sorting when using calc fields
                        EP

                             Thanks Phil.  What you suggest is normally what I do with date fields (I use a set field scipt to set the most current date of a related record in the parent table).  Sorts become instant when sorted by the field in the parent table.  I need to get clever and figure out a way to implement what you uggested if it is the best way.  For now, I am just going to sort by the sum field of  _fkCasesReferred but disaply the calc field in the portal/layout.  The calc field will be accurate (cases- canceled) but the actual sort will be slightly off at times.

                        • 9. Re: Slow sorting when using calc fields
                          philmodjunk

                               If you have these relationships

                               Cases>----referringContacts-----<Cases 2

                               From a layout based on cases....

                               Set Field [ ReferringContacts::ReferralCount ; Count ( cases 2::UncanceledFlag ) ]

                               coudl be used to update a number field, ReferralCount each time a Referral contact Id is selected or the case record is canceled.

                               UnCanceledFlag can be a calculation field that returns a value only if the case record is not canceled.

                          • 10. Re: Slow sorting when using calc fields
                            EP

                                 I set up the ReferringContacts::ReferralCount and  cases 2::UncanceledFlag fields.  I think I incorrectly set up the UncanceledFlag field calc (If ( CancelCase ≠ "Canceled" ; 1 ; 0 ) I think it should be If ( CancelCase ≠ "Canceled" ; 1 ; "" ) instead.  

                                 I am trying to understand the concept here before I add the Set Field function to my scripts.  If a user cancels a case, shouldn't UnCanceledFlag return -1 instead of zero or no value at all?

                                 Thanks

                            • 11. Re: Slow sorting when using calc fields
                              philmodjunk

                                   Count counts the number of related records where the field is not empty. So if you use Count, the flag field should be empty for canceled case records.

                                   You could use Sum instead of count, but then the value for canceled cases would need to be 0 or empty.

                                   -1 would not work in either case.