Getting custom field old values using formula field

Question

I have a phone field in custom object which stores phone number which is updated when we get new phone numbers. I need to find the old phone number using formula filed. Can anyone please help me in getting the solution. Thanks in advance

Answer 1

The general consensus is to use a second field with a workflow to capture the old number which I totally agree with. One approach worth considering is to make the 'Old Phone' field a text field and append the numbers as they change so you have a rolling history:

1. Create a workflow with 'ISCHANGED(Phone)' as the condition using 'formula evaluates to true' instead of 'criteria met'
2. Do a field update (text not phone) with the following formula: Phone & ", " & Old_Phone__c

You can get fancy with the formatting (comma's and line breaks) but you get the idea.

And if you need something more robust with more history and granularity such as who and datetime of when then I would consider creating a custom object to capture these audits and using a small apex trigger to write the changes. The benefit here of course is ease of reporting, managing, and understanding the data.

Answer 2

You may want to check the following functions:

• PRIORVALUE: Returns the previous value of a field
• ISCHANGED: Compares the value of a field to the previous value
• ISNEW: Checks if the formula is running during the creation of a new record

Answer 3

Formula fields are calculated at runtime whenever someone views the field or it's referenced in another formula. The values are not stored in the database, so you can't reference historical data in the formula field.

You can use workflow rules with a field update or Process Builder to store the last value in another standard phone field.

1. Create a custom phone field to store the value (e.g. Old Phone).
2. Create a new process. Choose your object (I'll use Contact in this example) and set the criteria to "Start the process when a record is created or edited"
3. Set the Criteria for Executing Actions to "Filter conditions are met" and set the Set Filter Conditions to Is Changed = True.
4. Add an Immediate Action of type Update Records, choose the your object, and set Old Phone to the value of your phone field.

Once you save and activate your process, this will update the Old Phone field whenever the other phone field is changed.