Calculation Does Not Store

[steigrafx]

I have a field called Service_Company, which is populated from a drop-down value list. Occasionally, actually rarely, the original service company goes out of business and a new company is assigned. I can only recall this happening twice.

I thought it would be a good idea to add a new calculated field called Original_Service_Company, which would populate with the original service company during data entry. I unchecked the "Do not store calculation results" checkbox, yet the results are not stored. Whenever the Service_Company field is changed, the calculation runs and stores the new service company in the Original_Service_Company field.

Maybe I'm misunderstanding the "Do not store calculation results" concept. I thought by unchecking the box that the results would then be stored for posterity.

As always, your help is appreciated.

Thanks,

Kevin

[comment]

A stored calculation field is not "stored for posterity". It re-evaluates when a referenced field is modified.

[steigrafx]

Thank you. Is there a fairly simple way to accomplish what I need, or should I be thinking along the lines of an audit trail?

[Barbecue]

Probably the simplest thing would be to change Original_Service_Company to a text field. Give it an auto-enter calculated value of Service_Company, and make sure the "Do not replace existing value" box is checked.

Unstored and stored calculations both recalculate based on changes to the values they are based on, but the value of stored calculations is that when a new value is calculated, it is actually stored, so it can be indexed. That lets you do things like use them for relationships and fast finds, which unstored calcs can't do. The disadvantage is that they take up space in the database, while unstored calculations are computed on-the-fly, so they use only a trivial amount of storage.

[bcooney]

"I have a field called Service_Company, which is populated from a drop-down value list."

In what table? Is this a foreign key? Perhaps the Service_Company should be a child table with a StartDate and EndDate? Do you have a table for the ServiceCompanies, each record with a unique ID?

[comment]

It's an audit trail any way you look at it. To keep it simple, try making it a Text field auto-entering a calculated value (replacing existing value) =

List ( Self ; Service_Company )

[steigrafx]

Simple, and perfect. That does exactly what I need it to do.

Thanks, Comment.