7 Replies Latest reply on Dec 2, 2015 6:00 PM by srzuch

    Summing portal records before they are committed.

    srzuch

      I have had inconsistent results using the aggregate function SUM on child records in a portal, before they are committed.  The SUM field on the screen displays the correct result, but the field in the Data Viewer does not reflect changes until the records are committed (and the Data Viewer window is refreshed).  I have an un-stored aggregation calculation field that shows the correct result on the screen, but an incorrect result in the Data Viewer.

       

      Other users have reported this issue.

       

      I need to be able to validate the total of the portal records before the records are committed.  The validation tests would be in the OnRecordCommit layout script trigger.

       

      What is interesting is when I select the parent record SUM field, the value will change to the the incorrect value, but change back to the correct value when I leave the field.

       

      Also, the LIST aggregate function works correctly; all amounts are properly reflected before the records are committed.

       

      So, I am trying to obtain a better understanding in what is happening beneath the hood.  It appears that there are temporary values being stored, which can be differ from the actual values in the database (clearly, since the records are uncommitted). The LIST function accesses these temporary values, as does the SUM function for display purposes.  But the SUM function “data” field does not, so I can’t test the value.

       

      The Invoice starter solution appears to work properly; the results of the SUM function reflects uncommitted records, and the SUM field properly shows up in the Data Viewer and can be validated in the OnRecordCommit script.  For the life of me, I can’t see what the solution is doing to get this to work right!

       

      I am working with FileMaker Pro Advance version 13.0v5, under Mac OS X Yosemite (v10.10.5), with plenty of RAM (16gb).  The testing is performed on a single, non-shared database, stored on my local disk.  I have created several test databases from scratch, all behaving the same way.

       

      This problem is an issue for me given the accounting/finance nature of the solutions that I am trying to develop.

       

      Thanks for any information.

       

      Steven

      (new in FM, old in everything else)

        • 1. Re: Summing portal records before they are committed.
          Mike_Mitchell

          I'm not near a computer at the moment, so I can't test this. But one thing you might try is using a summary field on the child side of the relationship. Not 100% positive it will work, but worth a try.

           

          Another option would be to use ExecuteSQL ( ) to aggregate. That might have some serious performance issues, depending on the data load, but it will be accurate based on the uncommitted state.

          • 2. Re: Summing portal records before they are committed.
            srzuch

            Thanks for your response.

             

            I have found that a summary field more consistently shows the correct amount in the data viewer, but needs a refresh object or windows to, on occasion, update the screen.

             

            I did try the ExecuteSQL before, and I had some issues, but will test it again. 

             

            The starter samples (invoices) appear to work with the Sum function, but I just can't get it to work.  I have tried a lot of different things.

             

            But, I am concerned about any of the functions totaling uncommitted portal records, consistently, in all environments, so I can use the totals in essential business validation tests before committing the information, especially, since different aggregation functions don't handle the uncommitted records the same way.   I may use a temporary table, or even repetition fields, to accumulate the information before posting to the real table. 

             

            I am looking to understand how FM treats the uncommitted records, and how others reliability total them and test the totals before committing.

             

            Steven

            • 3. Re: Summing portal records before they are committed.
              Mike_Mitchell

              Steven -

               

              There are differences in how calculations are evaluated on the host vs. on the client. See this Knowledge Base article for details:

               

                   How Functions Evaluate Differently on the Host Versus the Client in FileMaker Pro | FileMaker

               

              Pay particular attention to this bit:

               

              "In FileMaker, most find operations on unstored calculated fields will be executed on a host."

               

              This means that uncommitted records will not, in most cases, be reflected in the calculations. (There are some exceptions.) The reason you need a refresh of the window or object is to force a recalculation at the client (or, perhaps, a load of the result from the server, depending on the environment.)

               

              I suggest you use ExecuteSQL ( ) for this particular use case, since it will evaluate on the server UNLESS there are open records, in which case the server will send all records involved in the query down to the client to include the uncommitted changes. (Props to wimdecorte for this revelation.) This is why there's a potential performance issue, but if your business rules involve a critical need to have the exact value prior to committing the record, then this should suffice.

               

              Mike

              • 4. Re: Summing portal records before they are committed.
                srzuch

                Thanks for the information.   I will revisit testing ExecuteSQL().  But I am still baffled how the Invoice sampler database is able to get the Sum function to work 

                 

                Steve

                • 5. Re: Summing portal records before they are committed.
                  Mike_Mitchell

                  I played around with the Invoices solution a bit. It only updates when you exit the fields that feed into the Amount or Discount totals. They’ve cascaded the results as calculations, which will update after the field is saved / validated. That probably triggers a refresh of the unstored calculation tree.

                   

                  What exactly is your configuration? What are you attempting to Sum?

                  • 6. Re: Summing portal records before they are committed.
                    srzuch

                    Basic configuration for testing -

                     

                    Parent table:

                    Id

                    SumAmount: Sum(Child::Amount) (un-stored)

                     

                    Child table:

                    Id

                    ParentId

                    Amount

                     

                    Related on Parent::Id = Child::ParentId

                    All fields are numbers.

                     

                     

                    I also had a summary field in the Child table, summing the Amount field.

                    I then, based on your post, in the Child table, added Price & Quantity fields, and changed Amount to a calculation.

                     

                    This change appeared to give more consistent results of correctly summing the Amount in both the screen and data viewer, after refreshing the window (menu option) and refreshing the data viewer.

                     

                    But the results have been strange, and have only been tested in a single user, desktop mode.  As your link above mentioned, server results can vary.

                     

                    My novice conclusion is that I am playing with fire to base critical validation tests on aggregating uncommitted portal records.  It is best to use temporary tables and/or fields, commit records and the test the aggregate values.

                     

                    I still wish there was some under the hood documentation to clarify the uncommitted record issue. 

                     

                    Anyway, thanks much for your assistance in this matter.

                     

                    Steve

                    • 7. Re: Summing portal records before they are committed.
                      srzuch

                      Yes,

                       

                      It appears that if you need to use the results of the sum aggregation function in a parent table, to add amounts in a child table, within a portal, before the portal is committed, the child amount field must be an un-stored calculation field (e.g. amount = price * quantity).  If the amount field is just numeric, or is a stored calculation, then the sum function displays the correct total, but the underlying field does not have the correct total until the record is committed.

                       

                      I hope got this right, and have only tested the above in a single user local database.

                       

                      Steve