Triggering the “When a Task Expires” Event in a Single-Server Dev Environment

I was recently building a 2010-based workflow in SharePoint Designer 2013 and needed to test the When a Task Expires event in a task process. I wanted to make sure my task reminders worked as expected before migrating the workflow to production.

I thought that testing this event in a single-server development environment where I had complete control would be easy, but it wasn’t, especially given the lack of good information out there (even in 2015).

When I did a Google search on this, the best information I found was a few forum threads where developers pleaded with Microsoft for information on how to do this. Microsoft’s response was to run the Workflow timer job in your farm. Sometimes they also said to run the Workflow Auto Cleanup and Expiration Policy jobs for good measure.

As it turns out, Microsoft’s answer was partially correct. Running the Workflow timer job is required, but it’s not the whole solution. And the other two jobs are irrelevant.

In this post I’ll cover:

  1. The technical details of how the When a Task Expires event actually works.
  2. How to manually trigger the event for testing in a single-server development environment.

I will NOT cover how to trigger the event in a multi-server farm because I haven’t tested that. I don’t have a non-production multi-server farm available at the moment where I can try it.

And if you’re a business user reading this, you’ll need to get someone in IT to do this for you. The approach I describe here is highly technical and requires direct server access.

How “When a Task Expires” Actually Works

Before you mess around with triggering something, I think it’s important to understand how it actually works.

Here’s what actually happens.

Step 1: The due date is assigned/updated for a task in a task process

This date change triggers the workflow to determine if a task expiration event needs to be scheduled.

For 2010 workflows, SharePoint examines the value of the OverdueRepeat setting on the task process. For 2013 workflows, it looks at the OverdueReminderRepeat setting (same setting, just slightly different name).

If that setting is turned on and if the task due date occurs at or after the current time, a task expiration event is scheduled.

Step 2: A task expiration event is scheduled

The workflow adds a record to the dbo.ScheduledWorkItems table in the content database where the workflow instance is running.

This table is essentially a queue of tasks to be executed by special timer jobs called work item timer jobs. One work item timer job – the Workflow job – is specifically designed to fetch and process work items pertaining to workflows.

If you check this table, you’ll notice that the DeliveryDate column indicates when your task expiration event will be triggered. The workflow sets this to the due date of your task initially and then updates it each time the expiration event fires (if you’ve told the task process to repeat the event more than once).

Step 3: The task expiration event is processed and triggered

The Workflow timer job processes workflow work items each time it runs. In an on-premise SharePoint 2013 environment, this job is set to run every 5 minutes by default. In Office 365, Microsoft says it runs every minute.

The timer job relies on at least three fields in the dbo.ScheduledWorkItems table when fetching work items to process:

  • Type – A GUID value of BDEADF09-C265-11D0-BCED-00A0C90AB50FI in this column indicates a workflow work item
  • BatchId – Work items with the same batch ID belong to the same workflow instance. The batch ID is a Base64/binary-encoded version of the workflow instance ID (from the dbo.Workflow table)
  • DeliveryDate – The stored procedure that fetches work items only pulls items where this value is greater than or equal to the current system time.

If your workflow has a task expiration work item where the DeliveryDate is in the past, the event will be fired, and your “When a Task Expires” section will execute.

Note than manually updating the DeliveryDate column in the SQL table (which isn’t recommended anyway) will not work. If you try that, the date will be reset automatically (when the timer job next executes) to the original date the workflow thinks it should fire.

If your task gets deleted, the workflow is canceled, or the task’s due date is reset to a date that occurs before the current system time, the work item for the task expiration event will be removed (or never created in the first place).

Unfortunately that means if you try setting your task’s due date to a past date for testing purposes, it won’t work. The “overdue” event will never fire.

How to Trigger the “When a Task Expires” Event

Triggering this event is painful, so I’ve saved you some time and packaged up all the steps in a PowerShell script.

