Tech tutorials Repeating Event Reminder Notifications

By Insight Editor / 25 Nov 2015 , Updated on 16 May 2019

Using the Call HTTP Web Service action in a looping SharePoint 2013 Site Workflow

Notifications sent out ahead of events can help encourage attendance and prevent scheduling conflicts. The approach detailed below can be used to issue 30-day, 15-day and three-day reminder notifications before an event in a typical SharePoint 2013 event calendar.

Why use a site workflow?

A notification workflow could be attached to the calendar list and triggered when the event is added. Each event would have its own instance of the workflow running.

This can work well enough for small events or short-term solutions, but a large calendar list packed with hundreds of events would create hundreds of instances that could be running for months. This is a vulnerable situation, where a downtime period or workflow upgrade could impact hundreds of items.

Downtimes will occur, so it's important to understand what happens to every workflow if it fails. For example, if the list workflow used the "Pause Until…" action, and a downtime period blanketed a day or longer, the instances would never advance, missing all of the items for that day. The list workflow would need to be adjusted to process the missing time as an exception and instances restarted on all of the list items.

In contrast, a single site workflow can loop through the calendar items once a day, even to multiple calendars, looking for those events that require notification. If the system has a downtime period, only one workflow needs to restart, and workflow fixes to address any missing back dates are much easier.

In vulnerable situations where many list workflow instances are paused for long periods of time, consider if a single site workflow might be a more stable alternative.

Basic outline

Stage: First run pause Sending the notifications on off-hours is best, so we build in a delay for the first round until 11:50 p.m.
Stage: Main loop
1. Step 1: Set time variables. Use the current DateTime to calculate useful variables for the three-day, 15-day and 30-day notices.
2. Step 2: Get events. Use the time variables to build a filter for an HTTP Web Service call to gather related events.
3. Step 3: Loop — send notices.
  1. Pull title, EventDate and attendees data for each returned item.
  2. Use another Web Service Call to get the attendees’ contact information.
  3. Send notices.
Stage: Pause until tomorrow Pause until tomorrow and loop back to the start of the main loop stage.

About the Call HTTP Web Service action

A new action is available in SharePoint 2013 Designer workflows that allows the use of web services. SharePoint 2013 also has a new web services API in the _api folder.

The most obvious indication if you’re using the old or new web services is the location to where the calls go:

Calls location

For the first HTTP Web Service Call in our workflow, we want to gather all of the events that need three-day, 15-day or 30-day notices. The call to get items from our event calendar list looks something like this:

{site URL}/_api/web/lists/getByTitle('Event Calendar')/Items?

Specify that the data is returned as JSON (instead of Atom) by adding the $format querystring parameter after the ?:

$format=json

Limit which columns are returned with the $select parameter:

$select=Title,EventDate,ParticipantsPickerId

And use the $filter parameter to specify which items are returned:

$filter=(conditions)

Altogether, this will produce an HTTP Web Service Call similar to this:

https://cardinalsolutions-my.sharepoint.com/personal/mrogers_cardinalsolutions_com/ _api/web/lists/getByTitle('Event Calendar')/Items?$format=json) &$select=Title,EventDate,ParticipantsPickerId&$filter=((EventDate gt '12/3/2015') and (EventDate lt '12/5/2015')

$filter approaches

DateTime variables present a special challenge in a $filter string because the time portion of the field makes testing equality (=) difficult. Event notifications in this workflow only deal with the date portion, so the $filter must somehow gather all of the calendar events with DateTime field that match only the date.

There are two general approaches to this.

Constraining date approach

The $filter is built to test if a DateTime is between two other dates:

$filter=((EventDate gt '12/3/2015') and (EventDate lt '12/5/2015'))

Construct date approach

The $filter is built to break apart the EventDate field into day, month and year:

$filter=((day(EventDate) eq '3') and (month(EventDate) eq '11') and (year(EventDate) eq '2015'))

Both approaches are similar in length. We've used the confining dates approach and tested for the three notifications in a filter with a nested filter. (Green dates are calculated by the workflow and written into the $filter string before we use it in the HTTP Web Service Call action.)

$filter=(((EventDate gt '11/6/2015') and (EventDate lt '11/8/2015')) or ((EventDate gt '11/18/2015') and (EventDate lt '11/20/2015')) or ((EventDate gt '12/3/2015') and (EventDate lt '12/5/2015')))

Get the current time.

SharePoint Designer workflows have no specific function to get the current DateTime. List workflows can read the timestamp of when it was started, but this value isn't useful when the workflow delays or loops — and a SharePoint 2013 site workflow doesn't offer this timestamp.

SharePoint 2010 site workflows do, however, provide the datetime the workflow was initialized. Additionally, SharePoint 2010 site workflow can be used as a function called from the main SharePoint 2013 looping workflow. Therefore, all the 2010 site workflow needs to do is put that datetime into a variable:

The 2013 workflow can now call the 2010 workflow and obtain the varNow variable anew:

Workflow listing

(See comments below image.)

Workflow comments

First run pause stage

This stage is used to delay the workflow after it’s first started until 11:50 p.m. that night.

