Hi,
Sorry that this is a bit of a basic question. I have recently picked up the admin of this from someone who has left and I’m new to it!
We have a formula field that needs to be changed as it’s not correct. However when I’m looking at existing records they have values that do not match the current formula. Are formulas maybe only applied when a record is created and then a value can be overwritten by a user and it will not revert to the formula value?
I want to change the formula but leave existing records unaffected.
Thanks very much, Mark.
Hi,
If you execute formulas recalculation to all records from design mode or if you edit the record with the referenced value, the formulas will be executed recalculation that the value will be overwritten with the new result.
In your case, I would suggest you to remove the formula in that field and create a new field to apply the formula you need, and use this new field to retrieve the result.
Before applying the new formula you need, you can first apply formulas that equals to the original field to retrieve the current value to the new field (execute formulas recalculation once the design changes is saved), then apply the new formula you need. So that even if the previous value is changed by the new formula, you still have the old field to refer to.
Or even more, you can write “IF() conditional” formula to identify if the old field has a value, take that value, if not, use the new formula to calculate.
Let’s say your original field is A1 (with formula A2A3), and the newly created field is C1 (you would like to change the formula to A2A3*0.5), the step would be:
- remove the formula in A1
- apply formula in C1 that equals to A1, save design changes and apply formulas recalculation
- assign formula in C1: IF(A1.RAW!="",A1, A2A30.5) , which means, if the original field value A1 is not empty, take that value, otherwise calculate the result by “A2A30.5”.
In this case, you will have value of all records retrieved in the new field.
1 Like