But first, here’s an overview:

  1. Disable all your timer jobs except the Workflow job (I explain why in Step 3).
  2. Run your workflow, and make sure your task due dates are set later than the current system time (usually the next day is a safe bet).
  3. Adjust the system clock on your server to a time that’s at least as far in the future as your task due date.
    • This is why I had you disable most of your timer jobs in Step 1. Many of those jobs run every few minutes or seconds, and setting your system clock ahead will trigger than ALL to run at the same time.
  4. Manually run the Workflow timer job to process and fire the “When a Task Expires” event.
  5. Reset the system clock back to the current time.
  6. Re-enable all the timer jobs that were disabled in Step 1.

So that’s the process, and that’s why it hasn’t been summed up in a simple, quick answer in Microsoft’s forums.

But again, I’ve made this easy for you. I’ve included PowerShell code below that does everything I mentioned above.

Simply copy and paste the code below into a script file named Trigger-SPWorkflowTaskExpiration.ps1.

*** WARNINGS ***

  1. Do NOT run this on a production farm. It’s intended ONLY for single-server development farms.
  2. Do NOT run this in a multi-server farm (where SharePoint and SQL aren’t on the same box). As I said earlier, I don’t have a non-production multi-server farm available for testing at the moment, so I haven’t tested that scenario and the script doesn’t account for it.
###############################################################################
#
# Disclaimer: Like any free code you find on the Internet, use this at your own
# risk. Neither Bart McDonough nor Incline Technical Group will be held
# liable for any damage done to your environment.
#
# Usage: Trigger-SPWorkflowTaskExpiration [time-span],
#   where [time-span] is a string that can be parsed into a .NET
#   TimeSpan structure and specifies your desired adjustment to
#   the system time on the computer on which this script is run.
#
# Example: "Trigger-SPWorkflowTaskExpiration 1.00:00:00"
#   would set the system time ahead 1 day from the current time and
#   then trigger the workflow task expiration event.
#
# DO NOT RUN THIS IN A PRODUCTION ENVIRONMENT!
#
###############################################################################

# Type name of the workflow timer job that processes task expiration events
$WorkflowJobTypeName = 'Microsoft.SharePoint.Administration.SPWorkflowJobDefinition'

# Default system time adjustment is 1 day into the future
$dateAdjust = [System.TimeSpan]::FromDays(1)

# Use time adjustment from command line if supplied
if ($args -ne $null) {
   [System.TimeSpan]::TryParse($args[0], [ref]$dateAdjust)
}

$timerJobs = Get-SPTimerJob -ErrorAction Stop | where {
   $_.GetType().FullName -ne $WorkflowJobTypeName
}

Write-Output "$($timerJobs.Count) timer job(s) besides the workflow job were found in this farm."
Write-Output "Attempting to disable those jobs..."

$disabledCount = 0
$timerJobs | foreach {
   $job = $_
   $job.IsDisabled = $true
   try {
      $job.Update()
      $disabledCount++
   }
   catch {
      $job.IsDisabled = $false
   }
}

Write-Output "$($disabledCount) job(s) successfully disabled. The remaining jobs are protected and cannot be disabled."

$workflowJob = Get-SPTimerJob -Type $WorkflowJobTypeName -ErrorAction Stop
$newTime = (Get-Date).Add($dateAdjust)

Write-Output "Updating system time to $($newTime)..."

$x = Set-Date -Adjust $dateAdjust

Write-Output "Running workflow timer job..."

$lastRunTime = $workflowJob.LastRunTime
$workflowJob.RunNow()

Write-Output "Waiting for workflow job to complete..."

while ($workflowJob.LastRunTime -eq $lastRunTime) {
   $workflowJob = Get-SPTimerJob -Type $WorkflowJobTypeName
   [System.Threading.Thread]::Sleep(1000)
}

Write-Output "Resetting system time back to current time..."

$x = Set-Date -Adjust $dateAdjust.Negate()

Write-Output "Attempting to re-enable timer jobs..."

