Custom Object Field Tracking

Question

We are trying to track the duration (time spent in days/hours) in each Status change (Custom Field) on the Opportunities Object. Right now we have enabled field history on this custom field (picklist) of status, but this reports into the Opportunity Field History related box (which shows changes made to anything altered in the Opportunity)

We need to only track the Change of Status on this picklist and how much time it spent total in that particular status.

So, if the User A creates the Opp in status "New". User B changes it one day later to Status "In Progress" and then User C changes it one day later to Status "New" again. Finally User D sets the status to "Completed" one day later. What should show on the table. As I coded it, this is what should show: Status = Time in Status (days) --------------- New = 2, In Progress = 1, Completed = 1

In this ie: History should show: Last Modified By Column 'User A' Status Column 'New' Total Duration 2 days Last Modified by Column 'User B' Status Column 'In Progress' Duration 1 day etc

Currently using this code attached below but need to know How can we change 'Opportunity Status Change Name' from displaying those random sequence of numbers and letters to the actual name of the stage?

Answer 1

All objects in Salesforce have to have a Name value and if you do not provide one when creating the objects via code it defaults to the Id of the object, that is what you are seeing with the "random" string of characters. If you want the name to be your stage name then when you are creating the Opportunity Status Change Object just make Name = whatever value you want. In this case I think it is your status field.

Answer 2

Like @CyberJus suggested, setting Name in the code using the Status value is probably the way to go. You could also just drop the Status field from the related list and layout and copy the Status to Name using a Workflow rule.

The fields @James suggests can be a way to do this, but breaks down after more than a few Status values, and you need to think long and hard about all the rules. Can you skip statuses/is a linear workflow enforced/can you go backwards? If it's not clear which fields need to be used in the calculations consistently, you can have some problems. I also believe that the history tracking object gives more robust reporting capabilities if a little more complex for end users.

For Lead Status, I really like a free AppExchange product. It takes a similar approach that you are with the custom object. It may be worth looking at to compare your solution and give you some ideas on making it more bulletproof.

Answer 3

I would recommend moving away from your code-based solution.

I suggest creating several custom fields to support this, coupled with Workflow.

Custom_Stage_2_Begin_Date__c

Custom_Stage_3_Begin_Date__c

Custom_Stage_4_Begin_Date__c

From here, you can create workflow rules which will update the appropriate field with a date/time stamp (CreatedDate is implicitly Stage 1 Begin Date). Once you have the values, you can easily create custom formula fields to determine differences between the stage changes, which will yeild your sought-after "Stage Duration."

This, will make your date reportable and much easier to understand/maintain. Note, you'll have to go back and add the datestamps to historical data...