2 Replies Latest reply on Feb 3, 2016 5:12 AM by AllegroDataSolutions

    Stored Calculation Question


      I am developing a database that a client started building. It has a bar graph which compares the total sales for the previous and current years. The dollar values are on the vertical axis. The horizontal has two bars for each month (the first bar being for the previous year and the second for the current year). The values come from a related table to the seller record being viewed. It contains fields for the dollar amount of each invoice, the month, and the date. To this I have added calculation fields to show the dollar amount if the sale occurred in the previous or current year. Also added two summary fields, that total the values of these fields (this is the data plotted on the chart).


      It works. But I don't think I'm done. Since the values of calculated fields are stored, my concern is what will happen next year, when the value of Year(Get(CurrentDate)) will change from 2016 to 2017, and the value of  Year(Get(CurrentDate))-1 will become 2016. My first question is will they even update, unless every record is visited, changed and saved? If the answer is yes, I'm thinking of having a server-side script to force the update on New Year's Eve, before anyone uses the database, because there will be tens of thousands of records to update. My second question is whether there is a better way to do this  -- i.e. getting the values of the calcs, and therefore the summaries, by some other means (unstored calcs, global fields, etc.).



        • 1. Re: Stored Calculation Question

          Your suspicion is correct. Use of any of the Get ( ) functions in a stored calculation will result in the returned value as of the time the calculation was stored, or when any value on which it depends changes. So yes, you'd have to run a script to update the values (or do it manually).


          As to your second question, storing data rather than using unstored calcs is generally a better approach. It will perform much better. All calculations force a performance penalty when they evaluate. The question is, when do I pay the penalty? Stored calculations impose the penalty whenever they are updated, but just sit there otherwise. Unstored calcs, on the other hand, impose the penalty every time they evaluate, for every client. Ouch. (The only benefit of using unstored calc fields is that they aren't downloaded from the server, so they don't consume network traffic. But it's usually a poor trade.)


          My suggestion would be to use a fixed date value, rather than Get ( CurrentDate ), for something like this. (I often use a creation date.) You can set up a relationship (or a pair of them) from the layout / TO on which the chart is based using global fields and just set them to the current and previous year values. That will give you your two sets of data against which you can chart, without the need to update the calculations. The relationships will evaluate quite quickly because the calculations are indexable, and that will vastly improve the performance over using unstored values.





          • 2. Re: Stored Calculation Question

            Thanks. I suspect the solution was going to be something like that. Even more important now, as it appears some of the calculations and summaries on other layouts will have to be updated monthly.