[SalesForce] how can we check if all milestones are completed on that case

Assume I have 10 milestones running on the Case under a single Entitlement Process.

  • how can we check if all milestones are completed on that case ?
  • How can we calculate the Total ElapseTime and show it in the Custom Field on the case layout ? Do I need to used formula field or the trigger logic ?
  • I have End-to-end SLA and some SLA within the main SLA. In order to calculate the ElapseTime of the SLA, I only need to consider the main SLA (not other SLA/Sub SLA which are running with main SLA). How can we differentiate those SLA based on Category & SubCategory ? Do I need to write whole logic trigger ?

Any guidance around this ?

Best Answer

how can we check if all milestones are completed on that case ?

You could query and check isCompleted values

SELECT CaseId,
    CompletionDate,
    CreatedDate,
    ElapsedTimeInMins,Id,
    IsCompleted,
    IsDeleted,
    IsViolated,
    MilestoneTypeId,
    MilestoneType.Name, 
    StartDate 
FROM CaseMilestone  
WHERE CaseId='<case id>'

How can we calculate the Total ElapseTime and show it in the Custom Field on the case layout ? Do I need to used formula field or the trigger logic ?

You need to trigger an after update trigger, when the status is getting completed then fire this query and update that custom field.

SELECT SUM(ElapsedTimeInMins) 
From CaseMilestone 
WHERE CaseId = '<case id>' 
and IsCompleted = true

I have End-to-end SLA and some SLA within the main SLA. In order to calculate the ElapseTime of the SLA, I only need to consider the main SLA (not other SLA/Sub SLA which are running with main SLA). How can we differentiate those SLA based on Category & SubCategory ? Do I need to write whole logic trigger ?

Yes, you need to write logic in trigger. To retrieve milestones based on Category and SubCategory use this query:

SELECT CaseId, Case.Category__c,
    CompletionDate,
    CreatedDate,
    ElapsedTimeInMins,Id,
    IsCompleted,
    IsDeleted,
    IsViolated,
    MilestoneTypeId,
    MilestoneType.Name, 
    StartDate 
FROM CaseMilestone  
WHERE CaseId='<case id>' AND IsCompleted=true 
AND Case.Category__c = '<my category>'
Related Topic