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.
- Case Meets Criteria – Process Builder triggered (Pass inputs into Visual Flow)
- Flow Checks to see if existing open Record
- Flow Updates existing record if possible
- Flow Checks to see if Case is still Open, exists if Closed
- If Case is Open, Flow creates new Record
We will be creating a few fields on our Custom Object:
- Case [Master-Detail Lookup]
- Start Time[Date/Time]
- End Time [Date/Time]
- Status [Picklist – Mirror Case’s values]
- 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!
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.
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!
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
Thanks for this post, David. It was super helpful.
Do you know if there’s a relatively easy way to make this go by business hours timing instead of a 24 hour clock? My issue is I have an offshore team doing data entry to open new cases after hours and they’re processed when my team arrives to the office in the morning.
Alex, If your BH are set, then you could use a formula field to calculate it. Otherwise, you probably can use some Apex methods to do it.
Thanks, David. I’m working on this now.
One other question – how would I change this to make it just track how long a case is opened as opposed to how long it’s in each status? I didn’t realize this is creating a timer for each status change, I just want from open to closed and have it adding on time each time it’s reopened. With our process we have a lot of cases that get reopened days or weeks later and we need to report on total case time to clients so this helps a lot if it’s setup right.