Capturing Case Time to Resolution Using Sugar Logic and Legacy Workflows

Overview

Support personnel may wish to monitor how quickly cases are being closed in Sugar. In this article, we will walk through creating a custom field called "Time to Resolution" that determines and stores the number of days it took to close a case. In order to properly calculate the time to resolution, the custom field will be calculated and we will also create a workflow that updates a second custom field called "Date Case Closed" which is used in the calculation.

Use Case

As an example, we will create a Time to Resolution field on the Cases module that, using a Sugar Logic formula, a workflow, and a second custom field, will automatically populate with the number of days from when a case was created to when it was closed.

Prerequisites

You must be an administrator or have developer-level access to make the necessary changes in Admin > Studio and create workflows.
You must first create two new fields for the Cases module and add them both to the Cases record view layout via Admin > Studio:
- An integer field called "Time to Resolution"
- A datetime field called "Date Case Closed" with the field name date_case_closed_c

For more information on creating new fields and modifying layouts, please refer to the Studio documentation. For more information on calculated fields, please refer to the Introduction to Calculated Fields article. For more information on workflows, please refer to the Workflow Management documentation.

Please be aware of the following compatibility notes regarding this article:

Each user's license type (e.g., Sugar Serve, Sugar Sell Premier) determines what functionality is available as described in the License Types matrix.
Users on Sugar 11.0.x or higher with a Sugar Serve license type have access to two calculated fields, Hours to Resolution and Business Hours to Resolution, in their Sugar instance and do not need to complete the steps in this article.

Steps to Complete

We will first make the Time to Resolution field a calculated field via Studio. Then we will create a workflow that captures the date the case was closed in the Date Case Closed field.

Adding the Time to Resolution Formula

Use the following steps to add a Sugar Logic formula to the Time to Resolution field:

Navigate to Admin > Studio > Cases > Fields and select "Time to Resolution".
Mark the Calculated Value checkbox then click the Edit Formula button.
Enter the below formula and click "Save" in the Formula Builder window.
- Note: This formula will calculate the difference between the Date Created and Date Case Closed fields for the case.
```
divide(abs(subtract(hoursUntil($date_entered),hoursUntil($date_case_closed_c))),24)
```
In order to hide the field before the case is closed, mark the Dependent checkbox then click "Edit Formula".
Enter the below formula and then click "Save" in the Formula Builder window.
- Note: This dependent formula will make the Time to Resolution field visible on the record view layout only if the case's status equals "Closed".
```
equal($status,"Closed")
```
Click "Save" to preserve all the changes made to the field.

Creating the Date Case Closed Workflow

Navigate to Admin > Workflow Management.
Click the triangle in the Workflow Definitions tab and select "Create Workflow Definition".
Complete the following fields and click "Save":
- Name : A descriptive name of your choosing (e.g. Update Date Case Closed)
- Execution Occurs : When record saved
- Target Module : Cases
- Processing Order : Actions then Alerts
- Status : Active
- Applies To : New and Updated Records
- Description : A brief description outlining the purpose of the workflow
Click "Create" on the Conditions subpanel to define your workflow condition.
- Choose "When a field in the target module changes to or from a specified value" and then click the hyperlinked text, "field".
  - In the popup window, select the field "Status" then click "Save".
  - When returned to the previous window, click "Next".
Click the hyperlinked text, "value".
- In the popup window, select the operator as "Is" and set the status value to "Closed". Click "Save".
- When returned to the previous window, click "Save".
Next, click "Create" in the Actions subpanel to updated the case's Date Case Closed field.
- Select the radio button labeled "Update fields in the target module" then click "Next".
- In the popup window, locate and select the Date Case Closed field then click the hyperlinked text, "Date Case Closed".
- In the resulting popup window, ensure that the values are set as "0 hours" and "Triggered Date". Click "Save".
- When returned to the previous window, click "Save".

The workflow definition is now complete as the appropriate condition and actions have been defined. The next time a case's status is set to "Closed", the Date Case Closed field will populate with the timestamp of the close date upon saving the record.

Formula Breakdown

In this section, we will walk through the formula entered for the Time to Resolution field:

divide(abs(subtract(hoursUntil($date_entered),hoursUntil($date_case_closed_c))),24)

The $date_entered and $date_case_closed_c fields will be used to determine how much time passed between the case being created and closed. The hoursUntil() function calculates how long from now it has been, in hours, since those two dates, and the subtract() function takes the difference. The abs() function returns the absolute value of the result, so if the difference between the two values is negative, it will return the absolute value which is a positive number. Finally, the divide() function will divide the number of hours a case was open by the number of hours in a day, 24, so that the final value is in days rather than hours.

Note: If you would prefer the field to calculate the number of full days the case was open without including fractions of days as decimal places, create the Time to Resolution field as an integer field and use this formula instead:

abs(subtract(daysUntil($date_entered),daysUntil($date_case_closed_c)))

Application

We can now test out the workflow and calculated field by editing an existing case record and setting the status to "Closed". Please note that due to the dependency formula, the Time to Resolution field will not be visible on the record view until the case's Status field is set to "Closed". As soon as the case is set to "Closed" and the record is saved, the workflow will trigger and update the Date Case Closed field as well as the Time to Resolution field, which will display the number of days between the Date Created and Date Case Closed fields.
12 - Results