User-Override on Calc Fields?

[clrblue]

Hi there... yes, I am STILL working on the billing and employee db I have been working on, but actually, I can see the light now. Anyway, I do have a question, if someone has the time...

Sometimes in an accounting system, although standard values are pulled to a field via calculations, the financial person in charge of data entry may want to change the value. Is it possible to create a calc field that allows a user to change the value? I can't imagine this hasn't come up previously, so I am hoping that one of you will be able to help me.

TIA,

KC

[BobWeaver]

A couple of possibilities:

1. Change the field type from calculation to a standard text or number field and use the auto-enter calculation option to set the initial value which the user can then overwrite.

Or..

2. Create a separate field (called OverrideField in the example here) that the user enters the override values into, and set up your calculation field formula like this:

Case(IsEmpty(OverrideField),<<Put the default formula here>>,OverrideField)

You can then put both the calculation field and the Override field on the same location on the layout with the calc field superimposed over the override field. Set the calc field format options to not allow entry. So, when the user click onto what he thinks is the calc field he goes into the override field where he can enter a different value.

[CobaltSky]

If you want the calculated value to update - and to overwrite the user-edited value - if any of the source values are changed, here is another approach you could consider:

For this method you'd need to create a calculation field, a number field, a trigger calculation field and a relationship, and set the number field to lookup the calculation result based on the relationship.

The relationship should be a self-join which matches the trigger field to an existing constant field (eg a record ID number or serial number field), with a formula which is dependent on one or more of the fields that your calculation depends on.

For example, if your calculation is FieldA + FieldB, your trigger field could then use the formula:

Case(FieldA + FieldB, Status(CurrentRecordID), Status(CurrentRecordID))

and the relationship could match the trigger field to a field with the formula: Status(CurrentRecordID).

That way, when the values in either FieldA or FieldB change, the lookup will be triggered to update and the number field will acquire the new resulting calculated value (from your original calculation field - which need not now appear on any layouts) - but in the meantime the calculated value will be directly editable by the user.