Troubleshooting Time-Elapsed Legacy Workflows

Overview

Sugar's Workflow functionality allows administrators to configure cause-and-effect actions across multiple modules. Time-elapsed workflows produce alerts and/or actions when specific conditions are met upon save and the specified amount of time has passed. However, there are times when a workflow does not produce the expected result. This article is not an exhaustive list of possibilities as to why the workflow did not function as expected. Rather, it seeks to help you troubleshoot and further understand the issue at hand.

For more information about workflows, please refer to the Workflow Management documentation.

Each user's license type (e.g., Sugar Serve, Sugar Sell Premier) determines what functionality is available as described in the License Types matrix. 

Symptoms

When an active time-elapsed workflow is not working as intended, the following behaviors may be experienced:

• Specified actions do not execute or execute at the wrong time.
• Specified alerts are not sent or are sent at the wrong time.

Understanding Time-Elapsed Workflows

Understanding how time-elapsed workflows function and how they differ from on-save workflows is important when creating or troubleshooting these workflows. For both types of workflows, each of the workflow conditions is assessed only at the time the record is saved. The fundamental difference between these two workflow types is what happens at the time of the save. When an on-save workflow's conditions are met, all alerts and actions for that workflow are immediately triggered and performed. However, when a time-elapsed workflow's conditions are met, a series of events are immediately initiated.

When the conditions are met, the workflow creates or updates an entry in the workflow_schedules database table. Among other data, this record identifies the involved workflow, the record that was saved to trigger the workflow, the date and time the save event occurred, and the date and time the actions and alerts will be performed. The date_expired field identifies the date and time the actions and alerts will be performed. The next time the Process Workflow Tasks scheduler successfully runs after this date and time, the alerts and actions associated with the identified workflow definition will be performed, and this record in the workflow_schedules database table will be permanently deleted.

It is very important to note that the conditions of a time-elapsed workflow are only assessed when the record is saved. These conditions are not assessed at any later time. The elapse of time is defined by the difference between the date_expired field of the record on the workflow_schedules table and the present date and time defined on the server. For more information about using the database table for troubleshooting, please refer to the Database Analysis section.

Prerequisites

The following item is required for instances hosted on-site and on Sugar's cloud service to properly execute time-elapsed workflows:

• The Process Workflow Tasks scheduler must be set to "Active" and should be executed at a high frequency. By default, this Scheduler is active and set to run every minute. For more information, please refer to the Schedulers documentation.

The following items are required for on-site instances to properly execute time-elapsed workflows. The Sugar Operations team ensures these requirements are satisfied for instances hosted on Sugar's cloud service:

• The cURL library must be enabled in your PHP configuration. You can verify cURL is installed and enabled in your PHP configuration by executing a phpinfo() and searching for "curl" in the output. To check this setting, open your php.ini file in a text editor and look for the following:
  • On Windows-based web servers: extension=php_curl.dll
    If this line is commented out with a ; remove the semi-colon, save your changes to the php.ini file, and restart your web server application to apply the changes.
  • On Unix-based web servers, cURL is compiled with PHP. Check your web server operating system's documentation for the proper installation instructions for cURL and PHP.
• A process on the web server must periodically run the cron.php file for your instance. For more information, please refer to the Introduction to Cron Jobs article.
  • On Windows-based web servers, a Scheduled Task is used.
  • On Linux-based web servers, the crontab file for the web server user is used.
  • On Mac-based web servers, the launchd operating system service management framework is used.

Resolution

Troubleshooting Workflow Fields

The Workflow module contains several pieces of information that serve both functional and organizational purposes in workflows. The following items may be influencing your workflow's ability to produce the desired result:

• Execution Occurs : Determines when and how workflows are triggered
• Target Module : The Sugar module that contains the record that triggers the workflow
• Processing Order : Determines whether any alerts will fire before or after any actions are executed
• Status : Determines whether the workflow is actively functioning or not
• Applies To : Specified which types save records will trigger the workflow
• Conditions : Conditions which must be met in order for the workflow to trigger
• Alerts : When a workflow triggers, emails which are sent to a specified list of recipients
• Actions : When a workflow triggers, actions which are executed on the target or related records

Execution Occurs

For time-elapsed workflows, the Execution Occurs field needs to be set to "After time elapses". If this is set to "When record saved", you may not be getting the results you expect. If this is erroneously set, click the Edit button in the top right corner, select "After time elapses", and save.

Target Module

