function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Michael Lindsay 4Michael Lindsay 4 

Opportunity Status Tracking with cumulative totals

All,

We are trying to track the cumulative time an opportunity spends in each stage including the current stage.  I understand that you can get to most of this data using the opportunity stage history report but it is lacking in the current stage data and the math is not always correct!  I found a posting at the following URL that looks close to what I what but even after creating it just as listed I am getting compile errors.

Here is the URL:  http://salesforce.stackexchange.com/questions/76619/calculate-duration-spent-in-custom-field-status

Here are the things I did.
  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)

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

        for(OpportunityStatusChange__c m : [SELECT id, Status__c, Opportunity__c, Time_In_Status__c FROM OpportunityStatusChange__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).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).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).Status__c,
                                                Status__c = trigger.oldMap.get(o.Id).Status__c,
                                                Opportunity__c = o.Id,
                                                Time_In_Status__c = hours
                                             );
                }

                metrics.add(temp);
            }
        }

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


Any help would be great!

Thanks,

Michael
Michael Lindsay 4Michael Lindsay 4
Sorry forgot to include the error.

Error: Compile Error: sObject type 'OpportunityStatusChange__c' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. Please reference your WSDL or the describe call for the appropriate names. at line 4 column 44