Smarter ideas worth writing about.

Repeating Event Reminder Notifications

Using the Call HTTP Web Services Action
in a looping SharePoint 2013 Site Workflow 

Intro

Notifications sent out before events can help encourage attendance and prevent scheduling conflicts. 

The approach detailed below can be used to issue 30-day, 15-day and 3-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 happen, 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 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 restarted, and workflow fixes to address any missing backdates 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 PM. 
  • Stage: Main Loop 
    1. Step: Set Time Variables
      Use the current DateTime to calculate useful variables for the 3-day, 15-day and 30-day notices.
       
    2. Step: Get Events
      Use the Time variables to build a filter for an HTTP Web Services Call to gather related events.
       
    3. Loop: Send Notices
      Pull Title, EventDate and Attendees data for each returned item.
      Use another Web Services Call to get the Attendees contact information.
      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 allow the use of web services. SharePoint 2013 also has a new web services API, in the _api folder. 

(see: https://msdn.microsoft.com/en-us/library/office/fp142380.aspx

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

 OLD  {Site URL}  /_vti_bin/listdata.svc
   ?querystring
 NEW {Site URL}
/_api/ Routing View Folders  ?querystring

For the first HTTP Web Service Call in our workflow, we want to gather all the Events that need 3-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? 

(see: http://zoom.it/Z9oC)

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) 

All together, this will produce a 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')
 

(more: https://msdn.microsoft.com/en-us/library/office/fp142385.aspx) 

$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 the calendar events with DateTime field that match just the date. 

There are two general approaches to this. 

Constraining Date Approach 
The $filter is build 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 3 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, and SP 2010 Site Workflow can be used as a function called from the main SP 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 obtains 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 is first started until 11:50 PM 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 SP 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 3, 15 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: 

Name  Type Value
Accept String application/json;odata=verbose
Content Type String application/json;odata=verbose

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

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 SP Designer's "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.

{"__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]},"}


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 "Loop: Gather Participant Email Addresses", the ParticipantsPickerId collection is stepped through, gathering email addresses which are then concatenated to send the same notification to all attendees when the loop is complete. 

(see: http://zoom.it/OLUf) 

Helpful Links 


Get to know the SharePoint 2013 REST service
https://msdn.microsoft.com/en-us/library/office/fp142380.aspx 

SharePoint 2013 REST Service syntax diagram for lists and items
http://zoom.it/Z9oC 

SharePoint 2013 REST Service syntax diagram for users and group
http://zoom.it/OLUf 

Use OData query operations in SharePoint REST request
https://msdn.microsoft.com/en-us/library/office/fp142385.aspx 




Share:

About The Author

Matt has been building web sites since 1997, participating in all roles. In the past few years his focus has been SharePoint development.