Send a Case Update Reminder Email When the Update Threshold Has Been Exceeded

Customer Service Agents have to manage many cases and sometimes, even the best of them, may need a gentle reminder that a specific case may need a bit of love and attention. Enter automation…

Here are a few lessons learned from implementing this use case:

Create a formula to track case update threshold by business days
Use a scheduled flow to automate the email reminder to the customer service agent that they need to update the status of a case

Business Use Case: Addison Dogster is the system administrator at Universal Containers. Samantha Smith is the Operations manager. Generally, her customer service agents are on top of their cases, ensuring their customers get the attention they need. However, occasionally, a few cases may slip through the cracks. Samantha would like to send the case owner a gentle reminder that a case is in need of some TLC if the case has not been modified for 2 business days.

Solution: First, Addison needed a way to determine whether a case has not been updated for 2 business days using a formula. Once she had that figured, she can create automation that will send an email to the case owner if an open case has not been updated for 2 or more days.

The formula to calculate whether an open case has not been updated over 2 business days looks like this (Thank you, my Beantown brother, SteveMo!):

IsClosed = FALSE &&
( TODAY() – DATEVALUE( LastModifiedDate ) ) >=
CASE( WEEKDAY(DATEVALUE( LastModifiedDate )),
1, 2,
2, 2,
3, 2,
4, 4,
5, 4,
6, 4,
7, 3),
0 )

Let’s explain the formula. It checks whether the case is open (IsClosed = False). Then, it takes today’s date and subtracts the last modified date. If the resulting number of days value is greater than or equal to number of days based on the day of the week of the last modified date, then Salesforce will check the box, otherwise it is unchecked.

For example, if today’s date is 2/10/2021 and the last modified date was Friday, 2/5/2021, the difference between the two days is 5 days. Friday is the 5th day in the week, so the threshold is 4 calendar days, calculated with 2 weekend days and 2 business days.

If today’s date is 2/10/2021 and the last modified date was Monday, 2/8/2021, the difference between the two days is 2 days. Monday is the 1st day in the week, so the threshold is 2 calendar days or 2 business days.

Being the #AwesomeAdmin that she is, Addison wanted to give Samantha maximum flexibility in determining the business day threshold, so she made the threshold configurable in a numeric field in a custom metadata type record. If the threshold changes from 2 business days to 3, this can be easily modified in the custom metadata type record rather than modifying the calculation in the formula.

Here is the modified case formula that includes the custom metadata type reference:

The automation solution (scheduled flow) looks like this:

View image full screen

This daily scheduled flow will pull records when a record’s Case Update Threshold Exceeded? field is true and the record’s status is not closed. Then the it will (1) determine whether the case owner is a user or queue, (1a) if the case owner is a user, get the user’s email address and (2) send an email to the user, (1b) if the case owner is a queue, it will get queue information, (3) determine whether the queue has a queue email, and (4a) if there is a queue email, send the email to the queue or (4b) if the queue does not have a queue email, get the queue members and (5) go into a loop where (6) we will get the email for each queue member*, (7) add the email to a collection of emails and (8) send an email to the queue members.

*Note: Yes, having DML actions within a loop is frowned upon. However in this use case, we only expect a handful of members in the queue so hitting the DML limit is slim.

Highlighted Steps:

1.Create a custom metadata type that hold a numeric value via Setup | Custom Metadata Type | New Custom Metadata Type. In Addison’s org, she created a custom metadata type called Generic Configurable Reference which has several fields: checkbox, numeric data text data. She created CMDT records in this Generic Configurable Reference CMDT to cover many different scenarios of different field types.

Best practice tip: Provide a description so you and other/future admins know what this custom metadata type is used for.

1a. Create a new custom metadata type record called Business Days Update Threshold to hold the business days threshold value in the numeric data field. Select Manage Records | New to create a new CMDT record for the Generic Configurable Reference. Let’s set the numeric data value to 2.

