5 Replies Latest reply on Jul 26, 2015 1:35 AM by lijnbach

    Best way to recalc stored calculations



      Best way to recalc stored calculations


      Hello everybody,

      I have a table with a lot of date calculations. Because of the performance I choose to make the calculations stored.

      My questions is, what is the best way to to perform a recalculation. I know the stored calculations only update when one of the fields is changed and/or on commit record. But, within the calculation the current date is also used, so I need an update every day the database is opened by the user, even when the user doesn't change the record.

      E.g. The user prints a report with "end dates" every week, without editing records. Without a recalculation I get the wrong dates.

      Thanks in advance,

      Hans Lijnbach

        • 1. Re: Best way to recalc stored calculations

          Is this stand alone or on a server?
          Without knowing a whole lot about your DB design, you can probably streamline things with unstored calculations depending on where they are.  Are they on the parent record, the child?

          Generally for reporting, many of the calcs could be stored for data that doesn't change, and the unstored ones only where absolutely needed.  I would start at the first calc in the long line of calcs, make it unstored, check performance, go to the next one, etc.

          Current Date calcs should almost always be unstored.  Once again, depending on structure, I cant image just one is tripping you up.

          I guess an OnFirstWindowOpen script trigger in your opening routine, could take a field or fields, set them to themselves, commit record, could help.

          1 of 1 people found this helpful
          • 2. Re: Best way to recalc stored calculations

            I would create a global field ( gToday ) or a global variable ( $$Today ) with an OnFirstWindowOpen trigger, that contains the Get ( CurrentDate ) and change those unstored calculation to stored, but using the Today field or variable.

            • 3. Re: Best way to recalc stored calculations

              O.K. Thank you both for your effort and time,

              It is (in this case) a stand alone application. But in total I got over 18 date calculations, so that is why I choose for stored calculations. I can give it a try and looks what happens if I make them unstored one by one.

              And I will experiment with a trigger OnFirstWindowOpen.

              Hans Lijnbach

              • 4. Re: Best way to recalc stored calculations

                Seems like unstored calculations would actually be the better option here.

                • 5. Re: Best way to recalc stored calculations

                  Hai Phil,

                  Yes, I am trying it out on this moment, I change them to unstored one by one, and see what happens with the performance. It looks like FMPA version 14 is handling this better then FMPA 13.

                  At first I choose for stored calculations fields because I know that a lot of unstored calculations have a big impact on the performance when cycling threw records.

                  Thanks again Phil.

                  Hans Lijnbach