2 Replies Latest reply on Dec 28, 2016 9:07 AM by BruceHerbach

    Unstored Calcs versus Auto Enter Calculation


      What are the pros and cons of using an unstored calculation versus an Auto Enter calculation in the field definition and Setup?


      I have layout in a utility table PERIODS that is related to a table that contains ordering and cancelation date. I have several unstored calc fields in PERIODS that need to refresh often. The refresh process takes several seconds and I'm wondering if it would be faster to use an Auto Enter calculated value.


      Are there benefits to using an unstored calculation versus the Auto Enter calculation?

        • 1. Re: Unstored Calcs versus Auto Enter Calculation

          If the calculation in PERIODS is using data/fields from the related table, you need to have some way to kick the auto-enter calculation to have it reevaluate.


          Unstored calcs are nice in that you don't have to touch them for them to recalculate. They don't required stored data in the table. However, they also have a dark side if they are mis-used. They can be expensive performance-wise. Especially when you get into one unstored calc being dependent on another unstored calc.


          You will need to find a balance:

          • During scripts, you can go update static values only when certain actions happen. This increases the data footprint, but will pay large dividends in speed and performance.
          • Typically for unstored calcs, I prefer, when possible, to only use them for UI stuff. It's not always possible, but that's the goal.
          • Auto-enters are nice for things like invoice totals, as only one example. Since you want it to calculate, but not update once the invoice is processed.
          • 2. Re: Unstored Calcs versus Auto Enter Calculation

            An unstored Calc updates when displayed.  So if you have a field based on a field in a related table it will update when displayed.  Which is great... except when it is summing up a lot of data and you want to display it in a portal..  Then system response can be an issue.  Portals will scroll slowly.


            Stored/indexed calculations store their value and will display very quickly when scrolling portal.  The drawback is that a stored calculation can only be based on fields in the current table.  As soon as you base the calculation on related table it automatically becomes an unstored calculation.


            There is a work around and that is to have the field be a number or text field with an auto enter calculation.  The big issue here is making sure that the value stored in the field is correct.  To do this,  you can add a field to the table and add it to a Let() function in the auto-enter calculation.  This becomes a trigger field.  Change the value in the new field and it will force the field to update the calculation and with the data from the related table.  The downside is that making sure this is correct is all up to the developer.


            Another approach can be to run a script that updates the field.  Either triggered by a button or script trigger.  Again it is up to the developer to make sure the data in these fields is correct.