$enabledCount = 0
$timerJobs | foreach {
   if ($_.IsDisabled) {
      $_.IsDisabled = $false
      try {
         $_.Update()
         $enabledCount++
      }
      catch { }
   }
}

Write-Output "$($enabledCount) job(s) successfully re-enabled."

If you run the script with no command-line arguments, your system clock will temporarily be set one day ahead, and the Workflow timer job will be executed to trigger your task expiration events.

If you supply the optional time-span argument, whatever value you supply will be used as the system clock adjustment. The format of the time-span is a string which .NET can parse into a System.TimeSpan structure. In general, that means d.HH:mm:ss.

For example, running “Trigger-SPWorkflowTaskExpiration 2.05:30:00″ would set your system clock ahead by 2 days, 5 hours, and 30 minutes.

Conclusion

Hopefully this will help others out there who want to test task expiration events in their SharePoint Designer workflows. If you discover something new that I missed or have problems with the script, please leave a comment and let me know.

Open-Ended Date Ranges (null dates) with SharePoint queries in SSRS reports

Yesterday I was building an SSRS report in Report Builder 3.0 for a client. The report was querying data in a SharePoint 2013 site, and the client asked me to add date parameters so he could specify a date range for the data that comes back.

Seems simple enough, right? I thought I just needed to add two date parameters to my report, update my query to use them, and I’d be good to go.

Well, that was almost enough.

You see, in my case I wanted to support open-ended date ranges, which means one or both date boundaries can be null/empty. For example, the client might specify January 1, 2015, as the start date, but by leaving the end date null, the client is telling the report to pull everything after the start date. Or, as another example, leaving both dates null would return all data in the list.

The problem is SSRS doesn’t work that way. Since SSRS was originally created to query SQL data, it’s very literal in its interpretation of a null date. As far as it’s concerned, passing a null value for a date parameter means you want data where a particular date field is null. It doesn’t provide an option to interpret the null value as “I don’t care” (open-ended boundary).

The good news is I was able to get SSRS to do exactly what I wanted. If you need this same behavior, check out the YouTube video I made showing how I did it.

SharePoint OpenSearch URL Tokens

In SharePoint 2013, we can configure OpenSearch result sources for searching external providers outside of SharePoint.

The URL for an OpenSearch result source allows several replaceable tokens to be used, but so far I’ve been unable to find the complete set of tokens documented anywhere (even on MDSN).

Therefore, I’m documenting them here for reference. All of the following are valid tokens in OpenSearch provider URLs.

SharePoint OpenSearch URL Token Reference

Token Replaced With
{searchTerms} Search query (keywords)
{startIndex} Start index of first item to display, relative to total number of results

Example: If SharePoint is displaying 10 items per page, the start index on page 1 is 0, the start index on page 2 is 10, and so on.

{startItem} Same as {startIndex}
{startPage} Despite the name, behaves same as {startIndex}
{count} Number of items to display per page (setting in search results web part)
{itemsPerPage} Same as {count}
{language} Language, for example “en-US”
{inputEncoding} Encoding of search query request, for example “utf-8″
{outputEncoding} Desired encoding of query response, for example “utf-8″

While the last few tokens may not be used very much, the others are fairly important because features like paging won’t work correctly without them.

As noted above, several tokens behave the same way, so just choose the one you want in those cases. For example, you can pick {count} or {itemsPerPage} to pass the “number of results per page” to the search provider. They both mean the same thing.

More Details on Paging

For paging in particular, it’s important to know that two things are required to get it working properly in SharePoint. Almost every example I’ve seen of OpenSearch paging with SharePoint misses at least one of these, which is why so many people struggle with it.

#1: The OpenSearch Provider Needs to Accept Paging Parameters

The OpenSearch provider needs to accept parameters for paging such as “start index” and “items per page” and take those into account when searching. If the OpenSearch provider has those arguments, you can pass values for them using the tokens I listed above.

For some services you might want to query such as YouTube, you’ll have to write your own web service as a go-between because YouTube’s v3 API doesn’t support start indexes. Instead, it requires “back” and “next” tokens for paging, which are special strings it sends back with results. SharePoint can’t supply those.

