SugarCRM SupportKnowledge BaseWorkflowsCapturing a Record's Last Activity Date Using Workflows

Capturing a Record's Last Activity Date Using Workflows

Overview

In this article, we will walk through creating a field that represents the date an activity record (i.e. call, meeting, email, task, or note) was last created for a particular record. This process requires the creation of a custom date field as well as several workflows. The workflows will capture the creation date of the last activity in an account. The Last Activity Date field 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 Records' Activity Counts Using Calculated Fields articles for additional approaches available in Sugar®.

Note: The Last Activity Date created in this article will only apply to activity records created after the steps have been completed.

Prerequisites

You must be an administrator or have developer-level role access to create a custom field in Studio and utilize the Workflows module as required by this article. For more information, please refer to the Studio and Workflow Management documentation. 

Use Case

We will create a Last Activity Date field for the Accounts module that reflects the last time a user held a call or meeting, created a note or task, or sent an email to the account's email address.

Steps to Complete

For our example, we must create the Last Activity Date field for the Accounts module plus five separate workflows that will control the value in the Last Activity Date field. Because there are five activities modules (i.e. Calls, Meetings, Emails, Tasks, and Notes), the five workflows will detect records that may be created in an activities module against the account. The workflows will work independently to detect a certain change (e.g. a new note has been created and related to the account) and will update the value of the Last Activity Date to the moment that the change occurs.

The following sections explain how to create the Last Activity Date field and set up the necessary workflows. These steps assume some familiarity with using the Workflows module in Sugar. For more information on the basics of setting up workflows, please refer to the article Understanding and Setting Up Workflows.

  1. First, create the Last Activity Date field for the target module where you would like to view the information. We will create the Last Activity Date field for the Accounts module. Navigate to Admin > Studio > Accounts > Fields and click "Add Field". 
    studio-addfield
  2. Create a new field with the following values and then click "Save" to create the Last Activity Date field.
    • Data Type : Datetime
    • Field Name : last_activity
    • Display Label : Last Activity Date
  3. Optionally, add the Last Activity Date field to the record view layout if you wish to view the field outside of reports. Please refer to the Application section for further details.
  4. Now, create the eleven workflows. Navigate to Admin > Workflow Management. Expand the menu from the Workflow Definitions tab and select "Create Workflow Definition". 
  5. One at a time, create and save the following workflow definitions. We will configure the conditions and actions for each in subsequent steps, but you may want to move ahead at your own pace. 
    Name Target Module Applies To
    Last Activity - Calls Calls New and Updated Records 
    Last Activity - Meetings  Meetings  New and Updated Records
    Last Activity - Tasks Tasks  New Records Only 
    Last Activity - Notes  Notes  New Records Only 
    Last Activity - Emails Emails  New Records Only 
    Note: Execution occurs "When Records are Saved" for all of the workflow definitions.
  6. For the workflow actions, each workflow will result in the same action: updating the Last Activity Date field on the account. After saving each workflow, scroll down to the workflow's Actions subpanel and click "Create".
  7. Select "Update fields in a related module" in the popup window and then click the hyperlinked word "Record" in "Update field(s) in a related record". 
    Image11-New
  8. Select the related module where the field will be updated. We will select the Accounts module for our example. Click "Save" and then click "Next" in the following window.
  9. Select the field that you would like to modify when the workflow triggers. We will select the Last Activity Date field for our example.
  10. Click the hyperlinked text "Last Activity Date" in the lower part of the window. 
    Image14-New
  11. Set the last activity date as 0 hours from Triggered Date then click "Save". 
    Image15-New
    The configured action will now be displayed in the Actions subpanel:
    Image23-New
  12. After configuring the Action for each workflow, please refer to the appropriate section of this page to create the conditions for each workflow:

Creating Conditions for Calls and Meetings

Because there is little value in a call or meeting until it has been held, we do not want Sugar to update the Last Activity Date until the user has indicated that the call or meeting has taken place. Therefore, this section explains how to set a condition that is met when the status of a related call or meeting record changes to "Held".