TIP: The stage name shows up in the workflow history and in the list columns for the workflow (in list workflows) and is helpful in following an item’s behavior. By boxing certain phases of the workflow in a stage, such as delay periods, you can understand at a glance where the workflow stands.

GetCurrentTime 2010 workflow

The very first line in the workflow is a call to a SharePoint 2010 site workflow, which simply records the time it was triggered into a variable. This DateTime value is read in the second line and assigned to varNow.

Stage as a loop

The new stages in SharePoint 2013 Designer workflows include a section labeled "Transition to stage," which allows the flow to be directed to other stages, much like a GOTO statement. In this simple example, the Pause until tomorrow stage at the end of the workflow delays for a time and then loops back to the Main loop stage until the workflow is terminated.

Setting time variables

At the beginning of the loop, the SharePoint 2010 workflow GetCurrentTime is called again and, from the datetime returned, a series of variables is created. This will occur in every loop hereafter, getting fresh values each day.

These variables specify the main three-day, 15-day and 30-day notices and the bounding dates on either side of the notice dates, which will be used in the HTTP Web Service Call $filter.

Building the $filter and the HTTP Web Service Call

The varFilterString variable builds a $filter string that will be used to specify which events to collect. As described above, it uses the time variables to specify ranges for the desired dates to fall between:

$filter=(((EventDate gt '11/6/2015') and (EventDate lt '11/8/2015')) or ((EventDate gt '11/18/2015') and (EventDate lt '11/20/2015')) or ((EventDate gt '12/3/2015') and (EventDate lt '12/5/2015')))

The web service call requires a custom header. We build this as a dictionary in the first line of the Get events step with the following values:

And then set the properties of the HTTP Web Service Call to use that as the RequestHeader:

Setting properties of the HTTP Web Service Call

Parsing the HTTP Web Service Call Results

If the call was set up to receive JSON, the results will look similar to this (formatted for visibility):

{"d":

{"results":

[

{"__metadata":{"id":"ee7af8d2-6441-47de-a194-27be493aafda","uri":"https:\/\/cardinalsolutions-my.sharepoint.com\/personal\/mrogers_cardinalsolutions_com\/_api\/Web\/Lists(guid'943a8e56-4f44-4c47-a12b-aea27f98783b')\/Items(1)","etag":"\"3\"","type":"SP.Data.EventCalendarListItem"},

"Title":"First place",

"EventDate":"2015-12-04T20:00:00Z",

"ParticipantsPickerId":{"__metadata":{"type":"Collection(Edm.Int32)"},"results":[1]},"},

{"__metadata":{"id":"d7d3f935-401d-4423-87eb-248658505bc2","uri":"https:\/\/cardinalsolutions-my.sharepoint.com\/personal\/mrogers_cardinalsolutions_com\/_api\/Web\/Lists(guid'943a8e56-4f44-4c47-a12b-aea27f98783b')\/Items(2)","etag":"\"2\"","type":"SP.Data.EventCalendarListItem"},

"Title":"Second Place",

"EventDate":"2015-11-19T21:00:00Z",

"ParticipantsPickerId":{"__metadata":{"type":"Collection(Edm.Int32)"},"results":[1,15,32]},"}

]

}

This result is a dictionary, meaning it has {key : value} pairs that the SharePoint Designer "Get" operation can use to extract information.

Getting d/results returns the red brackets [] and the two event items inside another dictionary (itemsRet). The number of items in the dictionary is counted, and a loop (Send notices) runs that many times, using an index counter to Get (index) from the second dictionary and place it into a third dictionary: varSingleItem.

A single event item with select fields as a dictionary

"Title":"Second Place",

"EventDate":"2015-11-19T21:00:00Z",

"ParticipantsPickerId":{"__metadata":{"type":"Collection(Edm.Int32)"},"results":[1,15,32]},"}

Gets are used again on varSingleItem to extract the title, event date and attendees collection (ParticipantsPickerId).

Because the ParticipantsPickerId results value is a collection of integers representing users to that particular SharePoint site, this collection will need iterated and the values looked up later using another HTTP Web Service Call in a loop (Gather participant email addresses).

"ParticipantsPickerId":{

"__metadata":{"type":"Collection(Edm.Int32)"},

"results":[1,15,32]

},"

Using non-integers as a counter

When setting up variables as counters in a loop, use number types instead of integers because incrementing the variable can be done in one step:

The reason for this is the Calculate action cannot output an integer type, so incrementing would take two variables and two steps.

getuserbyid web service call

Attendees are shown in the event results returned in the ParticipantsPickerId, which is a collection of integers that refer to the users in the SharePoint site. In order to get contact information for the notifications, we must use another HTTP Web Service Call to the getuserbyid web service in that site, passing in the integer for the ParticipantId.

[%Workflow Context:Current Site URL%]_api/web/getuserbyid([%Variable: varParticipantId%])?$format=json

In the Gather participant email addresses loop, the ParticipantsPickerId collection is stepped through, gathering email addresses that are then concatenated to send the same notification to all attendees when the loop is complete.