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.

  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.

36 thoughts on “ Building Better Field History Tracking ”

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

    Like

  2. 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%

    Like

    1. 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!

      Like

  3. 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?

    Like

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

      Like

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

      Like

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

    Like

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

      Like

  5. 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!

    Like

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

      Like

  6. 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 🙂

    Like

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

    Like

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

    Like

    1. 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!

      Like

  9. 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?

    Like

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

      Like

      1. 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!

        Like

  10. 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?

    Like

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

    Like

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

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.