You can automate repetitive tasks, reduce mistakes, and standardize common actions using the Workflow Rules functionality.
Some examples of workflow rules:
Workflow Rules come in two types:
Workflow rules are available for the following objects in the system:
IMPORTANT! The user should have prior knowledge of SQL language and understand the structure of the existing database before attempting to construct a SQL statement. Otherwise please contact us to create or modify the rule for you.
Rule Name - Identifies the rule. You can provide a brief description of the workflow or update being done.
Scheduled - If selected, the workflow item will run regularly. If you leave it unchecked it means this is an on-demand action.
Active - Check to enable the rule to run either on-demand or scheduled. Uncheck to prevent the rule from running and appearing in the Actions dropdown.
Sort - Specifies the order in which the workflow items will run and show in the Actions dropdown if more than one workflow item exists.
SQL Select - Allows you to specify if the object you are looking at on the screen is eligible for this rule. Specifies a SQL Select statement that should return a row if the rule applies to that particular object (task/lead/client/creditor/account) or no rows if the rule should not show or apply. More details and examples below for each rule type.
The eligibility is based on the SQL returning one or more rows. If a row is returned but all the columns are NULL it is still considered eligible.
On Demand rules do not have any email functionality.
When using an on-demand rule, you must use a SELECT statement NOT A STORED PROCEDURE as the statement is embedded inside an EXISTS () clause.
For on-demand rules we do not care what column(s) you return on the SQL Select. You could use something like "SELECT 1" or "SELECT {IssueID}" although those statements would make the rule always eligible since a row is always returned.
SQL Update - Specifies the SQL to run that will either update the data in the database or execute a system command.
What bookmarks can I use in SQL Select and SQL Update?
For all workflows: {UserID} - The user logged in
For Task Workflows: {IssueID} - The current issue on the screen
For Lead Workflows: {LeadID}. {DebtorID}, {DebtorType} - The current lead on the screen
For Client Workflows: {ClientID}, {DebtorID}, {DebtorType} - The current client on the screen
For Lead Account Workflows: {LeadID}, {DebtorID}, {DebtorType}, {LeadClientCredID}
For Client Account Workflows: {ClientID}, {DebtorID}, {DebtorType}, {ClientCredID}
For Creditor Workflows: {CreditorID}
For Email Workflows (used in the Outlook Add In):
{IssueID} - The current issue identified by the Outlook Add In
{ClientID} - The current client identified by the Outlook Add In from the email From address
{LeadID} - The current lead identified by the Outlook Add In from the email From address
{CreditorID} - The current creditor identified by the Outlook Add In from the email From address
{ClientCredID} - Not used
{LeadClientCredID} - Not used
{EmailSubject} - The subject of the current email message
{EmailBody} - The body of the current email message
{EmailFrom} or {EmailSender} - The sender of the current email message
{EmailTo} - The recipient of the current email message
{EmailCC} - The CC of the current email message
{EmailSentOn} - The date of the email message
Can Be Applied as Bulk Action - If selected the workflow rule will be available to be assigned to a saved search. Only workflow rules with SQL Updates can be used as Bulk Actions.
Questionnaire - You can specify a Dynamic Questionnaire that will open when you run this rule from the Actions dropdown (works in web and Windows but if you use the same action for both then the Dynamic Questionnaire must be compatible with both web and Windows).
When using actions a response message can be returned to the user as a popup. The last line of the SQLUpdate should return text as the column [OutputText]
For example: in the rule below that changes the Lead Status to 'BAD'. Notice the SELECT 'MY MESSAGE' as OutputText syntax highlighted.
Declare @Date datetime, @Result int
SET @Date = GetDate()
EXEC @Result = ChangeLeadClientStatus {LeadClientID},'BAD',@Date,NULL,'{UserID}',NULL
If @Result = 1
Select 'Success' AS OutputText
ELSE
Select 'Fail' AS OutputText
Workflow rules can be configured to run commands in the update statement by returning the column Outputcommand.
If doing so, it is recommended to show the popup message first using the column OutputText mentioned above.
The following commands can be used: OpenCreditor, OpenIssue, OpenClient, OpenLead.
The command must be in the following format, the @ID would have been a variable identified in the SQL Update at some point.
select 'Message here' as OutputText, '[Command.OpenIssue]' + cast(@ID as nvarchar) + ']' as outputcommand
Example: Open Browser to a page
select '[Command:OpenFile|www.google.com]' as outputcommand
Example: Close Task Screen and open the next from the User's List
SELECT TOP 1 '[Command:CloseIssue|{IssueID}][Command:OpenIssue|' + cast(IssueID as nvarchar) + ']' as outputcommand
FROM Tasks
WHERE AssignedUserID = '{UserID}'
AND ClosedDate IS NULL
AND CreatedDate > (SELECT CreatedDate FROM Tasks WHERE IssueID = {IssueID})
Example: Show Message will display a message in a message box
Select '[Command:ShowMessage|Message to display|Message Title]' as outputcommand
SELECT '[Command:Covid19-Financial Assistance|Advise Clients of New Covid Concessions Payment Alternatives|New Programs Available]' as outputcommand
The full list of commands can be found here.
Schedule Frequency - Specifies the frequency of the workflow item. The period is based on the Schedule Period. The Schedule Frequency should not be less than 5 minutes or the performance of the server may be compromised. Even if a lower number is entered, it will default to run every 5 minutes.
Schedule Period - Specifies the period in which the workflow item is scheduled. These options are based on the SQL language structure. (i.e. mi indicates minutes, dd indicates days)
Schedule Start Date - Specifies the start date for which the workflow item is scheduled to begin. The user can double click the field to open the calendar. You must enter a Start Date here.
Last Time Run - Specifies the date when the last workflow item was run. When adding a rule you must enter an initial date in the past here.
SQL Select - Allows you to specify which rows to process in the SQL Update. Specifies a SQL Select statement that should return a row for every item (task/lead/client/creditor/account) that you want to process in the SQL Update.
More details and examples below for each rule type.
If you leave the SQL Select statement empty, then the SQL Update will run only ONCE and repeat again based on the schedule.
If you include a column called Email, then it will send an email to that email address.
When using a scheduled rule, you can use a stored procedure but you must AVOID NAMED PARAMETERS as every @variable word in the SQL Select is prefixed with the rule ID to make it unique.
Your SQL Select must return the following column depending on the type of workflow:
For Task Workflows: IssueID
For Lead Workflows: LeadID
For Client Workflows: ClientID
For Lead Account Workflows: LeadClientCredID
For Client Account Workflows: ClientCredID
For Creditor Workflows: CreditorID
For Email Workflows: Email. Scheduled email workflows are only for emailing.
SQL Update - Specifies the SQL to run either once (if SQL Select is empty) or for every row returned by the SQL Select.
A SQL Update will either update the data in the database or execute a system command. More details below.
What bookmarks can I use in the SQL Update?
For Task Workflows: {IssueID} or @IssueID - The current IssueID being processed from the list of candidates that the SQL Select returned
For Lead Workflows: {LeadID} or @LeadID
For Client Workflows: {ClientID} or @ClientID
For Lead Account Workflows: {LeadID}, {LeadClientCredID} or @LeadID, @LeadClientCredID
For Client Account Workflows: {ClientID}, {ClientCredID} or @ClientID, @ClientCredID
For Creditor Account Workflows: {CreditorID} or @CreditorID
For Email Workflows:
Your SQL Select can return additional columns which the SQL Update can use, such as:
IssueID, PersonName, Category, Summary, SubjectOverride, DebtorID, DebtorType, IssueNoteID , CC, BCC
Those can be used in the SQL Update by using the following variables:
@IssueID, @PersonName, @Category, @Summary, @SubjectOverride, @DebtorID, @DebtorType, @IssueNoteID , @CC, @BCC
You can use the following pre-declared variables both in your SQL Select and SQL Update:
@ICMID - Indicates the rule ID
@CTR - Indicates the Start Time of the current run
@LTR - Indicates the Last Time Run of the rule previous to the current run
Typically you will use @CTR and @LTR in your WHERE clause to filter only for eligible records added or modified between @LTR and @CTR (like WHERE DateAdded > @LTR and DateAdded <= @CTR for example).
Emailing applies only to Scheduled rules.
If your SQL Select in your scheduled rule returns an Email column with a valid email address the rule will send an email when it runs.
Email specific fields in the Workflow Rule Settings:
Subject - Specifies the content of the subject of the email sent (unless a Subject column in the SQL Select overrides it).
Header - Specifies the content of the header of the email sent.
Body - Specifies the body of the email sent. If you leave it blank, the email body will be calculated based on the SQL Select (see below)
Footer - Specifies the content of the footer of the email sent.
Database Mail Profile - Enter name of DB Mail Profile to be used for the rule. Leave this field empty to use the SQL Server's default profile.25816
Contact Notified - Only for Task Workflows - Used to indicate that the rule is for notifying co-applicants linked to the Task. It will update the coappnotifeddate on the Tasks table.6953
User Notified - Only for Task Workflows - Used to indicate that the rule is for notifying the user linked to the Task. It will update the usernotifieddate on the Tasks table.
To send an email when the rule runs you can use the following columns in your SQL Select:
Email - The email address of the recipient. The Email column is required when you want to send an email when the rule runs.
CC - Copy. You can use email addresses separated by semi-colon (;)
BCC - Blind copy. You can use email addresses separated by semi-colon (;)
Subject - To specify the subject of the email. If blank the email will use the information in the Subject field of the rule or will say "Task Notification" if that is left blank as well.
Summary - You can add your own email text by returning a Summary column in your SQL Select
ClientID - For Client Workflow Rules - Will add an audit log entry that the rule ran for this client
LeadID - For Lead Workflow Rules - Will add an audit log entry that the rule ran for this lead
DebtorID & DebtorType - For Task Workflow Rules - Will add a log entry in the client or lead activity log
IssueID - For Task Workflow Rules - Will add a log entry in the Issue Log for that issue
CreditorID - For Creditor Workflow Rules - Will add a log entry in the Creditor Activity log
The email Subject will be:
The value of the Subject column in the SQL Select if it has one, or the Subject of the Workflow Rule in Settings
The email Body will be composed of the following four elements concatenated:
If the rule has text in the Body field in Settings then it will use that.
Otherwise, if the IssueID column in the SQL Select has a value then it will use:
TaskID: <IssueID column value>
Category: <Category column value>
Summary: <Summary column value>
Else, if the IssueID column is NULL then it will just use the value of the Summary column.
Returning the Summary column in your SQL Select is the most flexible way to generate the text of your email as you can place all your logic in a function and include any fields you want from the database.
In your subject and body you can use table column bookmarks in the format {Table.Column} such as {Issues.Status} for example.
Task Workflow Rules allow you to:
and/or
This can be done at regular intervals or on-demand from the Actions dropdown in Task Details both in Windows and Web
Whether a rule applies to the task can be specified by coding the SQL Select. The SQL Select can be based on the current state of the task or other related object.
Examples:
For example: The user can create a workflow that changes the state of Tasks with Category: Error, Priority: High, Status: Open, to a different state, such as Category: Enhancement, Status: To Test.
In addition, when the status of an Task is set to ‘Closed’, a workflow can be created to send an email to the client to update them that the Task has been completed.
The Workflow Rules category allows the user to manually add and edit Task changes or workflow items. This includes adding items affecting right-click menu options, scheduled tasks/updates and email functionality.
For example: The user can add a SQL statement that will email the client specified in an Task when the user has changed the status of the Task. This will allow them to be informed of the progress of the work, or the user can have the client be notified each time billable hours have been added if they want to be informed of accumulations due to budget restrictions.
When writing workflow rules for Leads or Clients LeadID or ClientID would replace TaskID in all cases.
TaskID: The Task number from the Tasks table (Tasks.TaskID). Integer.
PersonName: The name of the recipient. 50 characters max.
Email: The email address of the recipient. 50 characters max.
Category: The Task category from the Tasks table (Tasks.Category). 25 characters max.
Summary: Typically the Task summary (Tasks.Summary). 255 characters max. When sending emails not based on Task Information the summary can have a max of 3000 characters.
@ICMID: Hardcoded variable. Use @ICMID. The code will replace the variable with the Task Change Master ID which is the unique identifier for each Workflow Rule.
TaskNoteID can also be used when sending emails after Task notes are added. 25772
Workflow rules can also be configured to run other automatic tasks like creating Tasks for NSFs or follow-up calls.
The SQL Select statement can also return a list of Taskid's and then the SQL Update will run once for each TaskID.
Workflow rules can show on the DQ_ViewIssue.aspx for Task Workflow Rules for those checked as available for that login type.
Lead/Client Workflow rules would show on the Notes.aspx for Web Users as long as those are available for that login type. They can also show on Client/DynamicClient.aspx and SignUp/DynamicProfile.aspx.
Account Workflow rules show on Client/DynamicAccountDetails.aspx, SignUp/DynamicAccountDetails.aspx, and Creditor/DynamicAccountDetails.aspx
These items can perform the update to run SQL or open a DQ. When using a workflow rule with a DQ the Post Script for the DQ will allow these variables to be replaced:
{ClientID}
{DebtorID}
{DebtorType}
{LeadID}
{IssueID}
{CreditorID}
{DQ_QuestionnaireHistoryID}
{DQ_FormID}
Email User Assigned to Task based on Priority
Email Client Confirmation When an Task is added on the web