1. Tickler Date: a DateTime field that triggers the workflow to send out the email notification
2. Tickler Contacts: a People field (multiple section) where the notification email will be sent to
The requirement is simple:
1. Email notification sent to "Tickler Contacts" whenever the "Tickler Date" is reached, if both fields are defined.
2. Notification should be aware of the change on those two fields, and updated "Tickler Date" and "Tickler Contacts" should be applied.
There are a few options to accomplish such task. We can set a Windows Scheduled Task to run a console app which scans the whole list daily and send the notification, or use SharePoint Timer Jobs to do similar things, or implement SharePoint Workflow. SharePoint Workflow is very powerful and capable of handling many SharePoint tasks easily. With SharePoint Designer, you can even visually work on workflow's conditions, steps and actions, and apply the Workflow directly to the server without any code deployment (arguably good and bad on this). Workflow approach will be discussed in this post.
SharePoint Designer Workflow has a built-in "Send an email" action. We use that as the only one action step in our first Workflow for notification. Because the recipients come from a multi-user People field, we need to set Person field returned as delimited email addresses:
By default the BCC is not available on the email form. However, when you can highlight the send email action and click the "Advanced Properties" button on the top menu you will see the BCC field. You could even fine tune the formatting of the mail content since the original body HTML can be seen and updated there:
The next step is to figure out how to start the Workflow. A list-associated Workflow can be set to start manually, or automatically start when a list item is created or updated. For the simple Workflow we defined above is not going to start automatically because we haven't defined the condition yet.
If the trigger condition is based on the date time field, then we can also utilize retention policy to start a Workflow. Go to List's settings page and click "Information management policy settings" you will see following settings (the top "List Based Retention Schedule" won't be available if "Library and Folder Based Retention" Site Collection Feature is not activated):
We are interested in the item properties, so click the Item and go to Item policy editing page, check "Enable Retention", and then add a retention stage:
The condition here is that when "Tickler Date" is passed by 0 day, or "Tickler Date" is today, then workflow will start and send the email notification. All look good except that requirement #2 can not be satisfied. The "Tickler Date" is fixed, and Retention policy won't recalculate the condition when the "Tickler Date" is changed. There's no workaround on that as far as I know. So Retention policy is not an option in our case.
The solution is let Workflow deals with the conditions by itself, and make it start automatically when a List item is created or updated:
The Workflow quite explains itself. First it checks if "Tickler Contacts" and "Tickler Date" are both valid. It would simply "quit" (WF completes) if any of those two empty or "Tickler Date" has passed. Otherwise it continues to further logic. A Workflow variable is set to the "Tickler Date" so later it has a reference to compare if that has been changed. Then two parallel branches are split to run simultaneously. The first branch is waiting until the "Tickler Date" reaches so email notification will be sent out, and the workflow completes its work; the second branch is pending and looking for any change on the "Tickler Date" field, when that occurs the workflow stops and a new workflow instance will be created by SharePoint using the updated "Tickler Date", because the workflow had set to auto-start when list item is updated.
You may ponder what happens if the fields other than "Tickler Date" have been changed, would a new Workflow instance will be created? The answer is no. SharePoint only allows one instance of Workflow for each version to run. That's why we cancel the old Workflow instance when the "Tickler Date" is changed, so a new Workflow instance can be created and start.
You may also wonder why not just simply using the general "Modified" field instead of "Tickler Date": cancel the old Workflow instance whenever the List item is changed, then there’s room for a new WF instance with updated data. You will see that won't work if you try it out. The reason is that SharePoint does modify the List item silently for some background process such as updating List item's Workflow status. That causes time change on List item's "Modified" field which stops old Workflow, but SharePoint won't start a Workflow for such internal processing.
A final note: don't use system account to test your Workflow. It won't work! Creating or updating a list item using system account won't trigger associated Workflow even although the Workflow is configured to auto-start. Under the hood List Workflow was implemented using List Item Event Receivers, and system account is just ignored there.
