3 Replies Latest reply on May 16, 2012 10:34 AM by philmodjunk

    Storage Options - When Not To Store

    Tucker

      Title

      Storage Options - When Not To Store

      Post

       Is there a general rule about when you should choose not to store a calculation? I noticed on a previous answer to a post that I made, it was not advisable to store the result when calculating Year(Get (CurrentDate) for a field called This Year. When related to my particular circumstance this made sense because I want the calculation to perform the same function next year.

      However, what about a calculation that's: Year (Get (CurrentDate)-1 to provide you with the previous year's, year. Would this still return the same result if it was stored or unstored? Just curious.

      Thanks

        • 1. Re: Storage Options - When Not To Store
          Jason Wood

          A stored calculation only refreshes its value in certain circumstances (generally, when a FIELD it is dependant on is modified, but not when the result of a "get" function changes), so generally you don't want to store any calculation that references the current date, since the date changes every day and the value displayed in the field will not update automatically to reflect this.

           

          I think, in general, you want to store the results of a calculation if you can (ie: if it doesn't use related data), and if it doesn't use "get" functions that could change. Then again, you could save space by not storing calculations that are used only for display (not searched upon and not included in any other calculations).

          • 2. Re: Storage Options - When Not To Store
            Tucker

             Thanks Jason. I'm very new to this and am trying to get up to speed on some of the aspects that may come back to haunt me later. So far with much help from this forum, I've made great progress but I liken my knowledge to that of telling the time without understanding how the clock really works.  

            "I think, in general, you want to store the results of a calculation if you can (ie: if it doesn't use related data)..."

            That sums up what I have found from some experimenting. I found that any data that has been entered, not using a calculation must be stored and all calculations (that I've used so far) do not need to be stored. My calculations are for display only.

            • 3. Re: Storage Options - When Not To Store
              philmodjunk

              There's a basic trade off between stored and unstored calculations to keep in mind.

              Stored calculations will search, sort and "display" much faster than unstored calculations as FileMaker has to re-evaluate them each time they are referenced and also has to build a temporary "index" for that field in many cases. Thus, stored calculations result in a database that is much more responsive as you pull up different sets of records on your layouts.

              However, the shoe is on the other foot when you import data into a new copy of your database. During import, filemaker has to evaluate each and every stored calculation field. Unstored fields do not require this. In addition, stored fields may also be "indexed" and if so, FileMaker must build that index--a "sorted" datastructure (a btree I think) of each unique value from all the records in the table as part of the import process. Thus tables with lots of stored/indexed calculation fields will import much more slowly than do a tables of the same size with unstored calculations.

              Since you are likely to refresh your layouts/found sets etc. hundreds if not thousands of times for every time that you import records, I tend to choose stored/indexed fields whenever possible, but I revisit this issue for tables where the number of records becomes very large such as a table with 100,000 records or more. In those cases, the time needed to import data into a clone after a file is damaged or an updated copy has been produced could take many hours--and reducing the number of stored calculation fields becomes very desirable.

              Also keep in mind that you can use data fields with auto-entered calculations in place of some calculation fields. (They don't update automatically with get functions nor when if reference a field in a different record or table). FileMaker does not need to re-evaluate these fields--but will need to build any specified indexes during import so they will import a bit faster than stored calculation fields--provided that you import without selecting the "enable auto-enter options" check box.