Calendar Reminder (Part 2)
In Poor Folks Calendar Reminder (Part 1) the problem, the request, the plan of attack, and the first step in building a reminder behavior in a SharePoint 2007 calendar was covered. This post is the final installment on this topic.
Building Steps – People
Since each calendar due date/event could have a different set of people to notify, I decided that my initial implementation of this would be simple. I kept it simple by using a plain multi-line text field in the calendar list to capture the email addresses of the desired recipients. So in addition to the List Fields mentioned last time, I also created the following column:
ReminderEmail – Multiple lines of text, not required, 6 lines for editing, Plain text
In the future I may make a modification to improve this feature of the reminder depending on how the email is really used. For your reviewing pleasure, here is what my columns look like:
Reminder Columns added to Calendar List
Building Steps – Workflow 1
My workflow has four steps, each described below. To build this workflow:
- Launch SPD (SharePoint Designer)
- Open your site
- Select File, New, Workflow
- In the Workflow Designer window, Name your workflow (default is Workflow 1)
- Select the Calendar list that has the “reminder” columns added
- Check the Automatically start this workflow when a new item is created check-box
- Keep the check in the Allow this workflow to be manually started from an item check-box
- Click Next
Before we start creating Step 1, I want to add some Initiation Parameters. I will explain why, and it will be more apparent why later, so for now just click on the Initiation… button, we will add two parameters:
- In the Workflow Initiation Parameters window click the Add… button
- Field Name: ResetDate
- Information Type: Choice (menu to choose from)
- Click Next
- Enter the Choices: “No” and “Yes”
- Default value: No
- Display as: Radio buttons
- Click Finish
- Click Add…
- Field Name: TempDateTime (I chose this name to avoid any possible conflict with other variables, functions, reserved words, etc)
- Information Type: Date and Time
- Next
- Select the Use date and time item creation as default or Blank default value radio button
- Display format: Date and time
- Click Finish
- Click OK to save the Initiation Parameters and close the Workflow Initiation Parameters window
Workflow Initiation Parameters
Step 1 – Check Reminder, Set Variables
Step 1 will have 4 Conditional Branch’s :
Conditions: select Compare Calendar field, then select the Reminder field in the Current item and set it equal to “No”
select Compare any data source, then select Workflow Data and the Field Initiation:ResetDate, set it equal to “No”
Actions:
select Set Field in Current Item then select the ReminderStatus field to “No Reminder Set”
select Log to History List, enter “ReminderStatus Set to No Reminder Set”
select Stop Workflow, enter “No Reminder Requested”
This first condition checks to see if the user chose to send a reminder for the event/calendar item. If they did not (No value in the Reminder column) then we want to note that in the ReminderStatus column, log it in the workflow history and Stop the workflow. The default for our Initiation Parameter ResetDate is No, so our workflow would stop here if the workflow automatically started on a newly created item and the user selected not to send a reminder.
For the remaining Conditions, I will assume you can select the correct components to insert and simply show you the end result.
Workflow Step 1
Conditions: Else if Reminder equals Yes
and Initiation: ResetDate equals Yes
Actions:
Log “Reminder Requested” to the workflow history list
then set ReminderStatus to Waiting
then set ReminderDate to Initiation:TempDateTime
then Log “Set Status to Waiting, Set ReminderDate” to the workflow history list
Conditions: Else if Reminder equals No
and Initiation: ResetDate equals Yes
Actions:
Log “Reminder Requested Manually” to the workflow history list
then set ReminderStatus to Waiting
then set ReminderDate to Initiation:TempDateTime
then Log “Set Status to Waiting, Manual ReminderDate” to the workflow history list
Conditions: Else if Reminder equals Yes
and Initiation: ResetDate equals No
Actions:
Log “Reminder Requested” to the workflow history list
then set ReminderStatus to Waiting
then Log “Set Status to Waiting,Using Item ReminderDate” to the workflow history list
Step 2 – Pause Using Days
This step handles the user option of selecting the number of days prior to the event that the reminder should be sent. This step contains the same number of Conditional Branch’s as you determine. Mine contains 6 because I provide 6 options to the user (0,1,2,3,4,7) however I will only present a few here, the same concept applies to each that you repeat.
Conditions: If ReminderDays equals 0
Actions: Log ReminderDays is 0 to the workflow history list
If the user hasn’t selected a number of days, and the default is 0 then we want to make note of it, but continue on.
Conditions: Else if ReminderDays equals 1
Actions:
Add -1 days to Calendar:Start Time (Output to Variable:date1)
then Log “1 Day Reminder – Pausing Until:” to the workflow history list
then Log Variable:date1 to the workflow history list
then Pause until Variable:date1
Conditions: Else if ReminderDays equals 2
Actions:
Add -2 days to Calendar:Start Time (Output to Variable:date2)
then Log “2 Day Reminder – Pausing Until:” to the workflow history list
then Log Variable:date2 to the workflow history list
then Pause until Variable:date2
For each of your options simply subtract the number of days found in the ReminderDays column of the current item.
Workflow Step 2
Step 3 – Pause Using Date
This step handles the user option of sending the reminder on a specific date. It only contains one Conditional Branch:
Conditions: If ReminderDays equals 0
and ReminderDate is greater than or equal to Today
Actions:
Log Pausing Until Date: to the workflow history list
then Log Calendar:ReminderDate to the workflow history list
then Pause until Calendar:ReminderDate
Workflow Step 3
Step 4 – Send Reminder
The final step in our workflow simply checks to see that we are actually supposed to send a reminder, so one Conditional Branch handles it all:
Conditions: If Reminder equals Yes
Actions:
then Log “Sending Reminder to:” to the workflow history list
then Log Calendar:ReminderEmail to the workflow history list
then Set Variable:EmailAddresses to Calendar:ReminderEmail (this allows us to use the variable in the actual email To field)
then Email Variable:EmailAddresses
then Log “Email Sent – Ending Normally” to the workflow history list
Final Discussion
So what does our new workflow do? We collect the options the user selected when creating the Calendar item in our new columns, and use that to determine how to Pause the workflow until the correct amount of time has passed. Then we simply pause for that time and send the email with the desired reminder text.
Why we have the Initiation Parameters should be more apparent to you now. If the user wants to run this workflow manually because they didn’t set a reminder originally or if they want to send another reminder our workflow would run and stop without sending the reminder email without these parameters. By asking for the two parameters when running manually we give the user the option to kick off the workflow and adjust the reminder date or add a reminder date for that instance of the workflow. You can see how I use this in Step 1 to update the ReminderDate column and use that date to pause and send the email reminder.
RSS - Posts
Just wanted to say thanks for putting this together. It really helped me develop a solution for an executive request that came down this week. Thanks a lot.
Where it says Variable:EmailAddresses how do you set this? this is the first time this variable is mentioned in the tutorial???
In step 4, you create the variable named EmailAddresses and set it to the Calendar:ReminderEmail column.
When you select Actions, select Set Workflow Variable, then click on the workflow variable link that appears and select Create a new variable… Name it EmailAddresses.
Then click on the value link and the fx button that appears, for Source leave it as Current Item, and for Field select ReminderEmail (which should be a column created in your list previously).
The purpose behind this is to allow your users to enter email addresses into the calendar item for the people they want to receive this reminder. This part of the step pulls the email addresses entered into that calendar list item column (ReminderEmail) and assigns that value to the new Variable. In the next Action we send an email To the new variable EmailAddresses.
I was wondering if there was any way that you could maybe assist me with setting it up so that users can check a box and it would use their current login information to grab their Email address and they would have the option to set when they wanted to be reminded about an event. since we are still on 2003 it has to come through email not reminder.
Hi Kyle, I created this for MOSS 2007, haven’t tested any of it in the 2003 environment, and I am not nearly as knowledgeable with 2003. So I wouldn’t be much help trying to accomplish what you are looking for. There are 3rd party web parts that do what you are asking, which may be a much easier route if you can consider the purchase. My initial guess is that you would need to do some coding to get 2003 to provide the feature/behavior you are describing.
Regards,
tony (SharePoint Jack)
ABSOLUTELY WONDERFUL. Great blog.
Thanks, fantastic tutorial! Worked a treat.
Hello, this is awesome! Thank you so much for sharing it.. I have a couple of questions/problems…
1. instead of the reminder going out 1 day prior to the event, I’d like it to go out 5 min after the event starts. The events are all day. Its a schedule and each day has the person’s name and they are scheduled to triage a certain area. After running this workflow I get weird dates in the log.. for instance:
Reminder Requested
6/25/2009 8:38 AM Comment Set Status to Waiting, Using Item ReminderDate
6/25/2009 8:38 AM Comment 1 Day Reminder-Pausing until:
6/25/2009 8:38 AM Comment 1/1/1900 12:00:00 AM
6/25/2009 8:38 AM Comment System Account
Pausing for 0 minutes
2. Im not sure how to tell the workflow what addresses to send too and I have a specific reminder email crafted to go out..another example of the log:
Reminder Requested
6/24/2009 3:45 PM Comment Set Status to Waiting, Using Item ReminderDate
6/24/2009 3:45 PM Comment 1 Day Reminder-Pausing until:
6/24/2009 3:45 PM Comment 12/31/1899 7:00:00 PM
6/24/2009 3:45 PM Comment System Account
Pausing for 0 minutes
6/24/2009 3:45 PM Comment System Account
Pausing complete
6/24/2009 3:45 PM Comment Sending Reminder To:
the work flow is still in the In Progress mode..
thanks for any help you can provide…
Jen
Jen,
Question 1 – two parts. Part one – The date/time that is displaying is coming from a default date, I would say that the ReminderDate isn’t getting a value set. Check the Actions in Steps 1 and 3 to make sure it is getting a value assigned. In my example this gets set to the initiation variable in Step 1 and by Step 3 it is just being used. Check that you are selecting a Date and entering a Time when setting up the calendar event or when launching the workflow manually.
Part two – You would have to set the ReminderDate time portion to the exact time you wanted the reminder – for example if it was an 8am meeting you would have to input “8AM 05″ to have a reminder set to the date and 8:05 AM for the reminder email. Be aware that between processing time, server clock variations, and email lag time your email may not reach people until several minutes later. I suggest sending the email either shortly before or at the start of the event to make sure it arrives within the rough time span you want. Test it several times before you feel confident that it arrives when you expect it.
Question 2 – Part of the problem, if you followed my steps closely, is that I missed an action in Step 4. It needs to include:
then Email Variable:EmailAddresses
Sorry I missed that line, I have updated the posting to include it now.
Now that is added, you can see that the email will be sent to whatever email addresses you enter into the calendar event field ReminderEmail (See Step 4). If you haven’t added the custom fields to your calendar from Part 1 of this article, or caught the additional column “ReminderEmail” that I mention at the beginning of this posting under Building Steps – People, that would be the problem.
You also have the option of creating a SharePoint group containing the people who are to receive the email (if that is fairly static) and use it rather than a column in the calendar. The Email action in SPD will allow you to select specific people, hard-code the email address, or use a SharePoint group in addition to populating the “To:” field of the email with column data.
I think correcting these two things will at least make progress with your reminder, let me know how it goes.
tony
Thanks Tony, Ive made the changes and will try for tonight’s reminder and let you know how it goes..
Jen
Hi Tony.. Ive been going over and over this and I still get the strange date and time : 1/1/1900 12:00:00 AM
Jen, can you verify that your server clock is correct? It looks like you are using the Pause Using Date (Step 2) branch of the workflow – can you get a valid reminder if you use one of the other options?
Other ideas: Check the Action where the ReminderDate is set to the Initiation variable (TempDate), if that variable hasn’t been set then it could be a cause. Also track variables Date1 and Date2 to see if they are being set correctly. Is the Start Time column a valid date?
I would try walking through the workflow to find and track all date variables and columns, then add more “Log to history” Actions that display each of the date variables and date columns at every step and action. Then manually run the workflow (entering the initiation variables) and look at the log. That should help narrow where the crazy date is coming from.
You can remove those extra log entries after you figure out where the date needs to be fixed.
I am fairly confident that the ultimate problem is that one of the date variables or columns is not being populated with a valid date, but is being used by the workflow.
tony
Hi Tony, I actually fixed the date problem…but this time a new error occurred at the sending email step. I think its in the emailaddresses variable..should that be a string?
7/7/2009 2:16 PM Comment Remider Requested
7/7/2009 2:16 PM Comment Set Status to Waiting, Set ReminderDate
7/7/2009 2:16 PM Comment 5 minute Reminder-Pausing until:
7/7/2009 2:16 PM Comment 7/8/2009 12:05:00 AM
7/7/2009 2:16 PM Comment System Account
Pausing for 588.14468096 minutes
7/8/2009 12:05 AM Comment System Account
Pausing complete
7/8/2009 12:05 AM Comment Sending Reminder To:
7/8/2009 12:05 AM Comment
7/8/2009 12:05 AM Error System Account
An error has occured in Reminder Workflow
in my reminder email calendar field I have the users email that is to get the reminder… then in the workflow I have the TO field set to variable:emailaddress and a CC to their project lead… and then the subject and the actual reminder email…
thanks again for all of your help…
Jen
Hi Jen,
If your reminder email calendar has a field with the users you should use it in the TO field rather than the variable:emailaddress unless you are setting that variable to the value of your calendar field. I think that is why the comment in the log is blank…and I know that the email will fail and error out the workflow if the email address is blank or not valid. The email address variable should be a string and the email address field in the calendar list should be plain text.
Sounds like you are getting close to a working reminder, hang in there.
tony
Workflow looks good, can’t wait to see how it works… however…
I’m getting an error on Step 4, where the action is Email Variable:EmailAddresses. When I check the workflow it highlights in red the *Variable:EmailAddresses* as being incorrect. It’s like the email address is invalid. If I put my actual email address the workflow is accepted.
Your assistance would be greatly appreciated.
Thx
dan
Dan,
Check the TYPE for your variable. I used String because my users are entering the fully qualified email address. You probably need that type also.
Hi Tony, the workflow is working very well with one exception…when I set the reminder for any Monday the workflow sees it as Tuesday. .and does not run..it works for every other day of the week.. including Saturday’s and Sundays.. but every Monday morning, I have to go in and manaually run the workflow to send the reminders.. so Monday reminders are always late..I can’t seem to figure it out…
Thanks for all of your help.. hopefully you have an idea for this one..
Jen
Hi Jen,
Is there any system maintenance happening on Monday mornings? My first thought is that either the system is down or something is unavailable when the timer service wants to kick off the reminders on Monday’s.
tony
Thanks – even I could follow your instructions. Worked the first time!
Ok seems everything is working ok, the workflow says emails are being sent but I have not received any notifications. I know the email is correct so I am at a loss. Any suggestions?
UPDATE: My emails are going through to the individuals that are CC’d on the workflow but for the email that is put in the reminderemail section; those are not coming through. Ugh frustrating because it is so close to working
Hi Tonya,
I would start with reviewing Step 4…double-check your reminderemail variable in the workflow, is it pulling the email from the Calendar column? If so, then check the Calendar column to make sure it is a text column. Is the email address in the calendar column valid? If there is more than one is it separated with a semi-colon (;)?
After that, jump back to the workflow and check the email, is it using the variable in the TO column, does it look ok?
If you have done all of that and it is still a problem, then I would add some “Log to History” actions in the workflow. Output the value of the calendar email column to see that it is reading it and displaying a valid email address format. Next, output the value of the variable after you assign that column to to it so you can see that the variable contains a valid email address. Then run a test so you can review the new log entries, hopefully you will see something there that is causing the problem.
The only problems I have seen have been with the format of the email address. Sometime people have copied an email address that has “changed” to the display format (for example: “FirstName LastName”) and the address behind didn’t copy over, only the proper name. My guess is that it has something to do with the email address, especially since the cc is working.
Hope this helps,
tony
Thank you for this workflow, it works great. Here are the messages from my workflow:
“The email message cannot be sent make sure your outgoing emails settings for the server have been configured correctly. ”
As far as I can tell my outgoing email is configured correctly. I am getting alerts from lists. Is there anything els I can check?
How do you send the email to someone other than yourself?
I apologize for so many posts. I am not sure I have completed the second to the last step in Step 4 correctly. In the work flow you choose the Action to Send Email. Once the window opens a blank email screen appears and I am not sure how to fill in that email.
TO: what goes here? Variable:EmailAddress? and should this be just typed in?
FROM: should this be blank or is this automatically populated by SharePoint and will this email com from the email address that is in the to/from in the outgoing mail setting in Central Admin?
SUBJECT: should I input “Reminder” or something like that
BODY: should this be blank?
A second unrelated question. Can I apply this workflow to an existing calendar list provided I add the additional columns, reminderdate, etc?
Thanks for any help you can provide
Hi Sade,
It sounds like you may need to check your SMTP settings in Central Admin, or the SMTP service to be sure it is all configured and running correctly. I say this because you shouldn’t have a FROM field in the email…it should be pulled from the settings.
Anyway, I will address your other questions:
TO:
Notice the little Book Icon on the right of the To field in the Define E-Mail Message window. Click that icon to select Workflow Data and access your Variable. You can also select “User who created current item” or SharePoint groups, users from your address book etc. by using this “Select Users” window that the icon pops for you.
Note that you can do the same in the CC field
SUBJECT:
Yes I would put something like that for the recipient to understand what the email is about. Notice the fx button at the end of the Subject field…this will let you put data from your calendar list item into the subject line.
BODY:
I suggest mixing some “hard-coded” text and data from the reminder. Mine has something like
“You have a new Reminder:” and then place the calendar item Title or Description text after the colon by clicking on the Add Lookup to Body button and selecting them from the Current Item list.
Try the various options you have and you will see that you can create a nice message in the body with content from the current item, customizing the message for each reminder.
Final Question:
You cannot apply the workflow to an existing calendar without rebuilding it in Visual Studio. These workflows only work on the calendar list where they are created. That is a restriction of SharePoint Designer.
Well now it seems I am getting all the emails at once. If I understand this correctly, I leave the reminder days drop down at 0 if I choose a specific date otherwise I leave the date blank and select the number of days…correct?
Hi Tonya,
It sounds like you have the usage correct. You should leave the date blank if you want a reminder in a select number of days, or leave the reminder days at 0 if you want to use the date.
I have only been using one but the email is coming through as soon as I submit the reminder
I just put this in calendar view, selected Nov 4th as the reminder date selected 0 day reminder and the email came through immediately. and the status was completed..not correct right?
This just doesnt seem to be working for me…I was trying to use to to contact surgery residents to come in 7 days prior to their start date to receive their credentials and do their orientation. The emails either do not go out at all or they come in as soon as I put in the item. Maybe I am doing something wrong but I was hoping to be able to use this as a calendar and list all residents once a year and allow sharepoint to contact each one as their date comes up.
Here is an example of what one of the workflows is telling me
Initiator: Reznor, Tonya Item: Reznor
Started: 11/3/2009 14:39 Status: In Progress
Last run: 11/3/2009 14:39
If an error occurs or this workflow stops responding, it can be terminated. Terminating the workflow will set its status to Canceled and will delete all tasks created by the workflow.
Terminate this workflow now.
Tasks
The following tasks have been assigned to the participants in this workflow. Click a task to edit it. You can also view these tasks in the list Tasks.
Assigned To
Title
Due Date
Status
Outcome
There are no items to show in this view of the “Tasks” list. To create a new item, click “New” above.
Edit in Browser /_layouts/images/icxddoc.gif /sites/Indianapolis/surgery_training/_layouts/formserver.aspx?XsnLocation={ItemUrl}&OpenIn=Browser 0×0 0×1 FileType xsn 255
Edit in Browser /_layouts/images/icxddoc.gif /sites/Indianapolis/surgery_training/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser 0×0 0×1 ProgId InfoPath.Document 255
Edit in Browser /_layouts/images/icxddoc.gif /sites/Indianapolis/surgery_training/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser 0×0 0×1 ProgId InfoPath.Document.2 255
Edit in Browser /_layouts/images/icxddoc.gif /sites/Indianapolis/surgery_training/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser 0×0 0×1 ProgId InfoPath.Document.3 255
Edit in Browser /_layouts/images/icxddoc.gif /sites/Indianapolis/surgery_training/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser 0×0 0×1 ProgId InfoPath.Document.4 255
View in Web Browser /_layouts/images/ichtmxls.gif /sites/Indianapolis/surgery_training/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&DefaultItemOpen=1 0×0 0×1 FileType xlsx 255
View in Web Browser /_layouts/images/ichtmxls.gif /sites/Indianapolis/surgery_training/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&DefaultItemOpen=1 0×0 0×1 FileType xlsb 255
Snapshot in Excel /_layouts/images/ewr134.gif /sites/Indianapolis/surgery_training/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&Snapshot=1 0×0 0×1 FileType xlsx 256
Snapshot in Excel /_layouts/images/ewr134.gif /sites/Indianapolis/surgery_training/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&Snapshot=1 0×0 0×1 FileType xlsb 256
Workflow History
View workflow reports
The following events have occurred in this workflow.
Date Occurred
Event Type
User ID
Description
Outcome
11/3/2009 14:39 Comment “Set Status to Waiting, Using Item ReminderDate”
11/3/2009 14:39 Comment “1 Day Reminder – Pausing Until:”
11/3/2009 14:39 Comment 1/1/1900 12:00:00 AM
11/3/2009 14:39 Comment System Account
Pausing for 0 minutes
Hi Tonya,
It looks like your workflow isn’t capturing the date correctly – notice in the workflow history that it is showing 1/1/1900 in the comment.
I suggest walking through the steps again looking closely at your date variables. Make sure they are capturing the input value – you might want to add a “log to history” to out put the variable when it is created and when it is populated to help determine what is going wrong.
tony
so I did everything you said in this marvelous post and I had it working fine.
Then I decided to mess with it and try to add a ‘people’ field to the email addresses.
that did not work.
so I tried to edit what I had and edit it to match exactly what you had – I even deleted step #4 but it’s still not working.
11/10/2009 5:24 PM Comment ReminderStatus Set
11/10/2009 5:24 PM Workflow Completed System Account
No Reminder Requested
…is all I get.
do I have to rebuild the whole thing from scratch?
And how can I select the users from a list?
Can I put the default as the author?
Thanks!!
It is difficult to determine what is missing or not working correctly from the details you provided. Unfortunately the best thing to do is walk through the whole workflow step-by-step to determine what isn’t working now.
You should be able to use a people picker as long as your settings for that field ensure that you are pulling the email address rather than their name/identity. Often you end up with something other than an email address and SharePoint isn’t going back out to interpret the information in that workflow variable. I would check that variable assignment.
You also can select “Created By” in your email TO or CC column is you always want the message to go to the creator/author of the calendar event.
thank you! thank you thank you! I will try your suggestions about the names.
I ended up rebuilding the whole thing, deleted the columns, the workflow and everything and that appeared to work except now my link using ‘Encoded Absolute URL’ does not encode correctly
I’m getting this as a link:
…Lists/Test%20Checklist%20Tasks/28_.000
when the actual link is opened is much more involved.
It DID work the first time I successully created the workflow… but not after I rebuilt it. Ugh.
This worked nicely for the URL:
http://www.sharepointdiy.com/articles/encoded-absolute-url-doesnt-work-with-a-list-in-sharepoint
The ReminderDate is sort of an ‘override’ date field, correct?
Will the ReminderDays be ignored completely? I’d like to change my workflow email notification to reflect that as opposed to what I have:
“You have a reminder that a task is due in [ReminderDays] day(s)”
And can I remove the EndTime column from the view? It’s a copy based off of the standard calendar view and I can’t see how to remove it. The task list is just to reflect due dates not a spanned date range.
Can this be imported/exported with Designer 2007?