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:

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

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

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

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

Set the Record Lookup as our Start Element

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

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.

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

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

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

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.

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

Now we need to drag out a Record Create.

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

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.

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

Lastly we want to Save and then Activate our Flow!

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.

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)

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.

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)

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.

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