Salesforce field history tracking is a great built in feature, but doesn’t always meet the requirements of the business and can be cumbersome when trying to report on the historical data. Administrators struggle specifically with Opportunity field history reporting. Determining stage duration, how an Opportunity moves through the pipeline and related changes to the Opportunity can be challenging.
Thanks to Process Builder, Administrators now have the ability to create their own custom solution to fit the needs of the business while providing improved reporting capabilities.
Better Field History Tracking
Here’s the problem scenario I’m working with. Due to compliance reasons, the company needs to track specific fields on an Opportunity and want to be able to easily report on the previous value and new value of the fields, along with who made the change and when. The information should be easily pulled into a report for use by the compliance department. In addition, the business wants to leverage the history tracking to calculate stage duration in a more logical manner.
The solution is quite simple and will require a few components:
- A new custom object
- Custom fields on the new object
- Process Builder process
The custom object will house historical records created by the Process Builder when a field change occurs. As a result, a new record will be created which will count towards data storage limits. So, be aware that this solution may not fit all organizations.
1. Create the Custom Object
To start, we need to create a custom object and relate it to the object we want to track history against. In this case, that will be the Opportunity. The new custom object will be called Opportunity Field History and be related to the Opportunity via a Master-Detail relationship.
Related: Creating Custom Objects in Salesforce
Here are the steps to create a new custom object in Salesforce. Note that these instructions will be based on the Lightning Experience navigation.
- In the upper right-hand corner of Salesforce, click the Gear icon and then click Setup Home.
2. Using the left-hand navigation, in the Platform Tools section, click Objects & Fields | Object Manager.
3. In the upper right-hand corner of the Object Manager section, click Create | Custom Object.
4. Enter the properties of your custom object including the object name and related permissions. Here’s how my custom object is setup. After all settings have been established, click Save.
Now that the custom object is created, we need to create a series of fields on the custom object to capture the new and historical data from the Opportunity.
2. Create Custom Fields
The business wants to track the previous and new values from the Amount, Stage, and Close Date, along with who made a change to the record and when. In order to accommodate the request, we’ll need to create the following fields:
- Amount New (Currency)
- Amount Previous (Currency)
- Stage New (Text)
- Stage Previous (Text)
- Close Date New (Date)
- Close Date Previous (Date)
- Opportunity (Master-Detail)
Because we’re wanting to track and report on the previous and new value of the fields easily, two new fields for each field we’re tracking is required. Because the Process Builder will be triggered by the user who is making the change to the record, the Created By field on the Opportunity Field History object will be the user making the change to the Opportunity record and can be easily pulled into the report so there is no need to create a custom field to capture that information.
I chose a Master-Detail because I want the related Opportunity Field History records to be deleted if the Opportunity is ever deleted. That specific functionality is not available with a Lookup Relationship. The new Stage fields are text because I don’t want to have to recreate picklist values and keep the picklist up to date should those values change over time.
For more information on fields and the difference between Lookup and Master-Detail relationships, read this related article: A Primer on Salesforce Fields and Relationships, part of the Zero to Hero series.
Now that the fields are created, it’s time to create the Process Builder process!
3. Create the Process Builder Process
Process Builder is a fantastic resource to Administrators. I’m using Process Builder instead of Workflow Rules because records can be automatically created. To do this previously required an Apex trigger which often required a Developer.
In order for the new Opportunity Field History record to be created, we need to understand the logic and what field values should be captured. There are two paths this could go:
- Capture only the value of the field being changed OR
- Capture all of the fields at the time one or more of them change
I’m opting for the first option which will provide a snapshot in time for all field values, regardless of how many of these fields were changed, and manage the resulting Process Builder will be much easier should we decide to add or remove fields in the future. Here’s how we’ll create the Process.
1. In Lightning Experience, click the Gear icon then Setup Home.
2. Under Platform Tools, click Process Automation | Process Builder.
3. Click New in the upper right-hander corner of the Process Builder page to create a new process and provide a name and description. Click Save. NOTE: I tend to preface my Processes with a the name of the object in all caps so that processes are then grouped by Object. This makes it easy to find in a list.
4. Select the object that will trigger the process. In this case, we’ll select Opportunity. Because I want a new Opportunity Field History record to be created every time one of these fields is edited, I’ll choose to start the process when a record is created or edited. Click Save.
5. Next is the entry criteria. This is where we indicate what conditions must be met in order to execute the action of creating a new Opportunity Field History record. In the screenshot below, I’ve selected the option to execute the actions when Conditions are met. As a result, my criteria must equal a true statement in order to proceed.
In the Set Conditions section, I’ve indicated under what conditions to the Process will be evaluated. The requirement from the business is to capture the value of all of the fields regardless of which field was updated. So, my entry criteria are looking to see if Opportunity Amount, Opportunity Close Date or Opportunity Stage have been changed.
In the Conditions section (bottom of the screenshot) I’ve selected Any of the conditions are met (OR) which means that if any one of the three conditions evaluates to true, the related actions will be executed. Click Save to continue.
6. Click + Action in the Immediate Actions section and choose Create a Record for the action type.
7. Give the action a name, and for the Record Type, select the newly created Custom Object called Opportunity Field History. What we’re indicating here is that we want to create a new Opportunity Field History record when the criteria we just created is equal to True.
8. Now it’s time to create the field mappings. This is an easy step. I’ll be using formulas to populate most of the values on the Opportunity Field History record. To capture the old value of these fields, we’ll use a function called PRIORVALUE(). The formula is easy and is formatted in this way:
PRIORVALUE(FIELD_NAME__C)
Be sure to use the Insert Field button to populate the field. The API name is slightly different in Process Builder than in workflows.
The formula for Stage New and Stage Previous is a little bit different because we’re converting the picklist value to text. Here’s what the formula looks like for this specific field:
TEXT(PRIORVALUE([Opportunity].StageName ) )
One all of the fields have been mapped, it’s time to save the action. Here’s what this step looks like completed in Process Builder.
9. Click Activate to activate the new Process and test it out.
4. Test It!
Testing is always the fun part for me! Open an Opportunity and change one or multiple fields and save the record. Be sure to test all possible combinations of your entry criteria! As the field values are changed, new Opportunity Field History records should automatically be created and the appropriate values populated. Here’s an example.
While standard field history reporting works for most companies, there may be a use case where leveraging a custom solution is needed. Using Process Builder and a custom object is a simple way to track field history changes on any object.
Admin Hero 
Really inspiring app! Field History is not only useful to analysts but for Admins who have to research the behavior or workflow, integrations and wayward users. This could also be a model for an Apex/Visualforce version that allows you to just add fields to a Field Set to get them included in the data capture. The field creation aspect would be tricky, but the capture would actually be quite simple in Apex.
LikeLike
I do something very similar to this, so it’s nice to see someone else come up with this same idea. Love love love process builder.
Another thing you can do with this approach:
1) have a date/time field on the record that tracks ‘last stage change date’, or something similar.
2) set the value of this field via WF and field update
3) reference the ‘priorvalue’ of this ‘last stage change date’ in your process builder.
Now, whenever the oppty.stage changes, you’ll create a row in your child object, but you have the ‘start’ (priorvalue) and ‘end’ date/time (now) for each stage. And you can use this to easily calculate the ‘duration’ for each stage. And you can roll-up these values to the parent record – ex. cumulative time (duration) that the oppty was at or above 50%
LikeLike
Excellent suggestion Matt!
LikeLike
Hi Matt/Brent,
Great exercise here! I’d love to also try this – calculating the ‘duration’ for each stage but not 100% clear on steps.
1) Is the date/time field ‘last stage change date’ on the Opp or on the new Opp History object?
2) do we set a new workflow as ‘when stage changes populate ‘last stage change date’ field’?
3) do we need 2 new date fields on the Opp History object? This is to hold ‘prior value’ and also ‘today’.
Would these new fields added to the PB above at the same time?
Thanks!
LikeLike
Great article!!! … I always want to write something like that, but I never got a time.. How do you manage your time for all this:)
LikeLike
Magic! Thanks for reading!
LikeLike
This is great! I’ve been looking for a solution to this, and I think I found it!
Quick question: would this still fire when fields with history tracking are updated through a separate time-based workflows?
LikeLike
Changes done via time-based workflows should trigger this. However, I think you would need to have the “evaluate workflows for this object” checkbox checked (or whatever it’s actually called) to make that happen.
LikeLike
Hi, Question, are there any limits that need to be considered?
LikeLike
Data storage limits would be the biggest concern. Since you’re creating new records in your Salesforce org, it will begin to chew into your storage – especially if you’re tracking a lot of fields and/or objects.
LikeLike
Quick tip/note: when deploying this change set from Sandbox to Production, if the custom object contains a Master-Detail relationship field, be sure to include the M-D field additionally in the Change Set to avoid deployment failure.
References:
https://success.salesforce.com/answers?id=9063A000000stdDQAQ
https://help.salesforce.com/HTViewSolution?id=000232550
LikeLike
Good point, thanks Will!
LikeLike
Great article! This is exactly what I need to do, but on the Tasks. However, it seems I cannot create a Master-Detail relationship to the Activities object. Can you tell me how to replicate the field history trackng your talknig about for Tasks? My use case is I want to track which users “push” tasks to a future date, and track the number of times they do this, and the exact and average time to Task completion.
thanks for the great web site…I’m learning a ton!
Steve
LikeLike
Hi Steve! I’m not sure that you need this functionality to do what you’re looking to accomplish. You could build a push counter formula that adds “1” every time the date is pushed. I’ve done something similar on Opportunities and the Close Date. You could also create formulas to calculate the average and exact time for task completion. I would try to use formula field routes first because tracking these changes on tasks will result in a large number of records potentially consuming your data storage limits.
LikeLike
Brent, I have never been able to figure out the Process Builder – until now. Your tutorial was fantastic. A few steps were left out that newbies like me could have appreciated but I was able to think through it enough to figure out what they were. I used your technique above to track when a substatus of an application in my database changes. I own a recruiting company and have multiple recruiters and need to report on who moves an applicant from one stage to the next as we move the applicant through the process. I need to be able to measure this so I can see how many recruiters are setting up phone screens (substatus = PS Requested) and the date, how many recruiters have applicants where substatus is PS Set and the date that happened and on to PS Completed, etc. Your tutorial was so helpful. Now I just need to figure out how to generate a report and hopefully add that to a dashboard for reporting on each recruiter’s activity around substatus. Hopefully I’ll figure that out on my own!
LikeLike
I’m glad it was helpful Raegan! I purposefully leave out some small items in my blog posts sometimes just to be sure that folks are confident with the solution they are building! It’s a fun element of discovery and learning! Thanks for reading.
LikeLike
This is probably a simple thing and yet I’m completely missing it – but I cannot find my new custom object in the Select and Define Action step > Record Type….
Any help is SOOOOO appreciated 🙂
LikeLike
I just found the answer – I typed the name of the object in the Record Type field, and it showed up. Yay!
LikeLike
Awesome! Glad you were able to find it!
LikeLike
This is an interesting suggestion. The only thing that could create problems is if your org makes bulk updates. Process builder does not handle bulk transactions well and could cause Apex CPU Timeout issues. Outside of that, this an interesting solution.
LikeLike
Yeah, this is true. It’s the main reason I stay away from process builder in general now. I’ve just run into too many issues with it not being bulkified.
LikeLike
Use New APP Which is Native.
Very Helpful for the Mater Detail History tracking on Custom Object. Which is not available. Report can be exported in PDF and Excel Formate
https://appexchange.salesforce.com/listingDetail?listingId=a0N3A00000Ex0LHUAZ
LikeLike
You can track history for tasks records by following the below (it got a classic & lightning version):
http://www.mass-update.com/2015/10/track-field-history-for-tasks.html
LikeLike
Brent Thanks for this awesome article. My doubt is i need to track the Status field and its a dependent Pick list , and it has 15 stages. my question is do i have to create 2 fields for each stages? It would be great help , thanks in Advance.
LikeLike
I would create one field on the custom object for each of the fields you want to capture. You don’t need to show the dependancies in the custom object – just make sure each receiving field is of type TEXT and you’ll be good!
LikeLike
This is a great solution! I was wondering tho, is their a way to lock the field history custom object so users cant go in and create new field history records or edit the old ones?
LikeLike
Sorry for the add on. I understand editing can be stopped with validation rules. I guess im stuck on how to stop users from manually adding in new records and deleting old records
LikeLike
Hi Russty! I would manage this in a few ways:
1. Make the object Create & Read OWDs so that users can only see and create new records.
2. Don’t make an object tab available to the user. This would make it much more difficult to find the object and create their own records.
3. Modify the related list and remove the “New” button so that users can’t create a record through the related list.
4. Make sure the user doesn’t have Delete permissions on the object.
I think this would solve your problem. I hope that helps!
LikeLike
Can this be done with the Next Step field on the Opportunity Object? I want to ensure i track each Next Step Update.
LikeLike
You bet!
LikeLike
Hey Brent,
This is great and very intuitive. I am doing this in a sandbox and created a custom object with a master detail relationship to the account object. When I got to the process builder step of adding the action, I need to specify the account field relationship – like in your step where you use a field reference to connect opportunity to opportunity Id. I get an error message:
“The formula expression is invalid. Field Account is a picklist field. Picklist fields are only supported in certain functions.”
I’m sure there is something I missed. Any ideas?
LikeLike
I found my mistake, thank you.
LikeLike
Oh good – glad you were able to troubleshoot!
LikeLike
Hi Brent,
also first of all thank you for this post!
I have the problem that we want to track the history of a custom object under an opportunity.
We want to track the changes of our “opportunity items” when something is changed via API from the website (f.e. product added/changed)
I created a new custom object “opportunity items history” but when trying to build the process I can not access the “opportunity items”.
Do you have any idea how to make changes for our “opportunity items” visible?
Thanks
felix
LikeLike
Hi Felix! I am assuming that you’re talking about the Product Line Items on the Opportunity? I haven’t played with that object before as it relates to Process Builder but I would assume that if you can’t build off of it via Process Builder, then it’s not available. You could try doing this with Flow instead, or you may need to look at an Apex trigger.
LikeLike
I am a bit nervous about manipulating the administrator dashboard, I would be curious to connect to talk about optimizing my current instance of salesforce.
LikeLike
Hi Brent, first thank you for the article. Second could you explain what advantage I get with having a before and after fields for each field I’m tracking vs having one before and one after field, and a picklist field called Field Type containing the fields that I want to track (similar to the standard functionality of field tracking)?
LikeLike
Hey Rachel! I’m not entirely sure that I understand your question. The point of the Before and After fields is to get around a limitation that Salesforce enforces on long area text fields and field history tracking. You can only see that the field was changed – you can’t see what was specifically changed. I suppose if you wanted use a Field Type field as part of your solution and track details that way you could, but I wouldn’t go so far as trying to replace all of Salesforce’s built in field history tracking with this solution. It would be excessive and could drive up your data storage limits.
LikeLike
Hi Brent! Thanks for the article. Exactly what I needed to bypass some reporting limitations with field history on Account Teams. I’m now successfully tracking record edits, however, not able to track prior values when records are deleted. Do you know of any workarounds to track details upon deletion?
Only thought that comes to mind would be removing delete permissions, adding a new ‘delete’ button that would 1) create a field history record and 2) call a flow to delete the intended record.
LikeLike
Capturing data in deleted records is more difficult. Your solution sounds like it would work, but oh what a pain! This is generally where I would call in a developer (if you have one at your disposal) to write a trigger for this. But, if you don’t have a developer handy, I believe your solution would work great. Just be sure to think of all the places a record can be deleted from to sure your custom actions work and there are no functionality gaps.
LikeLike
Hi Brent,
How can we deal with picklist (multi-select) type? thanks!
LikeLike
Hi Brent,
When choosing between ‘ Capture only the value of the field being changed ‘ OR
‘Capture all of the fields at the time one or more of them change’ – what types of reasons would make you choose one over the other?
LikeLike
Hi Brent
Thanks for the guide. I have it working, but with one small problem.
The Process fails if you have Duplicate Management on.
We can’t save this record because the “AMH Create/Delete” process failed. Give your Salesforce admin these details. This error occurred when the flow tried to update records: DUPLICATES_DETECTED: Use one of these records?. You can look up ExceptionCode values in the SOAP API Developer Guide. Error ID: 627911960-1103020 (-1548041299)
Its a known issue, but does not look like it will be fixed by SF –
https://trailblazer.salesforce.com/ideaView?id=08730000000oMVgAAM
There are workarounds, but I have not had any success with these so far.
LikeLike