#2: SharePoint Must Know Total Number of Results

The other part of paging is letting SharePoint know how many total results there are. The reason why is simple. When SharePoint receives results, it asks, “Are there more total results than what I’ve been asked to show on one page?” If the answer is yes, paging links are generated. Otherwise, they’re not.

For OpenSearch results, there’s a “totalresults” element which can be included in the XML to let SharePoint know this information. Take a look at the ATOM response example in the OpenSearch specifications for an example. Without this information, SharePoint can’t support paging correctly. You either won’t get paging at all, or you’ll get limited paging functionality that doesn’t traverse the entire result set.

Many ATOM and RSS feeds do not include the OpenSearch-specific elements such as “totalresults,” so depending which provider you’re using, you may need to create your own web service in the middle to add those additional elements.

How to Create a SharePoint 2013 Timer Job – Course Discount

I recently updated a couple of lectures in my course about how to create SharePoint 2013 timer jobs and added two brand new lectures about timer jobs for SharePoint Online (Office 365).

The next 100 people who use the coupon code NEWTIMER15 when purchasing can get the course for $49 rather than $79 (almost 40% off)!

I’m a developer myself, and I know developers generally aren’t stupid. They know how to find a lot stuff for free on the Internet. I’m aware there are lots of articles and blog posts telling you how to create timer jobs for SharePoint.

But most of those articles don’t tell you:

  • Why SPJobDefinition isn’t always the right choice as a base class
  • How to create a server-side timer job for SharePoint Online
  • How to handle error-handling, progress-reporting, and configuration in real-life scenarios

And that’s the value I bring in my course. I cover all those and WAY more. I also add and update lectures periodically, and my course – unlike many other training options – is a one-time purchase for LIFETIME ACCESS. There’s no monthly or annual subscription fee. Once you purchase, you get all new and updated lectures at NO ADDITIONAL COST.

So what are you waiting for? Only 100 people get the discount, so sign up today and start developing timer jobs for SharePoint 2013!

Missing SearchNav Property in SharePoint Client Object Model

If you’ve ever customized SharePoint 2013’s (or SharePoint Online’s) search navigation through code, you might be familiar with the SPNavigation.SearchNav property in the server-side object model. This property was introduced in SharePoint 2013 (SharePoint 2010 used a different – and now deprecated – concept called “scopes”).

The SearchNav property gives you access to the list of navigation nodes (also known as “search verticals” or “search experiences”) that show up in a SharePoint site search box as pictured below.

SPSearchMenu

That’s great for server-side code. But what about client-side code?

Well, this is where things get awkward. SharePoint’s client-side object model doesn’t include the SearchNav property on its Navigation object.

But don’t worry! You can still customize search navigation with client-side code! The trick is to use a slightly different approach.

Here’s an extension method I wrote for the Microsoft.SharePoint.Client.Navigation class that illustrates the approach:

using System;
using Microsoft.SharePoint.Client;

namespace YourNamespace
{
    public static class NavigationExtensions
    {
        const int SEARCH_PARENT_NODE_ID = 1040;

        public static NavigationNode GetSearchNavRoot(this Navigation navigation)
        {
            return navigation.GetNodeById(SEARCH_PARENT_NODE_ID);
        }
    }
}

This is actually what the server-side SearchNav property does behind-the-scenes anyway. It just fetches a special navigation node with a well-known ID of 1040. Microsoft uses that as the root/parent node for the search navigation menu.

The only difference between the code above and the implementation of the server-side SearchNav property is that the server-side property returns the children (SPNavigationNodeCollection) of the search navigation root node, whereas I just return the root node itself.

I do that because in the client-side object model, operations are batched together and sent to the server as a single request. In order to return the child nodes in my extension method like the server-side property does, I’d have to first load the child node collection into my context and then make a call to ClientContext.ExecuteQuery(). However, I think calls to ExecuteQuery() should happen outside of the extension method, and that’s why I return the root node itself.