Target Module is the module in which the save must be made in order to trigger the workflow. If the workflow is expected to trigger when contact records are saved but "Target Module" is set to "Accounts", you may not be getting the results you expect. If this is erroneously set to the wrong target module, click the Edit button in the top right corner, select the intended module, and save.

Note: The Target Module field cannot be readily edited once conditions, alerts, or actions have been set up on the workflow.  If the target module needs to be changed, you must delete the workflow and create a new one with the desired settings.

Processing Order

If your workflow is going to include both email alerts and workflow actions, select which of the two you would like to happen first. This should not affect whether or not the workflow triggers, but may impact the field data that appears in the received alert.

Status

When this is set to "Inactive", the workflow will not process. If you have a workflow that is not firing, ensure that the status is set to "Active".

Applies To

This field indicates if this workflow is going to fire on all saves, only when a new record is created, or only when an existing record is updated. If the workflow is being triggered when it is not expected, or if it is not being triggered every time it is expected, the workflow's Applies To field may not be set correctly. Ensure that this field reflects the intention of the workflow. For time-elapsed workflows, the recommended setting is "New and Updated Records".

Note: "New Records Only" should not be used as time-elapsed workflows, by nature, may be executed only after the record is initially created.

Troubleshooting Conditions

Workflow conditions can evaluate one or more fields on the target or related module. All conditions must be simultaneously met in order for the workflow to trigger. There are various issues that can arise depending on how your workflow conditions are defined.

Date Fields: Specified Time Versus Passage of Time

When defining conditions for Date and DateTime type fields, the two available time-based condition options appear very similar and can be easily confused. For the primary condition, a workflow can be triggered "When a field in the target module changes to or from a specified value" or if a "Field does not change for a specified amount of time".

When using a Date or DateTime field, you should consider whether you want the workflow to trigger when the field equals a certain date/time or if the workflow should trigger after a certain amount of time has passed since the field was modified. A "When a field in the target module changes to or from a specified value" condition will fire when the Date or DateTime field equals a particular value. A "Field does not change for a specified amount of time" condition will not depend on the Date or DateTime field's value; rather it will trigger when the Date or DateTime field was changed a certain amount of time ago.

For example, selecting "When a field in the target module changes to or from a specified value" with a Date field being "less than 6 months from now" will trigger on scheduler run following your modification of the Date field to a date 4 months in the future. However, selecting "Field does not change for a specified amount of time" with a Date field "has not changed for 6 months", then the time-elapse workflow will fire six months after you edit the Date field, regardless of the value you give it.

If your workflow is not firing when expected, or unexpectedly firing, ensure that the conditions being used accurately reflect your intentions.

Field Content Integrity

When defining specific values for a condition, particularly for text or currency fields, it is very important to avoid typos or unintended characters in both the field data and the workflow definition. It is also important that your workflow definition reflects the data that is being recorded in the record. For example, if the workflow definition is to trigger when the State field contains "IA", but users have entered "Iowa" into this field, then saving this record will not meet this condition, and the workflow will not be triggered.

Confirm data integrity by carefully comparing the field content in a sample record with the data defined in the condition of the workflow to ensure a perfect match.

Field Confusion

Two fields in the same module can have identical or similar labels. This can create confusion when selecting the field in the process of defining a condition. If there are two fields with identical or similar labels, ensure that the field in the condition is the intended field. Changing the label of one of these fields via Studio > {Module Name} > Fields is one of the simplest ways of distinguishing them.

Contradicting Conditions

When more than one condition is defined for a workflow, all conditions must be true during the one save event in order to trigger the workflow. It is possible to create two or more Conditions that can never be true at the same time. For example, there can be two conditions on the same field, with one looking for the value to be one string, and the other looking for the value to be a different string.

In the below example, a record's Type field cannot change to "Analyst" and contain "Competitor" because if it changed to "Analyst", then it contains "Analyst". These would, therefore, be contradictory conditions, causing this workflow not to fire.

Conditions for Fields in a Related Module

In addition to all of the above concerns, conditions that evaluate related records may be prevented from behaving as expected if there is more than one relationship between the two modules. In this case, please ensure with testing that you are specifying the correct relationship. Additionally, ensure that you are not confusing a relationship with a custom relate field. You must specify the correct link in order to have your workflow behave as desired.

Conditions and the Actions of Other Workflows

An instance can have more than one workflow affecting the same module and, therefore, the same records. The same save event can trigger multiple workflows, and the actions of one can affect whether or not the conditions of another workflow are met. Workflow sequence determines the order in which multiple workflows are triggered on the same save event. Subsequent workflows will be affected by the actions taken by previous workflows, but the converse is not true.

