Salesforce: Counting How Many Times Something Happens

In a previous post, we explored setting a time stamp for when a field is changed to a certain value, for example, we want to capture when an Opportunity Forecast Category is set to Commit.  Today, we look at how to count an occurrence in Salesforce.  These will form building blocks for reports and dashboards.

To review, the workflow rule is really simple.  It needs to fire when the record is new, or when it is edited and the criteria is met.  

The behavior will then be every time the Forecast Category is set to Commit, and it wasn't previously "Commit", the workflow will fire.  If we only want to fire the first time, then add a criteria that your Time Stamp field is blank.

The Field Update is equally simple.  It simply sets a Date/Time field to NOW().

But of course, an enterprise sales cycle is long, and an opportunity may move back out of "Commit", and then again to Commit.  Each sales organization may have a threshold of what is acceptable.  Things happen, what a sales person thought was a done deal may hit some snags, and he will need to move it back to Best Case.

But how do we capture the number of times the Opportunity Forecast Category is set to Commit?

What we need is a counter!

First, we need a number field on the opportunity.  Let's call it Commit Counter.  It needs to have a default value of zero.  If you have existing opportunities, the new field will have blanks.  You can use Data Loader to update, but that is optional as you will see later.

Then we will need a workflow rule to fire when a the Forecast Category is set to Commit.  In fact, the workflow above will also work.  If you need to add more conditions, for example, exclude System Administrators, you could start with the following criteria:

AND (
   ISCHANGED( ForecastCategoryName ),
   PRIORVALUE( ForecastCategoryName ) <> "Commit",
   ISPICKVAL (ForecastCategoryName, "Commit")
)

The important step is to have the right field update on your Commit Counter field.  In the above criteria, notice the use of the PRIORVALUE function.  It takes whatever value was in that field before the update.

But if your Salesforce org has existing opportunities, the new field will be blank unless you populate it with zero, or by going through the exercise of extracting the Opportunity Field History object, counting the instances for each opportunity, and updating your old records that way.

Since we will accept that we will only get the count of opportunities set to Commit moving forward, we will need a field update that looks like this:

IF ( ISBLANK (Commit_Counter__c),1, PRIORVALUE( Commit_Counter__c ) + 1)

This sets the field to 1 if it is currently blank, otherwise, it increments the field by one.

Activate the workflow then test it.  Add conditions as necessary based on your business requirements.

And there you have it: a field that counts how many times an opportunity goes into Commit.  This can be used as part of your pipeline or win/loss analysis, or simply part of a larger pipeline quality dashboard.