0

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

2 Answers 2

2

Sam!

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

Custom object optional features setup

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:

Flow example

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:

Report example

Hope it helps!

1
  • 2
    Field history is a good suggestion just note it only is saved for 18 months unless you pay for extended history tracking . Odds are that is long enough, but worth noting. The report will only show edits made in the last 18 months. That's why I went for custom object.
    – gorav
    May 7 at 2:53
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.