How to track the Case Age of each Status

I’ve been running into many scenarios where people want to track the Age of each status (be it Case or another Custom Object).  We’ve got things like Field Tracking that we can use to get close, but the data isn’t very easy to build dashboards and reports on.  So, my mind immediately went back to one of my first posts and Flows, Case Time Tracking.  In this post we were wanting to see how long the Users had worked on a Case, and not how long our Cases were staying in a status.  But architecturally we’re going to be pretty similar.

Automation Overview:

  1. Case Meets Criteria – Process Builder triggered (Pass inputs into Visual Flow)
  2. Flow Checks to see if existing open Record
  3. Flow Updates existing record if possible
  4. Flow Checks to see if Case is still Open, exists if Closed
  5. If Case is Open, Flow creates new Record

We will be creating a few fields on our Custom Object:

  1. Case [Master-Detail Lookup]
  2. Start Time[Date/Time]
  3. End Time [Date/Time]
  4. Status [Picklist – Mirror Case’s values]
  5. Minutes [Formula, Number, 2 decimals]

For our Minutes Formula we want to use this:

IF ( ISBLANK ( End_Time__c) , 0 , ( End_Time__c – Start_Time__c ) * 1440 )

Depending on your business case, you might want to change this to be Hours or Days instead of Minutes.  Also, you might want to make 

Here is what our finalized Object looks like:

Case Age 2

Now that we have our Object ready, we can go create our Flow (Setup | Create | Workflows & Approvals | Flows).

NewFlow.jpg

The first step is going to be grabbing our Record Lookup.

RecordLookup.jpg

We are going to be doing a Lookup on our Custom Object we just created to track the Status Ages.  We want to first check to see if one is already open on this Case, and if we need to stamp the open record with an End Time.  If not, then we can ignore going to a Record Update and go to a Record Create if the Case Status is not closed.  For our Record Lookup, we need a variable for our Case’s Id and our Existing Status Age Id (our new Custom Object).

var_CaseId.jpg

StatusAgeId

Which gives us our Record Lookup.  If you notice, we are using IS NULL with the Start and End Date fields.  We simply want to find a record that has a Start Date but doesn’t have an End Date.  That would equate to being an open record that we need to update with an End Dat

Record Lookup for Case Status Age.jpg

Set the Record Lookup as our Start Element

startElement.jpg

Now, we want to drag a Decision Element into our canvas to determine if we found a Record in our Lookup or not.

Add Decision to Flow.jpg

Call the Decision Element “Existing Record?”.  For our first Decision Criteria or Outcome to be checking to see if our variable of the Case Status Age is null or not.

Decision in Flow.jpg

Now, we want to drag in a Record Update element to update the existing Case Status Age record, if we found one.

DragOutRecordUpdate.jpg

We will want to use for our criteria the Id of the record we found in our Lookup.  For the fields we will update, we want to use the System variable, CurrentDateTime

RecordUpdateCaseStatusAge.jpg

Update End Date.jpg

Now, lets drag out another Decision Element.  We need to determine if our Case is Closed or not.  If it is Closed, we don’t need to create another record.  If it is Open, then we will create another Case Status Age record.  So, lets call the Decision Element “Case Closed?”.

AnotherDecision.jpg

We want to create a variable called var_CaseIsClosed that will be passed in from the Process Builder.  Note: this is a BOOLEAN field.  We will check to see if var_CaseIsClosed is FALSE.  If so, we will proceed to our Record Create, but if it is TRUE we will let it exit the Flow.

CaseIsClosedBoolean.jpg

CaseIsClosedDecision

Hit OK, and we now get to map our elements together.  Notice, we map our Record Update to our second Decision Element.

MapDecision.jpg

Now we need to drag out a Record Create.

DragOutRecordCreate.jpg

We need to create a variable called var_Status, which just like var_CaseIsClosed, will be passed in through our Process Builder.

var_Status.jpg

We now will setup our other inputs on the Record Create to be our Case’s Id and the Start Date (which we will use the System Variable again).  This is our last element of the Flow, so we don’t need to assign the Record ID to a variable.

CreateCaseStatusAge.jpg

Now, we need to map our Decision to the Record Create.

FinalizedCaseStatusAgeFlow.jpg

Lastly we want to Save and then Activate our Flow!

SavedFlow.jpg

Note: I like to append “- Flow” to my Autolaunched Flows.  This makes things clearer when I am dealing with them on the backend since Flow and Process Builder both are in the Flow metadata folder.

ActivatedFlow.jpg

Fantastic!  Now we need to setup our Process Builder that launches this Flow.  So lets go create a new Process Builder for this.  (Setup | Create | Workflows & Approvals | Process Builder)

NewPBCaseStatus.jpg

We want the Process Builder to fire on our Account, so for our object select Case, and for starting the process select when a record is created or edited.

CaseStatusPB.jpg

Now we need to setup our Criteria.  Unfortunately an ISCHANGED function doesn’t run on new Cases.  So we have to break out the formula editor and use this formula:

ISNEW() || ISCHANGED([Case].Status)

CaseStatusChanged

Now we get to setup our Immediate Action of launching a Flow.  All we need to do is pass in our Case Id, Case Status, and our IsClosed field.  Note: the IsClosed is listed as Closed in Process Builder.  IsClosed is the API Name for that field.

Set Case Status

Hit Save, and then hit Activate up in the top right corner… and you’re done!

5 thoughts on “How to track the Case Age of each Status

  1. Les November 7, 2016 / 3:35 pm

    It looks like you left something out on this line: “Depending on your business case, you might want to change this to be Hours or Days instead of Minutes. Also, you might want to make ?[. . .]?”

    What might we also want to make?

    Thanks for this idea! Top notch work you do here.

    Like

    • David Litton November 9, 2016 / 11:48 pm

      Les, right now I have no clue what I was going to write there. I think I had an idea and then realized it made no sense… I’ll get that post updated so it doesn’t confuse anyone else, and update it if I do figure out what I was getting ready to write :). Thanks for the feedback!

      Like

  2. Naveen Dhanaraj February 26, 2017 / 4:27 am

    Reblogged this on Naveen Dhanaraj and commented:
    Calculate Time spent on Case Status By David Litton.I had Implemented this. Good way to Go and Got Expected Results

    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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s