2 Replies Latest reply on Jun 17, 2015 10:31 AM by philmodjunk

    Calculation Field



      Calculation Field


      What is the difference between how a field is setup to be a calculation.

      - Field name > Type: Number > Options: (the calculation)    or  

      - Field name > Type: Calculation > Options: (the calculation)


        • 1. Re: Calculation Field

          Some basics:

          The first way is an auto-enter calculation, with varying options, also used in place of Look up. When you do it this way, you can have the field auto enter a calculation, and still have the ability to edit. 

          The second way is a straight calculation field.  You will not be able to edit it on the layout.

          Do you have a specific dilemma you are trying to work through?

          • 2. Re: Calculation Field

            The first option will always be a stored, indexable field as long as global storage is not specified. BUT, if the fields referenced in the calculation do not come from the same record, the calculation will not automatically update when the referenced field from another record is modified.

            Also, changing or adding a an auto-enter calculation will not automatically enter or update data in records that already exist in the table. You have to take additional steps to update the value in this field for existing records.

            The second option will always update when any referenced field is modified. This can be a desirable or undesirable characteristic depending one what you need. (If this is a calculation on an invoice, you don't want such a calculation to re-evaluate on all past invoices when the business manager updates prices...)

            But this "always updates" feature has a hidden cost: If the fields used in the calculation are summary  fields, global fields or fields from a related table occurrence, the field will be an unstored calculation. Such fields evaluate only as they are displayed on the layout--which can be a good thing in some cases, but if you have a lot and especially if you have some that pull data from large numbers of records, it can slow down the speed at which the layout refreshes. Searches and sorts on unstored calculations are much slower than those that refer to stored/indexed values. This difference will not be noticeable with small groups of records, but can be very painful to completely unusable when there are very large numbers of records.

            And even calculation fields will not update if they are set up as stored calculations and the calculation does not reference another field.

            Get ( CurrentDate)

            For example, must be put in an unstored calculation field or the date shown in the field does not update when the current date changes. (It's the change to a field from the same record, used in the calculation that triggers the update of both stored calculation fields and auto-entered calculations--when "do not replace existing value" is specified in the second case)