7 Replies Latest reply on Jun 6, 2017 6:15 AM by CarlSchwarz

    Summary fields - client or server - speed


      In a financial application I am summarising unbilled time in a matter record (law practice).  I have a summary field in the time database totalling all unbilled time but when working remotely it is incredibly slow as each matter in a client record pulls the data from the time database.  I am running FMPS.  The summary fields are viewed as single fields on a layout and also as multiple records in a portal.


      What is going to be quickest way to get the data, given that I am running FMPS?  I can use the traditional relationship way to view the summary field in the matter layout but that pulls down every record from the time database, I believe, to then summarise the data on the client. 


      All I need is the total so how do I in effect get FMPS to do the summary calculation and just send the total? 


      I am moving over to SQL queries so as to cut down on the ridiculous number of relationships I have.  Would an SQL query avoid the need to pull down all the data or is the query once again run out of the client so all the data has to be downloaded.


      This isn't an issue when on an internal network but working via a VPN on a slow connection makes the solution almost unusable.  Either that or I have to sacrifice a data rich layout. 


      Any pointers would be appreciated.

        • 1. Re: Summary fields - client or server - speed

          You can use "Perform script on server" to set a field to the summary amount when you need it, for example when navigating to the report page.

          That will be fast.

          • 2. Re: Summary fields - client or server - speed

            That would certainly work in a form but not for portals with summary fields.  Good idea though.

            • 3. Re: Summary fields - client or server - speed

              In that case the best method would be to call the perform script on server script whenever someone changes a value that effects the sum.  Then you can still store the value and have it up to date.


              Based on your description I'm going to guess that in your case you would have an unbilled total sum for a job, and then an unbilled total for a client.

              • 4. Re: Summary fields - client or server - speed

                It is a technique I can definitely use in some areas but there are too many layouts where this information is presented so it would require a lot of programming to enable it, still leaving me with gaps.  This is a practice management system for a law firm with a lot of "dashboard" - functionality I'm loathe to lose.


                It seems as though we could do with a summary field which is processed on the server and not the client.   It seems very wasteful of resources to download all the data for a summary field which the client is going to process in exactly the same way.

                • 5. Re: Summary fields - client or server - speed

                  I often use PSOS with eSQL and return the result back to the client. It works really well and allows the flexibility of not having relationships. You just need to pass query parameters to the server as script parameters and parse them into variables.


                  Follow the the best practices for eSQL and it is pretty fast.

                  • 6. Re: Summary fields - client or server - speed

                    I often use the result in a merge variable so there is no record commit unless I need it in a field. If it is just for display reference merge variable is faster.

                    • 7. Re: Summary fields - client or server - speed

                      Optimising for WAN does take time and careful consideration.  You could "hide" the summary fields when logging in remotely and use a button to turn them on, then you could get on with your work speedily and only call on the summary information to display and calculate when you need it.

                      In the inspector use the hide condition and the summary field won't calculate until you make it visible.


                      It would be good for the summaries to be calculated on the server and is something FileMaker could improve on but when summaries are often dependent on the context of the client, portal filters, global variables, the current record, the current found set etc. I can see why it is calculating on the client and not the server.