7 Replies Latest reply on Jul 2, 2015 2:36 PM by philmodjunk

    Calculation field (global) doesn't update

    AnalogonEnterprise

      Summary

      Calculation field (global) doesn't update

      Product

      FileMaker Go

      Version

      13.09

      Operating system version

      MacOs 10.9

      Description of the issue

      In a calculation field, set to global/date, with a formula wich calculates the first day of the actual quarter, today I get the result of the last quarter. When I copy the formula into the data viewer, the result ist right. The field won't update. It only updates when I change the formula of the field a little bit, e.g. add a blank. Then, a second field which calculates the end of the quarter also updates.

      In the screenshot I added the global calculation field to the data viewer and then copied the formula of this field into it – two different result of the same formula (today is 2. July 2015).

      Configuration information

      "Don't calculate when fields are empty" is deactivated.

      Bildschirmfoto_2015-07-02_um_05.59.08.png

        • 1. Re: Calculation field (global) doesn't update
          Fred(CH)

          Rainer, there are important things to keep in mind with globals and with calculation also. If the file is hosted, the globals won't save their data when you close you session (except if hosted by a FMP and only its own session will save not sessions of connected users).

          Thanks to you screenshots, it is clear the problem here is how to trig the calc. I dont know this formula. Is NächteGrossereGanzZahl a global field or a calc function ?
          And is PATMASK::QuartalAnfang a global Field in the current table ?

          Because the rule here is approximatively : to trig a stored calculation to update, a change must occur on a field which is a part of the formula and from the same table as the calculation. Moreover, with global calculation, this "trigger" field must also be global.

          • 2. Re: Calculation field (global) doesn't update
            AnalogonEnterprise

            Fred:

            -database is local

            -NächsteGrößereGanzzahl = Ceiling

            -PATMASK::QuartalAnfang is the global calculation field, the actual layout is based on PATMASK

            -the trigger is Hole (Systemdatum) = Get (currentdate)

            As I wrote, in the data viewer you see the calculation field (PATMASK::QuartalAnfang) AND the calculation itself.

             

            PS: While typing this: when I press return, the cursor always goes to the beginning of the text (MacOs 10.9, Safari 7.1.6)

            • 3. Re: Calculation field (global) doesn't update
              Fred(CH)

              -PATMASK::QuartalAnfang is the global calculation field, the actual layout is based on PATMASK

              But the caclulation we are talking about is also from the same table ?

               

              -the trigger is Hole (Systemdatum) = Get (currentdate)

              As i said, the trigger can only be a field. So this is not the case here --> the issue.

               

              As I wrote, in the data viewer you see the calculation field (PATMASK::QuartalAnfang) AND the calculation itself.

              The data viewer doesn't need a trigger. This is the very reason why i knew supposed it was a triggering issue.

              • 4. Re: Calculation field (global) doesn't update
                Fred(CH)

                Oh and one simple suggestion to investigate farther :

                If you now set the field PATMASK::QuartalAnfang even with its same actual value, just trying to trig the calculation once more.

                Do it updates the result or not ?

                • 5. Re: Calculation field (global) doesn't update
                  philmodjunk

                  I generally find it much simpler to set up an unstored calculation field rather than a global calculation. I generally find that the odd rules for how a global field updates get in the way of how I need to use the field in my solution. Granted, this isn't always a workable alternative, but when it is, it's a lot simpler option.

                  The basic rule for update triggers are as follows:

                  In both a stored calculation and in a data field with an auto-enter calculation where the "do not replace existing value" is NOT checked, the same rules apply:

                  If a field from the same record that is referenced in the expression has its value changed, the calculation will update automatically. but if data from another source, such as a global field/variable, a get function or a field from a related table* changes, the calculation does not update automatically. This often requires that calculation fields using only Get functions or the ExecuteSQL** function to be defined as unstored before they work the way you need them to.

                   

                  * When a stored calculation field refers to either a field from another record or a global field, it ceases to be a stored calculation field as FileMaker automatically changes its storage options to "unstored" and doesn't let you change it back.

                  **ExecuteSQL calculations will update in stored calculation fields if some part of the calculation outside of the quoted text used in the query refers to a field in the same record and a value in that field changes. Thus you can often use the ? optional parameter with a reference to a field in the same record supplying the value to the ? and then the calculation works as needed in a stored calculation or auto-enter calc.

                  • 6. Re: Calculation field (global) doesn't update
                    Fred(CH)

                    Hey Phil,

                    Thank you for shimming here ! Your explanations are indeed far more clear and detailed than mines.

                    However, if i were you, i would not "tease" for ExecuteSQL at this point. It is an amazing and powerful function but doesn't avoid to know basic calculation rules and obviously it add some more additional technical challenges.

                    At this point, it may add more difficulties than it will solve...

                    • 7. Re: Calculation field (global) doesn't update
                      philmodjunk

                      That was not my intent. Instead, many people are under the misapprehension that Calculation fields must be unstored if their expressions use ExecuteSQL. This is not really the case as the same rules that govern when a field will or will not recalculate also apply to ExecuteSQL We just forget that much of our expression is a quoted string and thus anything inside that string isn't the type of reference that will result in an update when data is changed in another field.