12 Replies Latest reply on Feb 23, 2016 6:04 AM by reidbloomfield

    Calculation Field Does Not Refresh with Refresh Window

    johnhorner

      Title

      Calculation Field Does Not Refresh with Refresh Window

      Post

      i have a report layout that contains a calculation field.  the calculation references a summary field in a related table.  if the data is changed in any of the fields in the related table which are being summarized, the calculation field fails to "refresh" or show the correct total.  i have a button on the layout to refresh the window and flush the cache.  this has no effect.  if i place the related summary field on the layout, it will update using the refresh window step so i know ht esummary field itself is current and accurate (but i need the calculation field because it is used by other summary fields on the layout).

      if i close the file and then reopen it and go back to the report, it updates everything and it will display the correct totals in the calculation field.  does anyone know how to get this calculation field to update?  can whatever process/es that takes place when i close and reopen the file be scripted somehow?  any thoughts much appreciated.  thanks...

        • 1. Re: Calculation Field Does Not Refresh with Refresh Window
          davidanders

          Put the summary field on the layout.

          Turn it white, tiny, and can't be entered into - even remove from tab setup.

          • 2. Re: Calculation Field Does Not Refresh with Refresh Window
            symbister

            Hi

            I had a similar thing with summary field on a layout that calculates the sum and the count of entries in a portal. This updates fine, but doesn't automatically if you delete a related record in the portal. My workaround was to script a delete button in the portal, which also switches into Preview Mode then back to Browse Mode, which updates the window. (Refresh Window didn't work for me)

            • 3. Re: Calculation Field Does Not Refresh with Refresh Window
              johnhorner

              thanks for the ideas...

              david, unfortunately, i had already tried placing the summary field on the layout (that's how i knew it was at least updating that field with the refresh window step). it doesn't seem to have any effect however on the behavior of the summary field.

              symbister, i also tried going into preview mode and back.  that doesn't seem to have any effect either.  both the calculation field and the summary field remain unchanged.  is there possibly another step in your script that is causing your window to update? perhaps a commit record or something like that?

              i did a couple of tests and discovered an interesting pattern.  if i simply change the data in the fields being summarized, as i mentioned before, it will not refresh using refresh window.  if, however, i change the data in one of the fields that is used to define the relationship between the main table where the calculation field resides, and the related table where the summary field resides, then the calculation field will update using refresh window.  it seems the calculation only recalculates when the set of related records changes...?

              any other thoughts?

              • 4. Re: Calculation Field Does Not Refresh with Refresh Window
                Sorbsbuster

                Phil passed on a good idea to easily force such a refresh.  Make the relationship include a cartesian of:

                SomeTextField X some other field

                and at the end of the script put a Set Field step to reset the SomeTextField to itself.

                • 5. Re: Calculation Field Does Not Refresh with Refresh Window
                  johnhorner

                  OMG!  hallelujah!!!  that's such a simple concept and easy to implement... brilliant!!!  working perfectly. Smile

                  this is something that i have wrestled with for years.  most of the time related fields were not changing often enough to make it a real problem and i always had the work around of closing and reopening the file, or removing and replacing a record from the relationship set if i really needed to be absolutely sure the numbers were accurate.  for me, this is one of those rare filemaker breakthroughs!!!  i see the light...

                  thank you, thank you, thank you!  and, as always, phil... you're a genius!

                   

                  (filemaker: how would you like to add a "refresh relationship" script step in additional to "refresh window"?)

                  • 6. Re: Calculation Field Does Not Refresh with Refresh Window
                    philmodjunk

                    Another option is to use the equivalent aggregate function in place of the summary field: Sum (relatedtable::field) instead of "total of" etc.

                    • 7. Re: Calculation Field Does Not Refresh with Refresh Window
                      johnhorner

                      phil... i was already impressed with your superhuman command of filemaker, and i thought i had seen it all, but then you outdid yourself by solving a problem without even responding to it... truly amazing!

                      thanks also for the additional option... i am wondering... i noticed that in some of these fields i had, in fact, previously used the sum function in a calculation as you described and i saw a comment i had left that said "seems slower than summary method".  it was a long time ago, but i am assuming i tested both options before i became aware of the update issue and went with the summary method because it seemed faster?  is this even true that a summary of related records displays faster than a sum (relatedtable::field) calculation?  apart from possible speed issues are there other factors to consider when deciding whether to use the scripted cartesian join update method or the "equivalent aggregate" method?  do you have a genreal preference for one over the other?

                      • 8. Re: Calculation Field Does Not Refresh with Refresh Window
                        johnhorner

                        ... if it helps clarify my situation, my goal is to generate a sales tax report.  a typical field in sales tax needs to show the total nontaxable sales, for example, for a given sales tax period.  each sales tax record is related to invoices using a multi-key relationship (period, year, and state).  invoices contains both a nontaxable sales calculation field which is the sum of nontaxable line items on a given invoice (related to invoices by invoice id) and also a summary field which equals the total of the calculation field for the found set.  so i currently have the option of using the summary method:

                        (salestax_INVOICES_PeriodYearState::zs_NontaxableSales_Actual)  -  the prefix "zs" indicates a summary field

                        or the aggregate sum method:

                        (Sum ( salestax_INVOICES_PeriodYearState::z_NontaxableSales_Actual ))  -  the prefix "z" indicates a calculation field

                        Would it make more sense to bypass invoices altogether and get the totals from the line items table itself?

                        (e.g. salestax_invoices_periodyearstate_INVOICELINEITEMS_Nontaxable::zs_ItemSubtotal_Nontaxable (summary method)

                        or sum ( salestax_invoices_periodyearstate_INVOICELINEITEMS_Nontaxable::z_ItemSubtotal_Nontaxable))?

                        • 9. Re: Calculation Field Does Not Refresh with Refresh Window
                          philmodjunk

                          As the number of related records increases, there can be a performance difference. On the other hand, you don't have to jump through any extra hoops to get your total to update either--so you have to match the method to your solution.

                          In your original efforts to script a refresh for the summary field, did you also try Commit Record? (I'm assuming that you were editing related data shown on the layout--probably in a portal.)

                          • 10. Re: Calculation Field Does Not Refresh with Refresh Window
                            johnhorner

                            thanks phil... as for the question about work flow, i was actually working in the invoices layout and making changes to either the price for one of the line items or the tax status of a line item (using a portal on the invoices layout).  then i was navigating to the main sales tax layout (all in the same window) that has buttons to run scripts (actually one script with different parameters) to generate various reports.  the script basically finds and sorts the target sales tax records (e.g. this month, this year, last year, summary vs detail, etc) and then opens a new window and goes to a sales tax report layout.  i just happened to notice after changing an invoice line item and creating a report that the totals (gross sales, taxable, nontaxable, etc) did not reflect the changes.  that's how this all got started.  i did try putting a commit record step in to the report script near the end of the script (after sorting i think?) but it  didn't seem to make a difference so i disabled it.  thanks again for the 2 solutions!

                            • 11. Re: Calculation Field Does Not Refresh with Refresh Window
                              J.M.Schomburg

                              Hi - I'd just like to add that that JohnHorner and PhilModjunk's solution worked for me:

                              I had a portal that wasn't recalculating after removing records IF portal sorting was turned on. Instead of just removing the record, I did a script (linked to the row 'delete' button) that first removed the record, then did a 'Commit Record'. That worked!

                              Thanks for helping me through it, guys.

                              • 12. Re: Calculation Field Does Not Refresh with Refresh Window
                                reidbloomfield

                                THANK YOU!!! This worked great. Just to be more specific for others, I created a text field in each table, set it as a global, auto calculation = "text". At the end of my script, I just set the field as "text" and it updated my calcs and summaries. I have a calculation field that sums the values of 5 different summaries in 5 different related tables. This solution worked like a charm.

                                 

                                Thanks again!