Finding SharePoint tasks that are assigned to groups you’re in

This question has been posted multiple times around the web, and I didn’t really find a good way to handle it, so here goes.

Problem

You want your users to take advantage of the built-in task lists of SharePoint. But from BPM training and painful experience, you remember that assigning tasks to individual users is not a great idea. When users change function or leave the company, you are left with tasks that are assigned to no-one, leaving the radar.

So instead, you want to be able to assign tasks to SharePoint groups. And of course, you want to have a summary of the active tasks that are assigned to you, wherever those tasks are in your SharePoint farm (on premise) or tenant (Online).

The simple (local) workaround

This actually doesn’t solve the problem completely, but is an interesting local workaround. It has been documented all around the internet, so I’ll just give you the briefs.

This only works for a single task list, where you provision a View that shows all tasks that are directly assigned to me OR that are assigned to a group I’m in, translated into a SharePoint View CAML Query:

<Or>
    <Membership Type="CurrentUserGroups">
        <FieldRef Name="AssignedTo"/>
    </Membership>
    <Eq>
        <FieldRef Name="AssignedTo"/>
        <Value Type="Integer">
            <UserID Type="Integer"/>
        </Value>
    </Eq>
</Or>

This cannot be done in the SharePoint UI, only the “FieldRef” bit can be set using the UI. The “Membership” bit needs to configured directly on the page that contains your webpart.

This will work fine for a single task list or task lists on the same site, but as it uses CAML queries it cannot be used cross-site collection for example.

The tougher (farm/tenant) solution

Ok, so we have more than 1 tasklist and want to assemble all of the information on one page, the homepage dashboard for example.

SharePoint Search is your friend this time. We’re simply compiling a search query that shows all Active tasks that are assigned to you. This can easily be done in JSOM:

var keywordQuery = new Microsoft.SharePoint.Client.Search.Query.KeywordQuery(clientContext);
keywordQuery.set_queryText('contentclass:STS_ListItem_Task AND AssignedTo:"'
  + username + '" AND StatusOWSCHCS<>"Completed"');
keywordQuery.set_collapseSpecification("ListID");
keywordQuery.set_trimDuplicates(false);
var selectProperties = keywordQuery.get_selectProperties();

Note that we’ve also thrown in the CollapseSpecification. On the homepage, we don’t want a link to every single task. I just want to know what the total number of tasks is, and a link to each task list that contains tasks for me. Also, we’ll not be trimming duplicates, as in my specific case, tasks are very alike so I want to make sure I get the correct total task count.

This however, still doesn’t fix the issue yet, as we’re still only retrieving items that are assigned to me directly.

For the next step, you’ll have to provision a timer job, scheduled task or remote event receiver. This is my secret sauce, you may not like it, but it covers the end users’ requirements, is maintainable and is loosely coupled from SharePoint.

The (no longer so) secret sauce

The idea is that every time a task is created, an extra field is added and filled in with the complete list of actual users that are in those groups. That way, the field can be queried using Search to find tasks either assigned to me directly or assigned to me through a group. The Search Query now looks like this:

var keywordQuery = new Microsoft.SharePoint.Client.Search.Query.KeywordQuery(clientContext);
keywordQuery.set_queryText('contentclass:STS_ListItem_Task AND (ExpandedUsersOWSUSER:"'
  + username + '" OR AssignedTo:"' + username + '") AND StatusOWSCHCS<>"Completed"');
keywordQuery.set_collapseSpecification("ListID");
keywordQuery.set_trimDuplicates(false);
var selectProperties = keywordQuery.get_selectProperties();

The field is called “ExpandedUsers” and contains the list of all assigned groups’ users per task. It is a multi-valued people-picker field that we’ll populate with the actual users in the group.

In the next section, I’ll talk about what choices you have to automate this, but we’ll need to automate this process nonetheless. This does provide an additional challenge: the “ExpandedUsers” field doesn’t exist anywhere, so we’ll need to provision that field in every site collection and for every task list as well. Ideally we would need to set the field to “Hidden”, but that way the Search crawler doesn’t pick up on the field’s value, so we’re leaving it visible.

The batch job modality

There are several ways to do the batch job:

  • Timer job: only possible with on premise SharePoint
  • Event Receiver: only possible with on premise SharePoint
  • Scheduled Windows Task using CSOM: works for both on premise and Online, is the modern counterpart of the timer job
  • Remote event receiver: works for both on premise and Online, is the modern counterpart of the event receiver

The timer job or scheduled tasks have the disadvantage of the delay between updates. If a new task is created, it will not appear on the homepage dashboard until the job is run and while running, it will also cover older tasks and hence will use more time than with event-based approaches.

The event-based approaches have the downside that if for some reason anything goes wrong with one task, it may never appear on the dashboard. This approach is less resilient to failure.

The worst downside for the event-based approach is that if group membership changes, the task list item will not be updated. If your groups are rather stable, this will be fine, but if they change once in a while, tasks may still get orphaned, not fulfilling the basic requirement of using groups for tasks.

The algorithm

What the batch job needs to do is not rocket science, but I’ll post some pseudo-code just in case.

Find all task lists in your farm/tenant (using Search)

This is required so that you can create your CSOM context.

For each task list
  Open the SharePoint Context
  Get the Web's groups
  If the Web doesn't contain the field, create it (using XML)
  If the List doesn't contain the field, add it
  For each task in the list
    Get the AssignedTo property
    If it is a group, get the group members
    If the user collection has changed, update the task list item

 

And that’s it really. It will take a few hours to develop the batch job, but if you’re used to CSOM, you’ll manage 🙂

Downsides

  • When you have alerts configured for the task list on item update, they will be triggered for each task. You cannot use SPList.SystemUpdate in CSOM, but the server side code does allow it. But as I’m a strong advocate for using SharePoint Online, I’m not elaborating on server-side code anymore.
  • You need to document these kind of timer jobs or event receivers rigorously so that they don’t get forgotten in the next update round (mostly applicable for on premise).
  • If you have many users in your SharePoint groups, we’re adding quite a lot of bulk to the individual task list items. This will have an implication on storage requirements. If you have groups with over 100 people and over 1000 tasks assigned to those kind of groups, remember that every one of those 1000 task will get 100 people in the multi-valued people picker field.
  • See the note on the event receiver downside.
  • The extra field ExpandedUsers cannot be Hidden, so users will see and be able to edit this field.

Questions? Hit the comments!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s