2

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, even if ie:status updated from prospecting to contract signed and back to prospecting---(similar to the history shown on 'Stage History'--

we need to calculate the overall time it spent in prospecting stage not a reset counter when it moved back. We do not have a developer to help us with some coding I imagine this would take, so I believe utilizing the Process Builder would help us accomplish this and if this is the way to go- confused on the steps to take.

enter image description here

3

The functionality that you are looking is not possible using Process Builder at this time. I believe it might be possible in the near future with enhancements that are coming in the next releases, but I cannot assure you of that.

You will have to rely on code in order to achieve it. I have some code that might help you.

Here is what you will need for the code:

  1. Add field to Opportunity Object called "Last Status Change" (Date Time)
  2. Add formula field (name it "Time in current Status") to Opportunity object with the following logic: IF(ISBLANK(Last_Status_Change__c), NOW() - CreatedDate, NOW()- Last_Status_Change__c)
  3. Create a new Custom Object called "Opportunity Status Change"
  4. Add the following fields to this new object: Opportunity (Lookup to Opportunity) Status (Text) Time in Status (Number)

Create the following trigger on the Opportunity Object:

 trigger trgOppTrackStatusTime on Opportunity (after update)  {
        Map<string,Opportunity_Status_Change__c> metricByKey = new Map<string,Opportunity_Status_Change__c>();

        for(Opportunity_Status_Change__c m : [SELECT id, Status__c, Opportunity__c, Time_In_Status__c FROM Opportunity_Status_Change__c WHERE Opportunity__c IN :trigger.newMap.keyset()])
        {
            metricByKey.put(string.valueOf(m.Opportunity__c)+string.valueOf(m.Status__c),m);
        }

        List<Opportunity_Status_Change__c> metrics = new List<Opportunity_Status_Change__c>();
        string key = '';
        Decimal hours;
        for(Opportunity o : trigger.new)
        {
            String oldStage = trigger.oldMap.get(o.Id).Operations_Status__c;
            String newStage = o.Operations_Status__c;
            Boolean stageChanged = oldStage != newStage;

            if(stageChanged)
            {
                hours = o.Time_in_current_Status__c;

                Opportunity_Status_Change__c temp;
                key = string.valueOf(o.Id)+string.valueOf(trigger.oldMap.get(o.Id).Operations_Status__c);
                if(metricByKey.containsKey(key))
                {
                    temp = metricByKey.get(key);
                    temp.Time_In_Status__c += hours;
                }
                else
                {
                    temp = new Opportunity_Status_Change__c(
Name=trigger.oldMap.get(o.Id).Operations_Status__c,
                                                Status__c = trigger.oldMap.get(o.Id).Operations_Status__c,
                                                Opportunity__c = o.Id,
                                                Time_In_Status__c = hours
                                             );
                }

                metrics.add(temp);
            }
        }

        if(metrics.size() > 0)
            upsert metrics; }

If the code fails to save, provide me the error and I will provide an update.

Good Luck!!

  • First off thank you for getting me started here--appreciate it! I have created everything you mentioned and am receiving an error on the Apex Trigger Error: Compile Error: unexpected token: '<' at line 1 column 8 trigger <name> on Opportunity_Status_Change__c (<events>) { }trigger trgOppTrackStatusTime on Opportunity (after update) { Map<string,Opportunity_Status_Change__c> metricByKey = new Map<string,Opportunity_Status_Change__c>(); – JazzyJ May 19 '15 at 14:06
  • Hi JazzyJ, I think that the auto-generated code when you click the New Trigger button is the one thats erring out. Please make sure that the only code you put on the edit box is what I provided. If thats not the case, let me know and we can look into it further. – slashApex May 19 '15 at 20:14
  • Thanks SlashApex you were correct on that first error!! But im getting another one now Error: Compile Error: Invalid field Status__c for SObject Opportunity at line 14 column 52 Also will this coding show the overall duration spent in each status in days or hours (we need days) – JazzyJ May 19 '15 at 20:17
  • Hi JazzyJ, it will show it in days. What is the APi name of the field that you want to track time for? I assumed it was called Status__c, but if that is not the case, then just replace Status__c with the correct name. – slashApex May 19 '15 at 21:56
  • Sorry mate its not working for me. I tried replacing the Status_c with Operations_Status__c on all lines I could find. I am getting errd Compile Error: Incorrect SObject type: Opportunity should be Opportunity_Status_Change__c at line 1 column 1 trigger trgOppTrackStatusTime on Opportunity (after update) { Map<string,Opportunity_Status_Change__c> metricByKey = new Map<string,Opportunity_Status_Change__c>(); for(Opportunity_Status_Change__c m : [SELECT id, Status__c, Opportunity__c, Time_In_Status__c FROM Opportunity_Status_Change__c WHERE Opportunity__c IN :trigger.newMap.keyset()] – JazzyJ May 20 '15 at 13:59

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.