When a time-elapsed workflow executes, the affected record is saved. If the save event triggers a "When Record Saved" workflow, the actions of the triggered on-save workflow will be reflected in the record regardless of workflow sequence.

Troubleshooting Actions

Workflow actions can modify fields within the target module or fields in modules related to the target module. They can also create new records in related modules. Workflows can have one or more actions that are automatically performed when the workflow is triggered. There are various issues that can arise depending on how your workflow's actions are defined.

Field Confusion

Two fields in the same module can have identical or similar labels. This can create confusion when selecting the field in the process of defining an action. If there are two fields with identical or similar labels, ensure that the field in the action is the intended field. Changing the label of one of these fields via Studio > {Module Name} > Fields is one of the simplest ways of distinguishing them.

Two Actions Affecting One Field

A workflow's actions happen in a series, so if there are two actions that modify a field, only the last action's result will be reflected in the field after the save is complete. If you have an action that sets a field's value but this field does not contain that value after it is saved, ensure that no other actions in this workflow are setting this field to a different value.

Actions of Multiple Workflows

An instance can have more than one workflow affecting the same module and, therefore, the same records. The same save event can trigger multiple workflows, and the actions of one workflow can override the results of the actions of another. Workflow sequence determines the order in which multiple workflows are triggered on the same save event. A given field will only reflect the changes produced by the last action triggered to affect that field. All previous changes will be overwritten.

When time-elapsed workflows fire, the affected record is saved. If the save event triggers a "When record saved" workflow, the actions of the triggered on-save workflow will be reflected in the record regardless of workflow sequence.

Actions Affecting Fields in a Related Module

In addition to all of the above concerns, actions that affect related records may be prevented from behaving as expected if there is more than one relationship between the two modules. In this case, please ensure with testing that you are specifying the correct relationship. Additionally, ensure that you are not confusing a relationship with a custom relate field. You must specify the correct link in each action in order to have your workflow behave as desired.

Troubleshooting Alerts

Workflows can send email alerts, and there are various issues that can arise that affect a workflow's ability to correctly execute its alerts.

Outbound Email Settings

If the outbound email settings defined in Admin > Email Settings are not configured correctly, then workflows will be unable to send workflow alert emails. Confirm that this outbound account is able to send email from Sugar before continuing to troubleshoot workflow alert issues. For more information, please refer to the Email documentation.

Recipient Lists

The recipient list for the alert template can be populated with users, recipients (e.g. Contacts, Accounts, Leads, etc), or members of teams or roles within your instance. Alerts being received by these recipients depends on the email address field being populated correctly for the desired related records. To rule out whether one of these relationships could be causing the issue, temporarily set the recipient list to send only to a specified user with a known, good email address. If that user does not receive the email, the issue is likely elsewhere.

Alert Templates

Template Availability in Workflow

A workflow can only utilize templates that are created for that same target module as the workflow itself. When an alert template is first created, a module needs to be selected. Therefore, a Contacts module alert template will not be available to a workflow with Accounts as its target module. Ensure that the template you are looking for is in the same module as the workflow you are troubleshooting.

Invalid Values

If a variable that is expected to show the value of a field in a related module is displaying "Invalid Value" in a received email, ensure that records are related using the intended relationship, especially if there is more than one relationship in place between the two modules or if custom relate-type fields are being used in the record view that do not represent a relationship between the records.

For example, if a Contacts module workflow alert has a variable for the related account's name but the Contact record is not related to an account via the relationship specified in the alert template, then that variable will print "Invalid Value" in the received email.

Field Confusion

Two fields in the same module can have identical or similar labels or similar database names. This can create confusion when selecting the field in the process of defining an alert template. If there are two fields with identical or similar labels or similar database names, ensure that the field variable in the alert is the intended field. Changing the label of one of these fields via Studio > {Module Name} > Fields is one of the simplest ways of distinguishing them.

New Value Versus Old Value

A variable for inserting the value of the contact's Name field after the save action is complete is:
{::future::Contacts::full_name::}

A variable for inserting the previous value of the contact's Name field prior to the same event is:
{::past::Contacts::full_name::}

An "old" value variable will be blank for all new records created in Sugar because this record did not previously exist, so the field contained no data.

Troubleshooting Scheduler: Process Workflow Tasks

The Process Workflow Tasks scheduler executes all time-elapsed workflow actions and alerts. This scheduler may be causing the issue if none of the time-elapsed workflows are triggering as expected but on-save workflows are behaving as expected.

