Jennifer Lee and Dee Ervin in How I Solved It

How I Solved It: Close the Gaps in Activity Tracking

By

Welcome to another post in the “How I Solved This” series. In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Dee Ervin closed the gaps in Activity tracking.


Key business problem

Case Management in Salesforce is facilitated through support processes that advise case statuses within the support lifecycle. When moving from one case status to the next, there are usually a series of Activity records created for emails, calls, meetings, etc., with customers/clients. However, these activities usually don’t include research, internal communications, or any activities that support the client interactions. Managers need a comprehensive report to reflect the time spent across all related case activities, as well as an understanding of activities when a case is reopened. This comprehensive view can be achieved with Flow, a standard object, and minimal user adoption effort!

How I solved it

Create three new Activity fields

To memorialize the status and type (that is, new or reopened) of the case, I created two picklist fields on Task layout. The last field I created is a formula field to calculate Completed Date/Time - Created Date.

1. Field: Case Status*

a. Data Type: Picklist

b. Description: Status of related case at the Activity record was created.

c. Values: {Values in standard Status field on the Case object}

d. Additional Note: Make “Read Only” on page layout

*Tip: To ensure the Status field on the Activities object aligned with any case status values now and in the future, I unchecked the “Restrict picklist to the values defined in the value set” field. This will seamlessly add any new values to the list as inactive values.

Case Status picklist field configuration on Activity object

2. Field: Case Type

a. Data Type: Picklist

b. Description: Advises if related case is “New” or “Reopened”

c. Values: New, Reopened

d. Additional Note: Make “Read Only” on page layout

Case Type picklist field configuration on Activity object

3. Field: Status Duration ({time measurement})

a. Data Type: Formula [Number, (16, 2)]

b. Description: Time within the status of the related case

c. Value**: ((Completed Date/Time - Created Date)*1440)/60

i. Multiply by 1440 to calculate minutes.

ii. Divide minutes by 60 to calculate hours.

**This does not consider business hours or weekends, but this formula can be updated to take these two items into account.

Status Duration formula field configuration on Activity object

Create a record-triggered flow

The completed Case Status Activities record-triggered flow

1. Configure Start element to trigger when a case is created or updated with the status equal to {default status when a new case is created} OR if the status is changed. I optimized the flow for actions and related records. There are two reasons for this: 1) Fast Field Updates is best used when there is only one object involved in the flow, and 2) The actions for this flow require the Case record to be saved to the database before creating and updating related Tasks.

Flow trigger from a Case being created or updated with entry conditions based on Case Status

2a. Add a Get Records element to find Task records related to the case; specifically, to find the most recent case status Activities Task record.

Get Records element to find the most recent, related Case Status Activities Task record

2b. To filter the Tasks to find the Case Status Activities Task, I created Text Variable based on the naming conventions I have set for these Task records, so this Text Variable has a default value of my unique subject line identifier.

Text variable created with a default value of Status Activities

3a. Once the Task records have been queried in the Get Records element, I set up a Decision element to create logical action paths. This first path covers the outcome of an open task found on an open case.

Decision element showing the conditions for the Open Case and Open Task path

3b. This second path covers the outcome of no task found on a new case.

Decision element showing the conditions for the New Case path

3c. This third path covers the outcome of no open task found on a reopened case.

Decision element showing the conditions for the Reopened Case path

3d. This fourth path covers the outcome of an open task found on a closed case. The importance of this path is to ensure when a case is closed, a new Task record is not created and any open Case Status Activities Task record is closed.

Decision element showing the conditions for the Closed Case and Open Task path

3e. This fifth path covers the outcome of a closed task found on an open case. The importance of this path is to ensure a new Case Status Activities Task is created when the previous Case Status Activities Task is closed.

Decision element showing the conditions for the Open Case and Closed Task path

4. Although the flow has five custom outcome paths, there are only three resulting actions. To keep each scenario represented, I used connectors to apply one action to multiple paths. The first action is an Update Records element to update Task record to closed. This action applies to both outcome 1 and outcome 4.

Update element to close the open Task record

5a. The second action is a Create Records element to create a new Task record. This action applies to both outcome 2 and outcome 5.

Create element to create a new Task related to the Case record

5b. To apply the correct Case Type to the new Case Status Activities Task, I created Formula Variable [IF(TEXT({!Get_Status_Activity_Task.CaseType__c})=“Reopened”,“Reopened”,“New”)] which references the Task output of the Get Records element to check the Case Type of the most recent Case element and apply it to the new Task record. This formula was required because I reused this Create Records element for both Case Status Activities related to new and reopened Case records. Formula:

Formula Variable to determine Case Type on Task record

5c. To apply a consistent naming convention to each Case Status Activities Task record created, I created another Formula Variable to concatenate the Status of the related Case record, and a suffix as a unique identifier, “_Status Activities”.

Formula Variable to set the naming convention for the Task subject line

6. The third action is a Create Records element to create a new Task record when a case is reopened. This action applies to outcome 3 only. The reason why this isn’t combined with the second action is because it particularly handles the initial reopening of a case.

Creating a new Task record

Business results

With this process in place, it will be easy to create time-tracking reports and differentiate Activities between statuses on new and reopened cases. With the new fields on Task records, other automation could be created to update all Activities records with Case Status and Case Type values. This will further support comprehensive reporting on standard Activities, and Case Status Activities tracking.

Try this at home

Although this solution was focused on cases, this concept could be applied to any object using stages or statuses to manage the process. Examples include, but are not limited to, Opportunities, Leads, etc.

Case Status Activities report with Status Duration summarized by Owner and Case, then further summarized by Case Type and Case Status

Resources

Want to see more good stuff? Subscribe to our channel!

SUBSCRIBE TODAY
Jennifer Lee and Stacy O'Leary in a new episode of "How I Solved It"

How I Solved It: Manage Contacts Who Have Changed Jobs

Welcome to another post in the “How I Solved It” series. In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Stacy O’Leary gives […]

READ MORE
Automate This! with Jennifer Lee, Rebecca Glasser and Diana Jaffe

Automate This! — A Trailblazer’s Insights from Migrating Workflow Rules to Flow

Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by #AwesomeAdmin Trailblazers like you. Automation allows you to remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, let’s see how Rebecca Glasser migrates and consolidates […]

READ MORE

Have an Idea for a Story?

We are all about the community and sharing ideas.
Do you have an interesting idea or useful tip that you want to share?

SHARE YOUR IDEA