I have a table that calculates 5 different price breaks based off a cost. Each of the price breaks has a different margin that is added to the cost. I am anticipating a time when our standard margins might change, but I don't want the existing pricing to update to follow the new margins. Is there a way to freeze these values after they are originally calculated?
I assume you have a date field. You can use the date field with your margin. For example, the margin field can be a formula field and if the margin changed on 7/1/2015 from 10 to 11, the margin formula would be:
If it changes again on 8/1/2015 to 12:
Set up a table of standard margins. Make 1 record and load it up with the margins. It will be Record ID# 1.
On your details table make a formula numeric field with a formula of 1.
Make a relationship based on that field and pull down the standard margins into your details tables, and name them like [Margin 1 (lookup)]
Then make a duplicate set up numeric fields just calked like [Margin 1] and at the bottom of the field properties set it to be a snapshot of the corresponding lookup field. You can also ad the Help text for the keyword snapshot.
So what happened is that when a new detail record is created, it will pull in the current set of Margins on lookups and then immediately freeze them in the snapshot fields. You will ONLY use the snapshot fields in any calculations.
Create 5 margin fields with each having a default value of the current margins. This will put the value into the field at the beginning and never change. If these need to change, just change the default values and all new records will have the new value and the old will be left alone. Then simply calculate the cost using these fields.
Another option following Mark's concept would be Variables that populate one or more formula fields and you can use Form Rules to set the margin fields to the variable upon saving (conditional that Date Created = Today).
Once I have the salespeople using the form i'll get a better feel if this was a good way to do this, if not, i'll probably end up doing what Mark suggested.