A typical usage of the above extension method in client-side code would be something like this:

NavigationNode searchNavRoot = web.Navigation.GetSearchNavRoot();
context.load(searchNavRoot, root => root.Children);
context.ExecuteQuery();

NavigationNodeCollection searchNav = searchNavRoot.Children;

// Now, the searchNav variable above is the *exact* equivalent of
// what I get from calling SPNavigation.SearchNav in server-side
// code. I can add and remove nodes in that collection to customize
// search navigation.

So as you can see, it is possible to access and customize search navigation in client-side code.

Hopefully you found this helpful. Happy coding!

Beta version of CAML tool with intellisense is ready!

If you haven’t already read my post about the browser-based CAML testing tool with intellisense that I’ve been working on, please read that first!

If you’d like to try the tool yourself, you can download it on GitHub here: https://github.com/inclinetechnical/QuickCAML

As noted on GitHub, please use the Issues page there to submit bugs, questions, or feature requests. While I may respond to a few things here or on LinkedIn when I have time, GitHub is the primary forum for that kind of stuff.

What I’m really looking for right now is for people (mostly developers) to download the tool, test it, and report back any major issues they find (like browser compatibility problems and things like that). I also welcome suggestions for new features.

I’ll be adding more updates on the tool in coming weeks, including putting out a tutorial on how to use all the query options.

Extension method to safely get list item field value in SharePoint 2013

For SharePoint 2013, here’s an extension method I wrote for SPListItem that’s turned out to be quite useful. It gets the value of a list item field in a “safe” fashion – that is, by returning a known default value if the field value is null or can’t be converted to the type you expect.

public static T GetValue<T>(this SPListItem item, string fieldName, T defaultValue = default(T))
{
    if (item == null)
        throw new ArgumentNullException("item");

    T value = defaultValue;
    object rawValue = item[fieldName];

    try
    {
        if (rawValue != null)
        {
            value = (T)rawValue;
        }
    }
    catch
    {
        try
        {
            // Direct cast failed, but try a type conversion just in case
            // we can still convert the raw value correctly (for example,
            // if the raw value is the string "true" and T is a boolean,
            // this allows us to correctly return a true boolean value).
            value = (T)Convert.ChangeType(rawValue, typeof(T));
        }
        catch { }
    }

    return value;
}

To use this, you need to put it inside a static class (like any other C# extension method) and add a using statement for Microsoft.SharePoint.

The defaultValue parameter is optional and specifies the default value to return. You can either pass in your own value or let C# determine it using the default keyword. The default keyword returns null for reference types, 0 for numeric types, and false for boolean types. For structs, it returns a new struct with each member initialized as described previously.

Because this is implemented using the standard SPListItem.Item[string] indexer, the behavior for finding fields by name is as follows:

  • First, SharePoint treats fieldName as the internal name of a field
  • If no match, SharePoint treats fieldName as the display name (title) of a field
  • If still no match, SharePoint treats fieldName as the static name of a field
  • If none of the above result in a match, an exception is thrown saying the field cannot be found

So as Microsoft says, the best performance comes when you pass fieldName as the internal name of a field because only the first check is done and the others are skipped.

Examples of usage:

// gets the integer value of "MyIntegerField" or -1 (programmer-specified default)
int value = listItem.GetValue("MyIntegerField", -1);

// gets the boolean value of "MyBooleanField" or false (C#-defined default)
bool value = listItem.GetValue("MyBooleanField");

// gets the lookup value of "MyLookupField" or null (C#-defined default)
SPFieldLookupValue value = listItem.GetValue("MyLookupField");

SharePoint 2010 compatibility: This extension won’t work as-is with SharePoint 2010 because of the optional defaultValue parameter. SharePoint 2010 uses version 3.5 of the .NET framework, and optional parameters are only supported in .NET 4.0 and above. For SharePoint 2010, I’d recommend overloading this method where one signature takes the optional parameter and one does not.