[SalesForce] Duplicating Opportunity Stage Duration for Custom Field

It's probably been asked before, but how do you duplicate the Stage Duration functionality in Opportunities on a custom field? I have a custom picklist field named "Operations Status" that serves the same purpose as the Opportunity Stage field. Users select a value that changes based on events driving our operations process. The statuses do not move in a linear fashion and can go back-and-forth based on certain events so a record can go from Value1 to Value2 to Value3 and back to Value2.

The idea is to track the duration on each status value and account for the back-and-forth situations (probably incorporating some sort of counter?) as well as the dates they occurred.

Does this involve the creation of n custom fields where n = the number of Status values or is this something that warrants it's own Custom object related to the parent object which is Opportunity in this case? Additionally, does this require the use of Apex Triggers or can this be done using declarative configuration (Workflows, Formulas, and such)?

Thank you in advance for the feedback.

Sean

Best Answer

Depending on your requirements, I can see two solutions here:

Solution 1

You can turn on history tracking for the Stage on the opportunity. This will track every time it has change, by who, and when. You can an opportunity history report (this is out of the box) which will give you the individual entries. Look at this help article: http://help.salesforce.com/HTViewHelpDoc?id=reports_opp_history.htm&language=en_US

Solution 2

As some of the previous comments imply - create a custom object that is a child of the Opportunity (Call it OpportunityStageEvents for arguments sake). Here is how it would work:

Create the new object and the fields you want to retain (like from and to stage names, time spent in this duration, who made the change, etc.)
Make the new custom object as a child of opportunity (by using master-detail relationship on the custom object to the opportunity)
Next you need to trigger every time you change the stage to create a record and capture the information. There are two ways to do this: a) create an Apex trigger to get the values, create the new child object record and insert it b) use the new process builder to essentially do the same thing (my preference - and a very powerful tool - but still in beta)

Happy to provide more details if required.

Related Solutions

[SalesForce] Trigger to update Opportunity stage from Task subject

Hope the below code helps!

trigger TaskSubject on Task (after update, after insert) {

set<id> accountIdSet = new set<id>();
//capture all the subject names in a set
//set<string> subjectName = new set<string>{'Call EJF','Email Centurion','Initiate Merion'};
map<id, list<opportunity>> opptyMap = new map<id, list<opportunity>>();
list<opportunity> updateOppList = new list<opportunity>();

//get those tasks where the tasks are related to account. Capture the account IDs in a set.
for(task t: trigger.new){
            string tempId = string.valueOf(t.WhatId);
    if(tempId.startsWith('001')){
        accountIdSet.add(t.whatId);
    }
}

//If we have any account IDs then get the associated opportuity and store the one account to many opportunity in a map<id, list<opportunity>>
if(accountIdSet.size()>0){
    for(opportunity opp:[select Name, stageName, accountid from opportunity where accountid IN :accountIdSet]){
        if(opptyMap.containsKey(opp.accountId)){
            list<opportunity> oppList = opptyMap.get(opp.accountId);
            oppList.add(opp);
            opptyMap.put(opp.accountId, oppList);
        }else{
            list<opportunity> newOppList = new list<opportunity>();
            newOppList.add(opp);
            opptyMap.put(opp.accountId, newOppList);
        }
    }
}

//If we get the opportunities then change the stage.
if(Trigger.isUpdate && opptyMap.size()>0){
    for(task t: trigger.new){

 //Check if the subject of the task is one among the set 
 //and also confirm that the subject has been changed.

        if(t.subject != trigger.oldMap.get(t.id).subject && opptyMap.containsKey(t.whatId)){

  //iterate thru the list of the opportunity associated with that account 
  //and check which of the opportunity contains the task subject in the 
   //opportunity name and update the stage to qualification

            for(opportunity oppty: opptyMap.get(t.whatId)){
                if(oppty.name.contains(t.subject)){
                    oppty.stageName='Qualification';
                    updateOppList.add(oppty);
                }
            }
        }
    }
}
if(Trigger.isInsert && opptyMap.size()>0){
     for(task t: trigger.new){
            if(opptyMap.containsKey(t.whatId)){
                for(opportunity oppty: opptyMap.get(t.whatId)){
                   if(oppty.name.contains(t.subject)){
                      oppty.stageName='Qualification';
                      updateOppList.add(oppty);
                   }
               }
            }
        }
    }
//update the oppties
if(updateOppList.size()>0){
    update updateOppList;
}
}

EDIT:
This code is working. I followed the below flow: Create an account(test account) --> Create a task for this account (test account) and make sure the subject is not 'Initiate Merion'.
create an opportunity (make sure the stage is not qualification) for the same account, name it 'test oppty Initiate Merion'.
Now edit the task created, change the subject to 'Initiate Merion'. You can now see that the Opportunity stage has been changed to 'Qualification'.

EDIT 2
Added an after insert event. Also the one more code block to handle the insert and update events seperately

EDIT 3 Task subject need not necessarily be from a set of predefined strings. As long as task subject is part of Opp name, stage must be updated

[SalesForce] calculate duration spent in custom field status

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:

Add field to Opportunity Object called "Last Status Change" (Date Time)
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)
Create a new Custom Object called "Opportunity Status Change"
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!!