10 Replies Latest reply on Jul 5, 2016 4:22 AM by siplus

    Stored Calculation fields

    HashirRaja

      Hi there Filemaker community

       

      I am creating a database for my business. I got a trial on fmphost.com to host it on a server to see how it goes. I tried it, and right away, noticed that when I load my list layout, it would take 2 min to load the calculation / summary fields. I have about 360 records at the moment. I am planning to have much more than that. My calculation fields are basically just calculating how much % I get from payments and how many payments are remaining. There are about 3 calculation fields per record. The summary field summarizes total payments.

       

      I would like to know,

      1 - Should I not use too many calculation fields?

      2 - If so, what is a good alternative? Make a script to enter data into those fields with triggers?

       

      I was reading online regarding this, and I came across a talk about stored and unstored calculation fields. I tried to do more research on that, but I am not able to find any details about it.

       

      I wanted to know,

      3 - What are stored and unstored calculation fields?

      4 - How do you make a calculation field into stored or unstored?

       

      Thanks in advance!

        • 1. Re: Stored Calculation fields
          RickWhitelaw

          A calculated field can be stored if it does NOT reference related fields or other unstored calculations. the option is under Storage in the field definition.

          • 2. Re: Stored Calculation fields
            bigtom

            Avoid too many unstored calcs. Processing the calculations with scripts is an option.

            • 3. Re: Stored Calculation fields
              RickWhitelaw

              Of course. And your point in replying exactly?

              • 4. Re: Stored Calculation fields
                RickWhitelaw

                Bigtom. My mistake. your reply showed up as a reply to me. Didn't realize "reply" always referred to the last poster. So of course it would.

                • 5. Re: Stored Calculation fields
                  RickWhitelaw

                  "Reply to original post" replies to the OP. Doh.

                  • 6. Re: Stored Calculation fields
                    siplus

                    There are 2 kinds of calc fields, based upon scope:

                     

                    - 1) calcs that depend uniquely upon values in other fields of the same record;

                    - 2) calcs that depend upon related fields from other tables and/or upon fields from other records in the same table, via  sum(), List() or being summaries to start with.

                     

                    for 1) you can decide whether you want the calc result to be stored or not; for 2) you can't decide, it's unstored.

                     

                    A unstored calc will evaluate when it's referenced, i.e. displayed on a layout or called by another calc or evaluated by a script.

                     

                    A stored calc will update silently when the values it's based upon change.

                     

                    For 1) you can click on "storage options" in the definition window of your calc and decide to check or uncheck the "do not store calculation results - recalculate when needed". For 2) you can click but won't be allowed to uncheck the choice.

                    • 7. Re: Stored Calculation fields
                      keywords

                      Replies so far have mainly addressed your third and fourth questions—stored and unstored calcs. Regarding alternatives to calcs (your second question), a useful alternative is a standard number field that is populated by an auto-enter calc. If the calculation process is slowing things down this can be a godsend. You would, however, have to build in a process by which the data can be readily updated whenever needed.

                      • 8. Re: Stored Calculation fields
                        HashirRaja

                        Thanks for the quick replies. All the answers were very informative. I will be checking into all of them.

                         

                        Out of curiosity of how others setup their databases, I downloaded fmp starting point. It is a great and enormous solution. I went into the database management window. There, were many tables and in those many tables, are many calculations.

                         

                        How does this solution scale on a server? Do the users face heavy lag times? The reason I am saying this is because as I have mentioned in my original post, I got a bad experience with just the ~300 records (3 to 4 calc fields).

                        • 9. Re: Stored Calculation fields
                          bigtom

                          FM starting point is a decent all around starter solution. The performance is really dependent on the type of calculation used. I have done files with 4-5 simple calc and over 500,000 records and it is still really fast on a server over WAN. If you calc are complex and involve other records, tables, summaries, summaries of calc fields and things like that it can get super slow even with a few hundred records.

                           

                          You may want to find a better way to store the data and change it as needed. How often do the calculated values change?

                          • 10. Re: Stored Calculation fields
                            siplus
                            1 of 1 people found this helpful