How to track time spent in each stage of a custom detail object

Question

So we have a custom detail object which uses a custom stage picklist field which we use for the path. We also have a custom status picklist field with values as unclaimed, In progress and completed to indicate if its either assigned, being worked on or completed. the status value corresponds to each stage and gets reset(unclaimed ) when moved to the next stage and the stage values progresses linearly.

we now want the ability to report days spent on each of these status values for different stages.

One not so ideal solution would be creating custom field for each status value for each of the stage values which would mean if there is 7 stages, we would need 7*3 21 fields that holds the days spent value.

Is there a better solution to handle this

Answer 1

Sam!

You can try to enable the Track Field History feature in your custom object:

Then, to track the time spent in each status picklist value create two fields, a process/flow to update them whenever your status changes, and finally a report in your Custom Object History.

Fields

• Last_status_update__c (Datetime):

Will get the current date-time when the Status__c field changes.

• Status_duration__c (Text):

Will concatenate the Stage__c + Status__c + status duration (current datetime - Last_status_update__c)

Don't forget to enable the History Tracking for those fields.

Flow

To fill those fields create a new Record-Triggered Flow in your custom object, it will seem like that:

Formula to calculate the status duration time (in minutes):

({!$Flow.CurrentDateTime} - {!$Record__Prior.Last_status_update__c}) * 24 * 60

Formula to concatenate stage, status, and status duration:

TEXT({!$Record__Prior.Stage__c}) & " | " & TEXT({!$Record__Prior.Status__c}) + ": " & TEXT({!statusDurationFormula}) & " minutes"

Report

Finally, create a report on your custom object history and you will have the status duration tracking by stage:

Hope it helps!

Answer 2

You could add a date/time field to the object to track the date/time that the record entered the current stage

When stage changes, use an after save flow to insert a child object record. On the child object record store

• the previous stage name
• time that it was in the previous stage (current time minus the date/time it entered the current stage)

And then update the date/time field to the current time.

Seems to me like that would work, esp if the stages are sequential and never go backwards.