function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Adrian-EAdrian-E 

Calculate Time spent in custom field

We have a custom field called WAITING_FOR on the case level with 4 options:

 

SUPPORT

DEVELOPMENT

PRODUCTION

CUSTOMER 

 

We'd like to populate 4 additional fields on the case level indicating the time spent in each status in days.

 

How can we achieve this with triggers? 

Best Answer chosen by Admin (Salesforce Developers) 
jrotensteinjrotenstein

Sounds like you'd need:

  • 4 fields to store the accumulated time for each status
  • A timestamp field to store the date of last change
  • A trigger that looks at the transition

The Trigger would need to look at the 'old' value of the status field. If it belongs to one of the buckets, it should take the duration between 'now' and the last change (as stored in the 'last change' field) then add it to the bucket for the appropriate status.

If you want real accuracy, you may want to include the 'current' time against the current status. For example, if it is currently in SUPPORT and has been there for 2 days, you might want to show those two days in the accumulated Support bucket. The Trigger hasn't yet triggered (since it is still in that status), so the Support field doesn't reflect those two days. Instead, add 4 formula fields that show the value of each field PLUS the current duration if it matches that status. So, the Support formula field would show the stored support duration plus 'now' - last change, but only because the status is currently Support. The Customer formula would just show the stored value, not the current duration since the status is not Customer. (Confused? Happy to explain better if you need it.)

Oh, and one further thing. You'll probably want a workflow that fires when the object is 'closed' that clears WAITING_FOR, just in case it is still in a WAITING_FOR state. For example, if it is set to 'Production' and the object is closed, you want to stop the clock. Could probably be done by clearing the WAITING_FOR in such a case.

All Answers

aalbertaalbert

In the past, I have accomplished this using formula fields and identifying the different transitions (from value A to value B).

You will need a set of fields to set when the specific WAITING_FOR status was set. And then you can use other formula fields to do the math on TODAY() - "previous date the value was set". 

 

 

 

jrotensteinjrotenstein

Sounds like you'd need:

  • 4 fields to store the accumulated time for each status
  • A timestamp field to store the date of last change
  • A trigger that looks at the transition

The Trigger would need to look at the 'old' value of the status field. If it belongs to one of the buckets, it should take the duration between 'now' and the last change (as stored in the 'last change' field) then add it to the bucket for the appropriate status.

If you want real accuracy, you may want to include the 'current' time against the current status. For example, if it is currently in SUPPORT and has been there for 2 days, you might want to show those two days in the accumulated Support bucket. The Trigger hasn't yet triggered (since it is still in that status), so the Support field doesn't reflect those two days. Instead, add 4 formula fields that show the value of each field PLUS the current duration if it matches that status. So, the Support formula field would show the stored support duration plus 'now' - last change, but only because the status is currently Support. The Customer formula would just show the stored value, not the current duration since the status is not Customer. (Confused? Happy to explain better if you need it.)

Oh, and one further thing. You'll probably want a workflow that fires when the object is 'closed' that clears WAITING_FOR, just in case it is still in a WAITING_FOR state. For example, if it is set to 'Production' and the object is closed, you want to stop the clock. Could probably be done by clearing the WAITING_FOR in such a case.

This was selected as the best answer