6 Replies Latest reply on Oct 26, 2008 4:56 PM by ralvy

    Calculation Field question

    ralvy

      Title

      Calculation Field question

      Post

      I'm in the process of evaluating both FilemakerPro 9 and AlphaFive 9. My experience with databasing is with a fully relational DBMS in the DOS world, DataPerfect. I'm considering converting a major DP database to a Windows database like FP, but have some questions regarding FP's Calculation fields.

       

      DP has a calculated field that doesn't store its data in the data file at all. It updates whenever it's displayed on the screen, or it's accessed by a report or another field's formula when that other field's record is being edited or accessed by a report. Otherwise, this type of calculated field's formula isn't triggered.

       

      This has the advantage of not burdening the database with updating tens or hundreds of thousands of records that may have such a field when opening the database. A simple example of such a field is an Age field, which has a formula that works of the Birthdate field. I really don't need to see that value when not displaying the record or running a report that includes it.

       

      When I play around with FP, it seems to not have such a field type. That is, its calculated fields seem to store their data in the data file, so when they're set to always update, they'll do that in all records that have them, when opening the database. Is this true?

        • 1. Re: Calculation Field question
          Sorbsbuster
            

          Hi ralvy,

           

          You're on a Filemaker Forum, so I'll save you the pages of purple prose praising its abilities.  I believe the feature that you want is covered in the 'Define Calculation' daialogue.  If you are setting up the calculation for a person's age, for example, then at the bottom right of the calculation definition entry box is a button 'Storage Options...'.  Clicking on this allows you to select how the calculation is handled, and you can select the option to 'Do not index.  Calculate only when needed.'

           

          I think that will give you what you're after.

           

          Good luck!

          Alan.

          • 2. Re: Calculation Field question
            ralvy
              

            Thanks for the quick reply. In FP9, I notice a Storage tab in the Options dialog. I assume that's what you mean. On that page I see a checkbox for "Automatically create indexes as needed." Is that what you're referring to?

             

            If that box is checked, will that field update its value whenever displayed?

             

            How about when reports use it?

             

            How about when other fields in other records access its value?

            • 3. Re: Calculation Field question
              Sorbsbuster
                

              Yes - if you set it to not index the field it will still calculate when you display it on a record, or a report.  The only 'downside' is that as it is not indexed then searching will be a bit slower, but the database functionality will be complete, not matter where you use the calculation.

               

              Alan.

              • 4. Re: Calculation Field question
                ralvy
                   Thanks, Alan. Very helphful.
                • 5. Re: Calculation Field question
                  davidhead
                    

                  ralvy wrote:

                  ... I notice a Storage tab in the Options dialog. I assume that's what you mean. On that page I see a checkbox for "Automatically create indexes as needed." Is that what you're referring to?

                   

                  If that box is checked, will that field update its value whenever displayed?

                   

                  How about when reports use it?

                   

                  How about when other fields in other records access its value?


                  Your original post was about calculation fields. The "Storage tab in the Options dialog" sounds like that for a standard field (text, number, date...). I think what Alan was referring to is the Storage Options... button in the Specify Calculation dialog. However, it has the same option - "Do not store calculation results -- recalculate when needed". 
                  To specifically answer your other questions:
                  1. If that box is checked, will that field update its value whenever displayed? Yes

                  2. How about when reports use it? Same

                   

                  3. How about when other fields in other records access its value?

                  Those calculations would also have to be unstored

                   


                  • 6. Re: Calculation Field question
                    ralvy
                       Ah. I see. Yes, I was looking at a Number field that I put a Calculation on. I forgot to use a Calculation field. I see now that it does exactly what I was used to. Thanks.