12 Replies Latest reply on Aug 29, 2016 7:19 AM by flukey

    Summary fields not updating

    flukey

      Using FMPA 15 with Windows 8.1.

       

      I have a simple list view that is based on an invoice table.  The invoice table has a pending balance field that is defined as the (invoice amount - the invoice payments).  The invoice payments is defined as a sum of payments from a related table.  The pending balance updates properly and instantly reflects the correct value.  However the summary field, defined as the total of the pending balance never updates until I change layouts and come back to the invoice layout.  Refresh window with flush cache does not do it.  Commit does not do it. going from one record to another does not do it.  Doing an omit to change the found set instantly updates the summary value to the correct amount.

       

      This is a simple list view that I have done hundreds of times, but this one is not updating.  I have run the file through recover but it comes up with no errors.

      What am I missing?

       

      I have attached a couple snapshots.

        • 1. Re: Summary fields not updating
          keywords

          It looks like a dependency issue. TotalPendingBalance is a summary of an unstored calc which itself draws on a further unstored calc. I think if it was me I'd look at trying to alter at least one of those to an indexable number, using an auto-enter calc that updates itself as its feed data changes.

          1 of 1 people found this helpful
          • 2. Re: Summary fields not updating
            philmodjunk

            And calculations that use the sum function can compute the same total and generally update more smoothly.

             

            and if you choose to use keywords suggestion, you might also consider a script trigger driven script that updates a normal data field as a way to reduce the chain of dependencies.

            • 3. Re: Summary fields not updating
              flukey

              I agree that it is probably a dependency issue.  However I am pretty sure that I have done this type of setup before without any issues (or maybe I just didn't notice).  Changing to an auto enter calc does fix the problem,  but it seems to me that the summary should be updating if the dependent field is updated.  I can understand if the field was in some other related table, but the field that is being totalled is in the same table, and that field is being updated properly by the relationship.  Seems like a bug to me.

              • 4. Re: Summary fields not updating
                flukey

                A script trigger will not work here. The invoice payment is being processed in a transactional script.  At the end of the script everything is committed and all of the appropriate fields get updated and reflect their proper values, except the summary field.

                • 5. Re: Summary fields not updating
                  philmodjunk
                  A script trigger will not work here.

                   

                  Actually, it might. The point is to reduce the number of dependency levels by storing an aggregate value in an indexed field. OnCommitRecord could, in fact be the trigger used.

                   

                  And I still think you should try using Sum() from the context of the parent record instead of summary from the context of the child.

                   

                   

                  • 6. Re: Summary fields not updating
                    Malcolm

                    I handle these situations by creating a summary field in the child tables, which is a TOTAL of the data that you are interested in. In InvoicePayment that would be "sum total PaymentAmount". In your line items table it would be "sum total LineTotal". From invoices you would then have a calculation which is simply ( LineItems::sum total LineTotal - InvoicePayment::sum total PaymentAmount ).

                    • 7. Re: Summary fields not updating
                      srzuch
                      5. Re: Summary fields not updating


                      Actually, it might. The point is to reduce the number of dependency levels by storing an aggregate value in an indexed field. OnCommitRecord could, in fact be the trigger used.

                       

                      And I still think you should try using Sum() from the context of the parent record instead of summary from the context of the child.

                       

                      Sum() from the context of a parent will display even when the child records are not committed; but, the actual field content is not updated until the the records are committed.  What is displayed is not equal to the number contained in the field, until after the record is commited (or, I believe, until the dependency chain is triggered by using the field in a parent stored calculation).   Frustrating.

                       

                      Steve

                      • 8. Re: Summary fields not updating
                        philmodjunk

                        but, the actual field content is not updated until the the records are committed.

                         

                        This is also true of summary fields, the difference being that the field will appear empty while the record is open for editing.

                        1 of 1 people found this helpful
                        • 9. Re: Summary fields not updating
                          flukey

                          Hi Phil,

                           

                          Actually I am using Sum from the context of the parent record, summing the child line items. Also the Summary is from the parent record summarizing the found set.  I am also doing a complete commit on everything.

                           

                          I guess that what you are saying is that I should run a sum of the found set on commit just so that I can get the updated total.  This means that I would have to get a list of the primary keys of the current found set, put them in a global, point that global to another TO instance of the invoice table, and calculate the sum.  Since I am trying to always provide a total for the current found set, I would have to do this every time the found set changes, e.g. omit or find, etc.  Triggers can be used but this can get rather cumbersome.

                           

                          I truly believe that this is a bug, since this is exactly what the summary function is supposed to do automatically.  I have a field in the same table that is being updated properly and the dependent summary field in that same table is not getting triggered to update.  If I was one or more TOs away I would understand the need to do what you suggest, in fact I do that now to trigger cache refreshes using the X join technique for some other cases.

                          • 10. Re: Summary fields not updating
                            flukey

                            Hi Malcolm,

                             

                            I am not sure that that would solve the problem.  The problem is not that the child records are not being summed properly, it is that the parent is not providing the correct summary total for the found set, even after it is reflecting the correct totals for the individual records.  Very frustrating that there seems to be no way to get the summary field to update other than switching layouts.

                            • 11. Re: Summary fields not updating
                              srzuch

                              I was referring to the calculation field in the parent table, using the SUM aggregation function totaling a field in a child table.  In this case, as Phil mentioned, the display is much smother and timely than using a summary field.  The display of the parent calculation field is actually updated as one enters records in the child table (e.g. through a portal), even prior to committing the child records.  The display of the summary field would not be updated.

                               

                              However, the results being displayed for the parent calculation fields is NOT what is returned if I use the field in a script, until the child records are committed (or the dependency chain is triggered).  The parent calculation field shown in the data viewer will also be stale until the records are committed.

                               

                              This can lead to invalid results in scripts testing the totals in the OnRecordCommit trigger (e.g. the total of child records must equal something).

                               

                              Steve

                              1 of 1 people found this helpful
                              • 12. Re: Summary fields not updating
                                flukey

                                Hi Steve,

                                 

                                I understand your point of the displayed value not updating, and the values not being valid in scripts until after commits have been done.  I have run into this many times over the years.

                                 

                                However in this case I have done the commit.  I can even go from record to record in the current found set (therefore there should be no commit problem), yet still the summary total is not being updated.

                                 

                                Understand that I am not having a problem with the sum function in the parent table, either the display or the actual value, both are correct.  The problem is a simple summary field in a list view that is not reflecting the correct total for the list, even after commits, cache refreshes, window refreshes.

                                 

                                At this point I will probably just eliminate the summary field from the layout and use a sum field instead as Phil suggested.  Very frustrating to have to go through these contortions for something that is very basic to FM.