Capturing Records' Activity Counts Using Calculated Fields

Overview

Sugar® allows you to create calculated fields that count a record's number of related records. While this type of calculated field can be useful in counting any type of related record, this article will focus on counting related activity records (e.g. calls, meetings, tasks, emails, and notes) as an example. The activity record count may then be used to gain insight into how your organization is communicating with contacts, leads, accounts, etc. or to identify individual relationships that would benefit from additional communication.

There are several methods for capturing and reporting on activities or other related records. Please also refer to the Reporting on Records With No Activities and Capturing a Record's Last Activity Date Using Workflows articles for additional approaches available in Sugar.

Beginning with version 9.1.0, each user's license type (e.g. Sugar Serve) determines what functionality is available as described in the License Types Matrix documentation. The Leads module used as an example in this article is not available for Sugar Serve users, but creating a calculated field to capture a records' related activity count works similarly for many other modules.

Prerequisites

You must be an administrator or have developer-level role access to create a custom calculated field, which is required for this procedure. For more information on calculated fields, see the Introduction to Calculated Fields article.

Steps to Complete

The following steps will cover creating a calculated field that counts a lead's related activity (calls, meetings, etc.) records as an example:

Navigate to Admin > Studio > Leads > Fields.
Click "Add Field" to create a custom field.
Configure the new field's settings as follows:
- Data Type: Integer
- Field Name: activity_count
- Display Label: Activity Count (or another name you wish to be visible in layouts)
- Enable Range Search: Checked
- Reportable: Checked
- Calculated Value: Checked
- Formula: Click on "Edit Formula". In the Formula Builder window, paste the following formula and click "Save":
```
ifElse(equal(add(
   count($calls),
   count($emails),
   count($meetings),
   count($notes),
   count($tasks)),0),0,
  add(
   count($calls),
   count($emails),
   count($meetings),
   count($notes),
   count($tasks)
))
```
  Note: The formula can be modified to disregard any unwanted activity types by removing the appropriate line.
After entering the formula, click "Save" again to create the calculated field.

Restricting the Calculated Field to Only Count "Held" Activities

If your organization only wants to count activities that have actually occurred, it is possible to conditionally count activity records whose status is "Held" (or its equivalent). For this process, you will need to create several calculated fields: one new field in each activity module plus the sum Activity Count field in the main (Leads, Accounts, etc.) module. Please note that the main module's Activity Count field will have a different formula than the one used in the previous example.

The following steps will cover counting activities with a status of "Held" for the Leads module as an example:

Create a calculated integer field named "Activity Count" (activity_count_c) in the Calls module using the formula: ifElse(equal($status,"Held"),1,0).
Create a calculated integer field named "Activity Count" (activity_count_c) in the Meetings module using the formula: ifElse(equal($status,"Held"),1,0).
Create a calculated integer field named "Activity Count" (activity_count_c) in the Tasks module using the formula: ifElse(equal($status,"Completed"),1,0).

Create a fourth calculated integer field named "Activity Count" (activity_count_c) in the Leads module using the formula:

ifElse(equal(add(
rollupSum($calls,"activity_count_c"),
rollupSum($meetings,"activity_count_c"),
rollupSum($tasks,"activity_count_c"),
count($notes),
count($emails)),"NaN"),0,add(
rollupSum($calls,"activity_count_c"),
rollupSum($meetings,"activity_count_c"),
rollupSum($tasks,"activity_count_c"),
count($notes),
count($emails)))

This will count all related notes and emails but exclude any calls, meetings, or tasks that were not held or have not yet occurred.

Application

Once the activity count field has been successfully created, it can be added to any layouts where you wish to see a record's current activity count. You will be able to filter in list view according to this value as well.

For additional insight, you can create a report based on the activity count field in order to identify contacts, leads, accounts, etc. who need additional touches or for other managerial purposes. The report filters can be configured in various ways depending on what criteria are relevant to your business process.

The following steps will cover creating a report to display a list of leads and the number of related activities (e.g. calls, meetings, tasks, etc.) as an example:

Navigate to the reports module and create a Rows and Columns report based on the Leads module.
Next, define the report's filters. Limit results for this report to only leads that have at least one activity by clicking on Activity Count in the Available Fields pane and then set that field to "Greater Than" 0 (zero).
If desired, add additional filters. For example, to limit results to only leads that have been converted or leads that have been created after a specified date, include "Leads > Status" or "Leads > Date Created" as filters. For an in-depth look at report filters, see the knowledge base article, Understanding Report Filters.
Click "Next" to choose the report's display columns. At a minimum, the columns should include the lead's Full Name field and the Activity Count field. For more information about configuring display columns, please refer to the Reports documentation.
Click "Next" again to proceed to the Report Details section. After you have named the report, click "Save and Run" to see a list of leads and their corresponding activities counts.