2. Create a custom formula checkbox field on the case object called Case Update Threshold Exceeded? which determines whether the open case’s last modified date has met or exceeded the business days update threshold value defined in the CMDT. If it has been met or exceeded, the checkbox is checked. Note: This is a processing field, so consider limiting the field level security access to system administrator or any profiles that really need read access.

Best practice tip: Provide a description so you and other/future admins know what this custom field is used for.

View image full screen

Formula syntax:

IsClosed = FALSE &&
( TODAY() – DATEVALUE( LastModifiedDate ) ) >=
CASE( WEEKDAY(DATEVALUE( LastModifiedDate )),
1, $CustomMetadata.Generic_Configurable_Reference__mdt.Business_Days_Update_Threshold.Numeric_Data__c,
2, $CustomMetadata.Generic_Configurable_Reference__mdt.Business_Days_Update_Threshold.Numeric_Data__c,
3, $CustomMetadata.Generic_Configurable_Reference__mdt.Business_Days_Update_Threshold.Numeric_Data__c,
4, ($CustomMetadata.Generic_Configurable_Reference__mdt.Business_Days_Update_Threshold.Numeric_Data__c + 2),
5, ($CustomMetadata.Generic_Configurable_Reference__mdt.Business_Days_Update_Threshold.Numeric_Data__c + 2),
6, ($CustomMetadata.Generic_Configurable_Reference__mdt.Business_Days_Update_Threshold.Numeric_Data__c + 2),
7, ($CustomMetadata.Generic_Configurable_Reference__mdt.Business_Days_Update_Threshold.Numeric_Data__c + 1),
0 )

3. Create the scheduled flow shown above. In Lightning Experience, it is found under Process Automation | Flows. Click on “New Flow.” Select Scheduled Flow. For those using Salesforce Classic, flow can be found in Create | Workflows & Approvals | Flows. Click on the Create button.

In the flow, we would configure the following flow resources.

A. We need to create a variable resource called CollectionofEmailAddresses to store a collection of email addresses to send an email to queue members.

Best practice tip: Provide a description so you and other/future admins know what this flow resource is used for.

This is how that flow resource would be configured.

Resource Type: Variable
API Name: CollectionofEmailAddresses
Data Type: Text
Allow multiple values (collection): Checked

B. For our next flow resource, we will configure a formula called LinkFormula to determine the link to the case record. “LEFT({!$Api.Partner_Server_URL_340},FIND(“/services”, {!$Api.Partner_Server_URL_340}))” will dynamically construct the URL for the environment/sandbox you are in. “& {!$Record.Id}” references the case record detail page.

Best practice tip: Provide a description so you and other/future admins know what this flow resource is used for.

This is how that flow resource would be configured.

Resource Type: Formula
API Name: LinkFormula
Formula: LEFT({!$Api.Partner_Server_URL_340},FIND(“/services”, {!$Api.Partner_Server_URL_340})) & {!$Record.Id}

C. We need to create a text template to store the email body called EmailBodyTextTemplate that we will reference in the email sent to the case owner: user, queue email and individual queue members.

Best practice tip: Provide a description so you and other/future admins know what this flow resource is used for.

This is how that flow resource would be configured.

Resource Type: Text Template
API Name: EmailBodyTextTemplate
Body:

It’s been over X days since you’ve last updated case number# {!$Record.CaseNumber}.

Subject: {!$Record.Subject}

Description: {!$Record.Description}

Link: {!LinkFormula}

D. First, we set up the scheduled triggered flow’s start date/time and frequency. In this case, we set the start date and set it to run at 8:00am daily.

E. Next, we need to set the object and conditions. Here, we select the case object and that the Case Update Threshold Exceeded field is true and the status is not closed.

Object: Case
Condition Requirements: All Conditions Are Met (AND)
Case_Update_Threshold_Exceeded__c Equals True
AND Status Does Not Equal Closed

View image full screen

F. Now, we configure a Decision flow element called User or Queue. Here, we determine whether the case owner is a user or is assigned to a queue, since our cases can be assigned to either. If the case is owned by a user, the ownerId will start with a “005.” If a case is owned by a queue, the ownerId will start with a “00G.”