1

I have a requirement where my client needs the detailed audit on the "Status" field of "Case" object. They want the details of

'Status Start Time' , 'Status End Time' , 'Status Name' , 'Status Duration (Status End Time - Status Start Time) (Format for this field will be of something like 2 Days 10 Hrs 50 Mins)'

I am asked to create a new object with the above fields and create a new record with the above field details everytime a STATUS update happens.

For this I tried to write a trigger:

Trigger

Trigger CaseStatusAudit on Case (After Update) 
{
    If(Trigger.isAfter)
    {
        If(Trigger.isUpdate)
        {
            For(Case C : Trigger.New)
            {
                If(Trigger.oldMap.get(C.Id).Status != Trigger.newMap.get(C.Id).Status)
                {
                    CaseAuditTriggerHelper.CaseAuditMethod(Trigger.New);
                }
            }
        }
    }
}

Trigger Class

Public Class CaseAuditTriggerHelper
{
    Public Static Void CaseAuditMethod(List<Case> CaseIds)
    {
        Set<Id> CaseId = new Set<Id>();

        For(Case C : CaseIds)
        {            
            CaseId.add(C.Id);            
        }

        List<CaseHistory> CHListOld = [Select Id,CaseId,Field,OldValue,CreatedDate from CaseHistory where CaseId =: CaseId and Field = 'Status'];
        List<CaseHistory> CHListNew = [Select Id,CaseId,Field,NewValue,CreatedDate from CaseHistory where CaseId =: CaseId and Field = 'Status'];

        List<Case_Status_History__c> CSHList = New List<Case_Status_History__c>();
        For(Case C : CaseIds)
        {
            For(CaseHistory CHO : CHListOld)
            {
                For(CaseHistory CHN : CHListNew)
                {
                    If((C.Id == CHO.CaseId) && (C.Id == CHN.CaseId))
                    {    
                        If(CHO.OldValue == CHN.NewValue)
                        {
                            Case_Status_History__c CSHVal = New Case_Status_History__c();

                            CSHVal.Parent_Case__c = C.Id;
                            CSHVal.Status_Value__c = C.Status;
                            CSHVal.Status_Start_Time__c = CHO.CreatedDate;
                            CSHVal.Status_End_Time__c = CHN.CreatedDate;
                            //CSHVal.Status_Duration__c = parse(CHN.CreatedDate.getTime()-CHO.CreatedDate.getTime());
                            integer intDays =  CHN.CreatedDate.Date().daysBetween(CHO.CreatedDate.Date());
                            datetime sameDayEndDate = CHN.CreatedDate.addDays(intDays);                
                            decimal decHours = ((CHO.CreatedDate.getTime())/1000/60/60) - ((sameDayEndDate.getTime())/1000/60/60);
                            CSHVal.Status_Duration__c = decHours;
                            CSHList.add(CSHVal);
                        }
                    }
                }
            }
            Insert CSHList;
        }
    }
}

There are two issues which I am facing:

1) I am unable to calculate the "Status Duration" field in "Date/Time" format.

2) Multiple records are getting inserted for every status update instead of one.

Please help me either by correcting this trigger or by suggesting a more appropriate approach for the same. User needs to report on this audit log.

Thanks! Ruchi

0

Your CHListOld & CHListNew are containing exactly same data as you are firing same query twice to fill them up.

I think you should just query the CaseHistory where field is status order by createddate desc and calculate the different between createddate.

  • Hi Santanu...thank you for suggesting . But this approach did not give me the desired results. Later I had taken a totally different approach and now I am able to get the desired results. :) – Ruchi Dec 15 '15 at 7:27
0

Below are the steps I took to achieve the audit log on the status field.

1) Created new object "Case Status History" with the fields mentioned in my question.

2) Created new field "Last Status Change" on "Case" object of type "Date/Time".

3) Created a workflow rule on "Case" with the criteria so that "Last Status Change" field is updated whenever a record is created/updated :

OR((ISCHANGED( Status )), (CreatedDate == LastModifiedDate))

4) Created a "Workfloe Field Update" for the field "Last Status Change" with value as : Now()

5) Created a Apex Trigger and class to created new records in the "Case Status History" object whenever a status update happens for the Case record.

Below is the Trigger Code

Trigger:

Trigger CaseTrig on Case (After Update) 
{
    If(Trigger.isAfter)
    {
        If(Trigger.isUpdate)
        {
            For(Case C : Trigger.New)
            {
                If (Trigger.oldMap.get(C.Id).Status != Trigger.newMap.get(C.Id).Status)
                {
                    CaseAuditTriggerHelper.CaseAuditMethod(Trigger.Old,Trigger.New);
                }
            }
        }
    }
}

Trigger Class :

Public Class CaseAuditTriggerHelper
{
    Public Static Void CaseAuditMethod(List<Case> CaseOldIds , List<Case> CaseNewIds)
    {       
        List<Case_Status_History__c> CSHList = New List<Case_Status_History__c>();

        try
        {
            For(Case CO : CaseOldIds)
            {
                For(Case CN : CaseNewIds)
                {
                    System.debug('---CO.Last_Status_Change__c---'+CO.Last_Status_Change__c);
                    System.debug('---CN.Last_Status_Change__c---'+CN.Last_Status_Change__c);

                    IF((CO.Id == CN.Id) && (CO.Last_Status_Change__c != CN.Last_Status_Change__c))
                    {
                        Case_Status_History__c CSHVal = New Case_Status_History__c();

                        CSHVal.Parent_Case__c = CO.Id;
                        CSHVal.Status_Value__c = CO.Status;
                        CSHVal.Status_Start_Time__c = CO.Last_Status_Change__c;
                        CSHVal.Status_End_Time__c = CN.Last_Status_Change__c;
                        Long dt1Long = CO.Last_Status_Change__c.getTime();
                        Long dt2Long = CN.Last_Status_Change__c.getTime();
                        Long milliseconds = dt2Long - dt1Long;
                        Long seconds = milliseconds / 1000;
                        Long minutes = seconds / 60;
                        Long hours = minutes / 60;
                        Long days = hours / 24;
                        Long Subhours = (minutes - (days * 24 * 60)) / 60 ;
                        Long Submin = ((minutes - (days * 24 * 60)) - (Subhours * 60)) ;
                        CSHVal.Status_Duration__c = days + ' Days /  ' + Subhours + ' Hrs /  ' + Submin + ' Mins ';
                        CSHList.add(CSHVal);
                    }
                }
                Insert CSHList;
            }
        }
        Catch(Exception e){
            }       
    }
}

This will give you an audit log of something like below

enter image description here

Hope this will help some of you looking for the similar functionality.

Regards! Ruchi

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.