Building Better Field History Tracking

Building Better Field History TrackingSalesforce 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.

  1. In the upper right-hand corner of Salesforce, click the Gear icon and then click Setup Home.

Setup-Home

2. Using the left-hand navigation, in the Platform Tools section, click Objects & Fields | Object Manager.

Object-Manager-Lightning-Experience

3. In the upper right-hand corner of the Object Manager section, click Create | Custom Object.

Create-Custom-Object-Lightning-Experience

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.

Custom-Object-Definition

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:

  1. Capture only the value of the field being changed OR
  2. 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.

Setup-Home

2. Under Platform Tools, click Process Automation | Process Builder.

LEX-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.

New-Process

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.

PB-Setup

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.

PB-Entry-Criteria

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.

PB-Create-a-Record-1

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.

Opp-Field-History-Record-1

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.

Load More Related Articles
Load More By Brent Downey
Load More In Admin Basics

28
Leave a Reply

avatar
14 Comment threads
14 Thread replies
2 Followers
 
Most reacted comment
Hottest comment thread
16 Comment authors
RusstySelviBenMeenamarc Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Lucas Cushanick
Member

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.

Matt
Guest
Matt

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… Read more »

Jane
Guest
Jane

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!

Ayub
Guest

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:)

Tomás
Guest
Tomás

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?

Jayson Bourne
Guest
Jayson Bourne

Hi, Question, are there any limits that need to be considered?

Will
Guest
Will

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

Steve Bruss
Guest

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

Raegan Hill
Guest

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… Read more »

Christine Kielty
Guest
Christine Kielty

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 🙂

Christine Kielty
Guest
Christine Kielty

I just found the answer – I typed the name of the object in the Record Type field, and it showed up. Yay!

marc
Guest
marc

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.

Meena
Guest
Meena

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

Ben
Guest

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

Selvi
Guest
Selvi

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.

Russty
Guest
Russty

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?

Russty
Guest
Russty

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

SUBSCRIBE ALREADY!

SUBSCRIBE ALREADY!

Join the bomb diggity Admin Hero email list and never miss a post. Like, never ever!

Hizza! You're subscribed! Nothing but good times ahead!