I want to create a workflow based on a SharePoint Calendar
I have created a Calendar and want to send an email out x number of days before the event is due to take place but not 100% on what I need to do, so if anyone has any idea's or solutions on how to do this that would be great.
I think the easiest way to do this would be to have a hidden "notificationDate" field. When an item is created or modified, have a workflow that sets the notificationDate to [eventDate - 4days]. You could also probably do this with a calculated column. Then, have a site workflow that runs daily and queries your calendar list for items where [notificationDate == today]. You can then have a forEach loop that will iterate through the items that meet your requirements and "do something." That something can be starting a list workflow that does the notification, or just doing the notification within the loop.
Let me know if this doesn't make sense or you have more questions!
Ok! Let's see what we can whip up. I made a new calendar list called "nintexCalendar." I made a column called "notificationDate" and then I created a workflow on this list that would run on New Item and on Modified and would set the notificationDate to Start Date minus 4. Unfortunately, I couldn't use a calculated column because I can't access that value in the workflow later. That workflow is simple though, here is what it looks like (I like to use lots of logs in my workflows for troubleshooting, you can omit those!):
Here's the details for subtracting the days to create the varNotificationDate variable:
Here's the details for the setting the time action (we do this so that we can check later if "todaysDate" == "thisDate"; if the times don't match, the logic will evaluate to false):
Then we just update the list item with our new value:
Make sure this workflow runs on New Item and Modified! That way if someone changes their start date later, the workflow still works. I created a few items to make sure my workflow worked:
Then I created a site workflow, by going to Site Contents and clicking on Nintex Workflow for Office 365, then clicking "Create Site Workflow." Here's what that workflow looks like:
First, I use the "set time portion of date/time field" to create a variable called "varTodaysDate" with a time of 12:00:00 for today's date
Then, I query my nintexCalendar list for items where the notificationDate is today!
I like to get a result count so I know how many items the query returned. This is for debugging, I log it. the varCollIDs is a collection variable that stores a collection of all the item IDs the query returned. Now I need to loop through each of those IDs and do something to the item (send an email) so in comes my For Each loop:
for each of the varCollIDs I want to output the varIndividID, this basically gives me the ID of the item I'm working on. So if my collIDs had like, 3, 7, 9, then the first look the individID would be 3, the next time 7, the final time 9. Then I just have my send an email action! To get the "start time" and event title, you have to do the advanced lookup, formatted like so:
Basically we're telling it to go to the list "nintexCalendar" and get the title of the item whose ID matches our "varIndividID". Once you click insert you get this:
And voila! Let me know if you have more questions
Once you do a test run and confirm the workflow works, you can schedule it, by clicking on the ellipses and then "Schedule" in the workflow builder:
Then you can set it to run daily at like, 8am and every day at 8am the email will be generated to whomever to let them know about the event in four days.
Hi Claire Allen,
1. In your list you have event date column (which is your date and time column type). Add action to calculate date, in days field subtract # of days when you want to send reminder
2. Use "Pause Until..." action and use the date you calculated from step 1.
3. Configure "Send Notification" action
Also, if you want to send multiple times then you can use While loop for counter in descending order and use that variable value to deduct days in calculate date. For e.g. 4 reminders then you do 4, 3, 2 and 1. So basically above logic will be inside a while loop.
Hope this will help.
No harm no foul! I'm open to other ideas I will say that I don't like the "Pause Until" as a general rule because then you just have workflows out there suspended/waiting for weeks or months. That's typically not best practice and if the workflow hangs up for any reason OR someone changes their event date, your workflow breaks.
My workflow(s) aren't really that many actions, the logs make it look longer than it is for the most part. I wouldn't want to have the first workflow (that calculates the reminder date) because a calculated column does it just fine, but the problem is that the workflow can't use/access that calculated value. You could possibly get around this with a calculated value on your form that writes to a column, but that seems like more work than a simple "calculation" workflow.
Either way, I hope Claire finds an adequate solution!