Recommended Settings

When viewing this scheduler in list view via Admin > Schedulers, the Interval should read: "As often as possible". The Range should span from a date in the past to a date in the distant future. By default, at the time of this article's publication, the values are: "01/01/2005 10:30am - 12/31/2020 03:59pm". The status should be Active.

Job Log

When viewing a scheduler in detail view, there is a Job Log subpanel that lists the log of recent runs this scheduler has had. If the job status of the most recent run is a value other than "Done", or the execution time is an unexpectedly long time in the past, these may be clues that the scheduler is not running as expected and may need to be further investigated.

On the other hand, if you do not see any recent entries in the job log and you have confirmed your server is set up to run the schedulers, please check the other active schedulers in the instance to see if a particular job is not completing. That job may be preventing other jobs from running in a timely manner due to an error.

Database Analysis

If you are confident that the time-elapsed workflow is appropriately configured and your schedulers are running normally, you can review the database to confirm events are happening as expected. SugarCloud customers do not have access to their database so this can only be done with instances hosted on a platform outside the SugarCloud environment. When a record is saved and the conditions of a time-elapsed workflow validate during that save, the record is then queued in the workflow_schedules database table. This table has the following columns:

When saving a record that you expect to validate against a time-elapsed workflow, immediately check the workflow_schedules database table to see if the record is queued. If the record is not entered into this table, then the conditions are not evaluating as expected. If you do see the record in the table, ensure that the date_expired column shows with the expected value. When the scheduler runs and processes records from this table, it will delete the row from the table.

Additionally, if the record is queued with a date_expired value in the past and it is not processing, you may want to see if another record in the table is preventing others from processing due to an error. You can isolate the first record the scheduler attempts to process with the following query:
SELECT * FROM workflow_schedules WHERE deleted = '0' ORDER BY date_expired ASC LIMIT 0,1;

With the results of that query, you can verify that the workflow and target record are still accessible and containing no errors. To access the workflow, copy the workflow_id value and insert it into the following URL format:
{instance_url}/#bwc/index.php?module=WorkFlow&action=DetailView&record={workflow_id}

If the conditions, alerts, or actions contain errors, you will normally see a red error message above the appropriate subpanel. These errors can result from the workflow referencing a field or relationship that has been deleted from Studio or removed from the application.

Monitoring the entire process from the database perspective will help you understand whether all other components described in this article are working as expected.

Testing Via The Database

The following steps will walk through testing a time-elapsed workflow using direct database access:

1. Ensure your workflow is configured correctly using the above troubleshooting steps.
2. Ensure that the workflow_schedules table does not contain any entries with a date_expired value in the past which may be halting the execution of other entries.
   SELECT * FROM workflow_schedules WHERE deleted = 0;
3. Create or update a record in workflow's target module in order to trigger the workflow to fire. Keep in mind that the workflow's conditions must all be satisfied.
4. Check that a new entry was placed in the workflow_schedules table. The bean_id field will match the ID of the record you created or updated in Sugar.
   • If the new record appears and the date_expired is the time specified in your workflow's condition past the time shown in the date_entered field, then your workflow evaluated its conditions and triggered successfully.
5. Set the row's date_expired value to a time that has already passed so that the workflow will execute its actions and alerts on the next cron run without having to wait the full time delay.
   UPDATE workflow_schedules SET date_expired = '<time in past>' WHERE id = '<ID of row>';
6. Allow your web server to run the process that executes cron.php.

If your workflow's alerts and/or actions are executed after the next cron run, the workflow has completed successfully. You can check the contents of your workflow_schedules table again to confirm that the row has been deleted.
SELECT * FROM workflow_schedules WHERE deleted = 0;

If your workflow's alerts and/or actions are not executed, use the query above to check the workflow_schedules table. If the row no longer exists then the workflow executed but may have encountered an issue with sending an alert email or executing an action. Refer to the Troubleshooting Actions and Troubleshooting Alerts sections above. If the record still exists in the table, you should ensure that cron.php is running and that the Process Workflows Tasks scheduler is active with entries in the Job Log subpanel corresponding to the last cron.php run. For more information, please refer to the Troubleshooting Cron and Schedulers article.

Additional Help

If the above areas did not reveal or resolve the issue you are experiencing, please reach out to others for additional assistance. There is an active community at sugarclub.sugarcrm.com. There may be a discussion of a similar issue there or you can start a discussion on the topic. You can also file a case in order to have SugarCRM Support assist you in troubleshooting your workflow.