function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Athira VenugopalAthira Venugopal 

How to find out the time spent by a specific user on a case record from its 'New' to 'Closed' status?

Anyone please help me!!

I just created a formula field to calculate  the time spent between status changes 'New' and 'Closed'. But it's not user specific. ie, if im the one who logged in i want to see the time i had spend on that.
Abdul KhatriAbdul Khatri
Hi Athira,

I guess they way you can achieve that is by Set History Tracking on status, in this way you get the audit of the field change by user. You can than create a Lightning Report (as you can't do report on History SObject in classi) to show the info as needed.

The other way you can create an SObject something Case Status Workflow and insert record as status change. You just need to add the following fields:
  • Status
You can build Report on SObject both in classic and lightning.

Let me know if this helps
Athira VenugopalAthira Venugopal
Hi abdul,
Thanks for your  reply. Is there any way to do this using Process builder or trigger?
 
Abdul KhatriAbdul Khatri
Well since spring 21 still isn't fully out otherwise could have achieve the below with that but with trigger here it is
  • First you can create an custom SObject Case Workflow with the following fields
User-added image
You can use the following trigger, please note this is just to give you an idea. You can change the trigger as per your need 
trigger CaseStatusWorkflowTrigger on Case (after insert, after update) {

	List<Case_Workflow__c> cwToInsertList = new List<Case_Workflow__c>();    
    for(Case caseRec : trigger.new){
        
        if(trigger.oldMap == null || (caseRec.Status != trigger.oldMap.get(caseRec.Id).Status))
        {
            Case_Workflow__c cwToInsert = new Case_Workflow__c();
            cwToInsert.OldValue__c = trigger.oldMap?.get(caseRec.Id).Status;
            cwToInsert.NewValue__c = caseRec.Status;
            cwToInsert.FieldName__c = 'Status';
            cwToInsert.Case__c = caseRec.Id;
            
            cwToInsertList.add(cwToInsert);
        }
    }
    
    if(!cwToInsertList.isEmpty())
        Database.insert(cwToInsertList);

}

Test class 
@isTest
public class CaseStatusWorkflowTest {
    
    private static testMethod void testCaseStatusWorkflowOnInsert() {
        
        Test.startTest();
        Case caseRec = new Case(Status = 'Open', Origin = 'Phone');
        insert caseRec;
        Test.stopTest();
        
        Case_Workflow__c cw = [SELECT Id, OldValue__c, NewValue__c FROM Case_Workflow__c WHERE Case__c = :caseRec.Id];
        system.assert(cw.OldValue__c == null);
        system.assert(cw.NewValue__c == 'Open');
        
    }
}