Calculations and Auto-Enter Calcs

This is fairly basic, but there are several aspects of Calcs and Auto-Enter Calcs that are very important to grasp, and the decision about which to use is sometimes not as straightforward as it might appear.

The data in a simple Calculation field cannot be changed, whereas the contents of an Auto-Enter Calculation can be overridden.  So it may be best to think of an Auto-Enter Calc as an ordinary number field which is initially populated via a calculation.

Updating an Auto-Enter Calc

Remember that, if you have NOT ticked “Do not replace existing value of field (if any)”, the Auto-Enter Calc will update if any of the fields within the calculation are changed.  If you want to “lock” the contents of the Auto-Enter Calc, make sure that this option is ticked.

Note that, even if this option is NOT ticked, the Auto-Enter Calc will refresh ONLY if at least one of the fields on which the calculation depends are local.  Related fields can be used in the calculation, and will make the initial calculation successfully, but the field will NOT update when related fields are changed.  So Auto-ENTER Calc is the right name for it – it’s not an Auto-UPDATE Calc when it depends on related fields.

Example

In the example, RBK_cost_autoenter_calc is defined as sessions::SSN_duration * resources::RSC_rate_ph. When data is first entered into the 2 related fields, the cost is calculated accordingly.  But when either or both of those values changes, the calculated value in RBK_cost_autoenter_calc remains unchanged.  By contrast, RBK_cost_calc, an “ordinary” calc field with the same calculation behind it, is updated when either of the values in the calculation changes.  But, as an unstored calc, the value in the field cannot be overridden.

So it’s really about understanding the behaviour of the 2 types of field, and making use of the behaviour.  In the room booking example above, it’s probably a desired behaviour that the calculated cost of a resource booking doesn’t change when the hourly rate changes (e.g. going up with inflation, which you wouldn’t want affecting old bookings), but you would almost certainly want the cost field to change when the session duration changes.

If you do need an Auto-Enter Calc field to be updated when related fields are amended, these are some possibilities:

  • Use a script trigger to update the Auto-Enter Calc on modification of the related field(s).
  • Duplicate the related field(s) in the local table.

A combination of the above is also possible – perhaps do a triggered recalc when the duration changes, which uses the new session duration and the locally stored hourly rate.

Advertisements

6 Comments on “Calculations and Auto-Enter Calcs”

  1. […] a more comprehensive look at calc and auto-enter fields, see here.  Thanks, as ever, to the Technet contributors who combine to make it such a great […]

  2. fmpsoft says:

    “Auto-Enter Calc will refresh ONLY if all the fields on which the calculation depends are local.”

    Are you sure that ALL the fields in auto-enter calc have to be local in order to refresh the value?
    Auto-Enter should be able to refresh if only one field is local and that one gets changed.

    • Dave says:

      You’re quite right – thanks for the correction. So I should say that if ANY of the fields on which the auto-enter calculation depends are local, the acalc will refresh.

      This has made me realise the potential of having the best of both worlds. This seems to work fine:
      1. Put a “trigger” field in the local table – initially containing one.
      2. Incorporate that trigger field in the autoenter calc (i.e. duration * rate * trigger in the example above).
      2. On the relevant fields in the related records (i.e. duration and rate), put a single step “on modify” triggered script, which does a “replace field contents” of the trigger in all related recs (replacing with 1).

      As long as the trigger is always set to 1, it will have no effect on the calculation, other than to force it to refresh. But it can still be stored (therefore indexed) because it’s “only” an autoenter, not a calc. A bit fiddly, but possible a useful workaround.

  3. stuart says:

    I came to this page after searching about a problem i was having. Thank you for clearing up a few questions I had.
    One thing i noticed with my situation is if a related field is NOT part of the auto-calc but is part of the record. Then I loose ALL of my data that was copied to another table:record.

  4. Mike J says:

    Excellent description. Thank you very much.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s