Note: These steps are identical for the Calls and Meetings modules but must be repeated separately for each.

  1. Navigate to the detail view for the "Last Activity - Calls" workflow.
  2. Scroll down to the Conditions subpanel and click "Create".
  3. Select "When a field in the target module changes to or from a specified value" in the Define a Workflow Condition window.
  4. Click on the hyperlinked word "field" in "When field changes to or from specified value". 
    Image6-New
  5. Select "Status" from the dropdown list then click "Save". 
    Image7-New
  6. Click "Next" in the Define Workflow Condition window.
  7. Click "Value" in "Specify new Status". 
    Image8-New
  8. Set status to "Is" and "Held" then click "Save". 
    Image9-New
  9. Click "Save" in the following window to finalize the condition. The condition will now be displayed in the Conditions subpanel:
    Image16-New1
  10. Repeat with the "Last Activity - Meetings" workflow.

Creating Conditions for Notes and Tasks

Notes and Tasks are generally created in real time and do not require a certain status to be considered a genuine activity. This section explains how to set a condition that is met when a related note or task's Date Created field changes, indicating that a note or task has indeed been created.

Note: These steps are identical for the Notes and Tasks modules but must be repeated separately for each.

  1. Navigate to the detail view for the "Last Activity - Notes" workflow.
  2. Scroll down to the Conditions subpanel and click "Create".
  3. Select "When a field on the target module changes" in the Define Condition for Workflow Execution window.
  4. Click "field" in "When field changes". 
    Image18-New
  5. Select "Date Created" from the dropdown list then click "Save". 
    Image19-New
  6. Click "Save" once more to finalize the condition. The condition will now be displayed in the Conditions subpanel:
    Image20-New
  7. Repeat with the "Last Activity - Tasks" workflow.

Creating Conditions for Emails

Emails may be incoming, outgoing, or archived from an external system. This section explains how to set a condition that is met when a related email type is archived, inbound, or sent, which should cover all scenarios for a stock Sugar instance.

  1. Navigate to the detail view for the "Last Activity - Emails" workflow.
  2. Scroll down to the Conditions subpanel and click "Create".
  3. Select "When a field in the target module changes to or from a specified value" in the Define a Workflow Condition window.
  4. Click the hyperlinked word "field" in "When field changes to or from specified value".
  5. Select "Type" from the dropdown list and click Save.
  6. Click "Next" in the Define a Workflow Condition window.
  7. Click the hyperlinked word "value" in "Specify new Type value". 
    Image21-New
  8. Set type to "Is One of" then hold the control or command key while selecting "Archived", "Inbound", and "Sent". 
    Image17-New
  9. Click "Save" and then click "Save" again in the following window to finalize the condition. The condition will now be displayed in the Conditions subpanel:
    Image22-New

Application

Once the field and workflows for each activity have been successfully created, you may choose to add the field to any layouts where you wish to see a record's last activity date. You will be able to filter in list view according to this value as well. If you choose to do so, we recommend restricting users' ability to edit the field. For more information on restricting access to a field, please refer to the Making a Field "Read-Only" for Certain Users article and the Role Management documentation.

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

The following steps will cover creating a report to display all accounts with no activity records (calls, meetings, tasks) created within the last seven days as an example:

  1. Navigate to the reports module and create a Rows and Columns report based on the Accounts module.
  2. In "Available Fields : Accounts", select the "Last added activity" field created above. 
  3. Set the filter to "Before" and select the date 7 days prior to today. Select "Run-time" to allow users to quickly adjust this date in the future.
    revised_report_filters
  4. Select "Next" to move to choosing display columns.
  5. Add any fields you would like under the "Choose Display Columns" section of the Report. Select "Next".
    add_display_columns
  6. Fill in the report details. Once completed, save and run the report. 
    fill_out_report_details

Last modified: 08/10/2017 06:04pm