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

9. Click Activate to activate the new Process and test it out.

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

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 Basics

    Building Better Field History Tracking

    Tweet Pin Share ShareSalesforce field history tracking is a great built in feature, but doesn’t always meet the requirements of the ...
Load More Related Articles
Load More In Admin Basics

22 Comments


  1. Luke C

    Luke C

    April 18, 2016 at 11:57 am

    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.

    Reply

  2. Matt

    April 19, 2016 at 8:42 am

    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%

    Reply

    • Brent Downey

      April 22, 2016 at 7:54 am

      Excellent suggestion Matt!

      Reply

    • Jane

      July 17, 2016 at 3:08 pm

      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!

      Reply

  3. Ayub

    April 20, 2016 at 10:38 am

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

    Reply

  4. Tomás

    May 9, 2016 at 9:48 am

    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?

    Reply

    • Brent Downey

      June 4, 2016 at 3:22 pm

      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.

      Reply

  5. Jayson Bourne

    May 19, 2016 at 2:31 pm

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

    Reply

    • Brent Downey

      June 4, 2016 at 3:24 pm

      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.

      Reply

  6. Will

    May 31, 2016 at 3:55 am

    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

    Reply

  7. Steve Bruss

    December 11, 2016 at 5:43 pm

    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

    Reply

    • Brent Downey

      January 3, 2017 at 2:46 pm

      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.

      Reply

  8. Raegan Hill

    December 18, 2016 at 9:48 am

    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!

    Reply

    • Brent Downey

      January 3, 2017 at 2:36 pm

      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.

      Reply

  9. Christine Kielty

    April 21, 2017 at 12:43 pm

    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 🙂

    Reply

    • Christine Kielty

      April 21, 2017 at 12:51 pm

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

      Reply

  10. marc

    June 12, 2017 at 6:49 am

    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.

    Reply

    • Brent Downey

      June 27, 2017 at 2:12 pm

      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.

      Reply

  11. Meena

    August 9, 2017 at 12:36 am

    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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Sponsors