How do I keep a calculated value from changing once it is calculated?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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?


AJ

Photo of AJ

AJ

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 804 Points 500 badge 2x thumb
When using formulas, I am not aware of any way to "freeze" a result.

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([DateField]<ToDate("7-1-2015"),10,11)

If it changes again on 8/1/2015 to 12:

If(

[DateField]<ToDate("7-1-2015"),10,

[DateField]<ToDate("8-1-2015"),11,12)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
No problem.
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.
Photo of Vincent

Vincent

  • 10 Points
Maybe a simple approach can be used.

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).
Photo of AJ

AJ

  • 0 Points
Thanks for all the input. I ended up making two fields for each price break. One is a calculated number and the other is a number. I created a checkbox labeled "lock price" and set a form rule that writes the value from the calculated field to the number field when the box is checked. Another form rule checks the box when the record is saved.

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.



AJ