10 Replies Latest reply on Jan 2, 2015 3:53 PM by philmodjunk

    Recalculate all fields

    PrestonHunt

      Title

      Recalculate all fields

      Post

      New to actually using FileMaker so excuse the newbie questions.

      What function can be used in a button or script to force every field in a table and on a layout to recalculate?

      I tried the "Refresh Window" and it did not do a thing.

       

      Pro Advanced version 12.

       

      Thanks

        • 1. Re: Recalculate all fields
          RickWhitelaw

          If your calculations and relationships are set up properly the fields should update when the layout is opened. Or you may need to have a script triggered OnLayoutLoad (or whatever they call it now) to update the fields.

          • 2. Re: Recalculate all fields
            PrestonHunt

            I need them to update while data is being changed on the layout. For some reason FM does not seem to what to do that automaticly every time. Sometime it does, sometimes it does not.  I was thinking about just putting a button on the layout that could be used to force an update and recalculation once any data on the layout was changed.

            • 3. Re: Recalculate all fields
              philmodjunk

              If the calculation is a stored calculation field, it will update automatically each time a referenced field is modified.

              If the calculation is an unstored calculation field, it will update automatically each time an event triggers a refresh of the layout.

              If the field is a data field with an auto-enter calculation or a looked up value setting, it will not update automatically if the referenced field is from a different record and/or table.

              • 4. Re: Recalculate all fields
                WBSimon

                Setup a button that triggers a script that leaves the layout, goes to another layout and then returns to the original layout. If you freeze the window, it can be done seamlessly. May not be the most elegant way to do it, but it works. I have had to do it once or twice when "Sum" fields were present in the layout.

                • 5. Re: Recalculate all fields
                  philmodjunk

                  In such cases, often all that is needed is to commit records on the current layout. There's a script step for that and it can be done manually by clicking a blank area of the layout.

                  • 6. Re: Recalculate all fields
                    PrestonHunt

                    Thanks. I will keep working on it. I cannot set some of the calculated fields as stored because they use a summery field as part of the calculation. I guess I could dump the value of the summery fields into other fields and then use them to get around that.

                    • 7. Re: Recalculate all fields
                      philmodjunk

                      Unstored calculations generally update without issue or needing any "help". On the other hand, the summary fields these calculations reference--particularly if from a related table will not update until you commit records as I have previously described.

                      But you can often get smoother updates in this situation by replacing the reference to a summary field in the related table with an aggregate function such as Sum, Count, Average, that computes the same value.

                      (But if these are summary fields that are defined in the same table as your layout, you just need to commit records to see them update.)

                      • 8. Re: Recalculate all fields
                        PrestonHunt

                        Thanks Phil.

                        I am basically just learning how FM does things by plowing in and kicking things. New to actually doing anything with FM but not to developing so it is just taking a bit of time to figure out how FM does things.

                        • 9. Re: Recalculate all fields
                          WBSimon

                          In the cases that I was referring to, "Commit Records" manually or by script step did not work either. Believe me, that would have been a lot fewer steps. 

                          • 10. Re: Recalculate all fields
                            philmodjunk

                            It should have worked. A change in layouts commits records and except for unusual cases,commit is all that should be needed. The layout change will be much like refresh window [flush cached join results ] with the same potential for a long delay waiting for everything to update. You don't want to do that unless you have no alternative--and you almost always do have a better option.