Summary Web part for all Tasks Assigned to the Current User

This post walks through a roll-up web part that summarises all Active tasks, Assigned To the current user, from across the entire SharePoint Online tenant, or On-Prem farm.

The below solution has been documented for a SharePoint Online tenant, but it could easily be used for an On-Prem install if required. Additionally, this could also be adapted to a SharePoint-Hosted App as an App Part, but for this blog I have just stuck to the simplest form to demonstrate the code.

THE SCENARIO:

• SharePoint Online/Office365
• Create a web part to return all non-completed tasks, Assigned To the current user, from all Task lists, from all site collections in the tenant
• Present these tasks in a rolled up summary table with the COUNT of items per task list displayed
• And link to the list if the user wants to see their Assigned To items

THE FINISHED PRODUCT:

If this looks like what you're trying to achieve, then read on!

THE SOLUTION:

I implemented the solution using SharePoint's Content Search Web Part (CSWP) and a custom Display Template.

Step 1: Create the custom Display Template

OK, I am not going to go into the inner workings of the Content Search Web Part. It is a fantastic feature in 2013 and there is plenty of great stuff out there to get you started. Like these pages here and here.

Note, just in case I have missed anything, you can download working versions of this code from my GitHub Gist.

So let's go!

The Control File

Generally the Control File doesn't do a lot of heavy lifting. It just renders the headers of the table and leaves the rest of the logic to the item file.
But in this case, we are going to iterate through the entire result set returned by search, group these results into site and list groupings, then send this custom result set to the Item file for processing.

• First, take a copy of one of the existing Control Templates: Control_List.html (https://site/_catalogs/masterpage/Display Templates/Content Web Parts/Control_List.html)
• Set the Managed Property Mapping:

<mso:ManagedPropertyMapping msdt:dt="string">&#39;Link URL&#39;{Link URL}:&#39;Path&#39;,&#39;Task Title&#39;{Task Title}:&#39;Title&#39;,&#39;SecondaryFileExtension&#39;,&#39;ContentTypeId&#39;,&#39;ListItemID&#39;,&#39;SiteTitle&#39;,&#39;SitePath&#39;,&#39;Site&#39;,&#39;SPSiteURL&#39;,&#39;ListUrl&#39;</mso:ManagedPropertyMapping> 

• Then, add the required reference to the CSS file that we'll fill out below:

    <script>
        $includeLanguageScript(this.url, "~sitecollection/_catalogs/masterpage/Display Templates/Language Files/{Locale}/CustomStrings.js");
        $includeCSS(this.url, "~sitecollection/Style Library/css/custom.css");
    </script>

• Include the following in the JavaScipt:
  var encodedID = $htmlEncode(ctx.ClientControl.get_nextUniqueId() + "_Table_");
• replace everything after var noResultsClassName = "ms-srch-result-noResults"; with

 _#-->  
           <table class="dataTable" id="_#= encodedID =#_">  
             <thead>  
               <tr>  
                 <th>Site Name</th>  
                 <th>List Name</th>  
                 <th align="center">Assigned to me</th>  
               </tr>  
             </thead>  
             <tbody>  
                       <!--#_  
                var encodedId = $htmlEncode(ctx.ClientControl.get_nextUniqueId() + "_row_");  
                var linkId = encodedId + "link";  
                var titleId = encodedId + "title";  
                var siteId = encodedId + "site";   
                var listData = ctx.ListData;  
                var resultcount = listData.ResultTables[0].ResultRows.length;  
                var resultArray = [];  
                resultArray = listData.ResultTables[0].ResultRows;  
                var other = {},  
                     i;  
                for (i=0; i < resultArray.length; i++) {  
                     ln = ctx.ListData.ResultTables[0].ResultRows[i].Path.toString().toLowerCase().search("/dispform.aspx");  
                     listURL = ctx.ListData.ResultTables[0].ResultRows[i].Path.toString().substring(0,ln)  
                  // if other doesn't already have a property for the current letter  
                  // create it and assign it to a new empty array  
                  if (!(listURL in other))  
                   other[listURL] = [];  
                     // send the object to the Other array:  
                  other[listURL].push(resultArray[i]);  
                }  
                for(var key in other){  
                     var taskcount = other[key].length;  
                     var len = other[key][0].Path.toString().toLowerCase().search("/dispform.aspx");  
                     var listURL = other[key][0].Path.toString().substring(0,len)  
                     var siteTitle = other[key][0].SiteTitle;  
                  var len2 = listURL.toString().lastIndexOf("/") + 1;  
                  var listTitle = listURL.toString().substring(len2,listURL.length);  
                     ms_outHtml.push(''  
                                         ,'  '         
                                         ,'          <tr class="border_bottom ms-itmHoverEnabled">'  
                                         ,'         <td>', siteTitle, '</td>'  
                                         ,'         <td><a href="', listURL ,'">', listTitle ,'</a></td>'  
                                         ,'         <td align="center">', taskcount ,'</td>'  
                                         ,'          </tr>'  
                                         ,''  
                                         );  
                     }  
                _#-->  
             </tbody>  
           </table>  
   </div>  
 </body>  
 </html>  

The Item File

• Again, start with a copy of the following template: Item_TwoLines.html
• Replace everything after, and including <div id="TwoLines"> with:

  <div id="Item_TaskDataRow">  
           <!--#_  
                var encodedId = $htmlEncode(ctx.ClientControl.get_nextUniqueId() + "_row_");  
                var siteTitle = $getItemValue(ctx, "SiteTitle");  
                var siteURL = $getItemValue(ctx, "SPSiteURL");  
                var itemPath = $getItemValue(ctx, "Path");  
                var n1 = itemPath.toString().toLowerCase().search("/dispform.aspx");  
                var listURL = itemPath.toString().substring(0,n1);  
             var n2 = listURL.toString().lastIndexOf("/") + 1;  
             var listTitle = listURL.toString().substring(n2,listURL.length);  
                var linkId = encodedId + "link";  
                var titleId = encodedId + "title";  
                var siteId = encodedId + "site";   
                var listData = ctx.ListData;  
                var resultcount = listData.ResultTables[0].ResultRows.length;  
                var resultArray = [];  
                resultArray = listData.ResultTables[0].ResultRows;  
                debugger;  
                var other = {},  
                     letter,  
                     i;  
                for (i=0; i < resultArray.length; i++) {  
                     ln = ctx.ListData.ResultTables[0].ResultRows[i].SitePath.toString().toLowerCase().search("/dispform.aspx");  
                     listURL = ctx.ListData.ResultTables[0].ResultRows[i].SitePath.toString().substring(0,ln)  
                  // if other doesn't already have a property for the current letter  
                  // create it and assign it to a new empty array  
                  if (!(listURL in other))  
                   other[listURL] = [];  
                       other[listURL].push(resultArray[i]);  
                }  
                for(var propt in other){  
                  console.log(propt + ': ' + other[propt].length);  
                }  
           debugger;  
           _#-->  
     <tr id="_#= encodedId =#_" class="border_bottom ms-itmHoverEnabled" data-listitemid="_#= ctx.CurrentItem.ListItemID =#_">  
       <td>_#= siteTitle =#_</td>  
       <td><a href="_#= listURL =#_">_#= listTitle =#_</a></td>  
                <td align="center">_#= resultcount =#_</td>  
     </tr>  
   </div>  
 </body>  
 </html>  

Step 2: Add a CSS file:

I cheated a little bit here and just borrowed some nice table CSS from the very useful DataTables JQuery plug-in.

First, ensure the Control File references this "custom css" file. See above for details.

Within this custom.css file, include the following classes:

 /* Table styles */  
 table.dataTable {  
      width: 100%;  
      margin: 0 auto;  
      clear: both;  
      border-collapse: separate;  
      border-spacing: 0;  
 }  
  /* Header and footer styles: */  
 table.dataTable thead th,  
 table.dataTable tfoot th {  
      font-weight: bold;  
 }  
 table.dataTable thead th,  
 table.dataTable thead td {  
      padding: 8px 10px;  
      border-bottom: 1px solid #111111;  
 }  
  /* Body styles: */  
 table.dataTable tbody th,  
 table.dataTable tbody td {  
      padding: 8px 10px;  
 }  
 table.dataTable th.center,  
 table.dataTable td.center,  
 table.dataTable td.dataTables_empty {  
      text-align: center;  
 }  
 table.dataTable th.right,  
 table.dataTable td.right {  
      text-align: right;  
 }  

Again, in case I missed anything, you can download working versions of this code from GitHub.

Step 3: Add the Content Search Web Part to your page and configure it

Add the Content Search Web Part to your page: Edit Page > Insert > Web Part > Content Rollup > Content Search (just so we're clear we're talking about the same thing)

and configure with the following properties:

Change Query

In Advanced Mode, update the Search Query to read:

ContentTypeId:0x0108* (IsDocument:"True" OR contentclass:"STS_ListItem") AssignedTo={User.Name} PercentCompleteOWSNMBR<>1

Basically what this is saying is: get all items (IsDocument:"True" OR contentclass:"STS_ListItem") of Content Type Task (ContentTypeId:0x0108), and all child content types (*), that are assigned to the current user (AssignedTo={User.Name}) and that are NOT 100% Complete (PercentCompleteOWSNMBR<>1).
Note that no 'path' variable is defined, so it will return content from all Site Collections on the tenant (that the current user has permissions to). This could be updated to include just the current site collection if that was your requirement. Just add:  path:{SiteCollection.URL}

Another way to meet this requirement, without the need to include your filtering logic in each web part, would be to create a custom Result Source in the Search Administration and refer to that

Assign the Display Templates

Under the Display Templates properties, select the respective Display Templates that we created above:

THE CONCLUSION

Thanks for reading this blog! I assume if you got this far that the content has been of interest to you. As mentioned, what you see in this post isn't complete, so you may to look at GitHub for the full, working model.
Hope this has been of assistance!

Creating a Reminder Workflow on a standard Task List in SharePoint 2013 / SharePoint Online

SharePoint Task lists are one of the great strengths of SharePoint Team Sites. Having a central list to keep track of and assign tasks to people is great for productivity.
But what about when people ignore the list their tasks within it?
In this blog I am going to create a fairly simple SharePoint Designer List Workflow that each day will check whether a task is Overdue AND not Complete, and send a reminder email to the person who is in the Assigned To field.
This will work for a standard Task list. I have created it in Office365 environment, but this will also work on your On-Prem SharePoint 2013 install.
So here goes…

Step 1: Create the list Workflow in SharePoint Designer

  

Step 2: Set up the Stages.

In this workflow, we require two stages: Running, and Finished.

So go ahead and create them using the "Stage" dropdown:

Step 3: Set the conditions

This example uses lots of conditions to check whether it should send the email so you’ll need to insert a 4 conditions within the Running stage.

1. If any value equals value

2. If any value equals value

3. Person is a Valid SharePoint User

4. If any value equals value

So your blank conditions will look like this:

Condition 1: Current Item:Due Date is less than Current date

Condition 2: Current Item:Due Date is greater than 01/01/2000 (this accounts for instances where Due Date is left blank)

Condition 3: Current Item: Assigned to is a valid SharePoint user

Condition 4: Current Item: Task Status not equals Completed

So all of the Conditions together should look like this:

Step 4: Set the Actions

So, what actions to perform if the above evaluates to True?

1. Find Interval Between dates

2. Find Substring in String

3. Extract Substring from start of string

4. Send an email

5. Log to History list

Once you have added these blank actions to your workflow, it will look like this:

Action 1: Find days between Current Item:Due Date and Today (output to Variable:DaysOverdue)

Action 2: Find <space> in Current Item:Assigned To (Output to Variable:fnameLength)
This action will get the number of letters in the first name. Assumes that the Assigned To Display name looks like "John Citizen".

Action 3: Copy Variable:fnameLength (the number of characters in the first name) from the Current Item: Assigned To (Output to Variable:DisplayFirstName).
Output to the DisplayFirstName variable should be "John".

Action 4: Send the email (details below)

Action 5: Log "Sent email reminder" to the workflow history list.

So the finished product of Actions will look like this:

Create the Email

Step 5: The Finished Product:

Step 7: Final Configurations

Problem importing Managed Metadata Service

Thanks to this very nice post from Chaitu Madala, I was able to move my Managed Metadata Service from my Dev environment to Test.

I was quite pleased with this progression, but when I moved to my Test environment, I got the following error:

Import-SPMetadataWebServicePartitionData : The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

To fix,

• SQL Server Management Studio > Security > Logins
• Find the account that is running the Application Pool for your MM Service App, 
• Go to the properties for that login and see what the Default Language is.
• If it is set to British English, then change it to English
• Click OK.

The remote server returned an error: (401) Unauthorized. SharePoint BCS

Frustrating couple of hours yesterday trying to figure out a 401 Unauthorized error when trying to access an External BCS list programatically using the CSOM.

After jumping through a few well documented gottchas, I finally came stumped on the error on my console:

The remote server returned an error: (401) Unauthorized.

The error came straight after my command:

ctx.ExecuteQuery();

I finally got the tip from forums that the issue related to Alternate Access Mappings.

In my code's settings.xml, I was referring to the FQDN URL:  http://machine.domain.ext.com:port

However, in the Namespace of the External Content Type (in SharePoint Designer), it was referring just to http://machine:port.

Once I updated my code to use the same URL as the External Content Type Configuration, it all worked perfectly.

JQuery image lightbox for SharePoint 2010

When creating large content pages with multiple images embedded (like help documentation or blogs), the content may be more readable if there are small images within the body of the page that expand out to larger, light-boxed images when clicked.  Or you may prefer to have hyperlinks on the page with a unique style (like a red hyperlink) so that users can click on it to see a pop up image. 

This blog walks through the process to meet each of these scenarios.

Note that this has been implemented in SharePoint using the standard modal dialog (SP.UI.ModalDialog.showModalDialog) that comes out of the box with SP 2010.  JQuery is still required, but SharePoint does most of the heavy lifting.

The steps taken:

Part 1: Set up the CSS and Masterpage

1. CSS: In a .css stylesheet that is referenced in your Master Page, add the following classes:
   
2. Reference JQuery in Master Page: Within the <head> tags of your /_catalogs/masterpage/Custom.master Master Page, add a reference to the JQuery code in appropriate location (version 1.10 + is required):
   
   <script type="text/javascript" src="http://sitecollection/Style Library/Scripts/jquery.js"></script>
3. MasterPage: In the footer of the same Master Page, add the following:
   
4. More CSS (optional): Finally, you may wish to fix the modal dialog on the page - meaning that wherever you were scrolled to on the page, the dialog will always be in the centre of the screen.
   To do this, update a style sheet that is called by the System Master Page (most likely "v4.master" but you can check at SiteCollection/_Layouts/ChangeSiteMasterPage.aspx).
   Add the following class:
   

Part 2: Applying the pop-up to content pages

Applying the pop-up using images

1. Insert a logo image on the page.  This is the one I have used:.
2. Apply the “Lightbox” style from the “Image Style” drop down:
   
3. Insert a link on the image to the image that you want to see in the modal dialog:

Applying the pop-up using Hyperlinks

To apply the pop-up image functionality to inline text, rather than using an icon image, do the following:

1. Insert the text into the page, highlight and select “Insert” Link
   
2. Open the page in HTML mode, add the class to the <a> tag:
   
3. Save the page and click on the link to see the image in Modal Dialog form

The finished product

Your pages look clean and mean:

And when users click on a familiar icon (or hyperlink style), a helpful modal dialog will popup and give them a full resolution image to enrich your content:

Finally, of the snippets above can be downloaded here.

itemstyle.xsl Grid View returns blank rows in IE7

This is a problem that will have diminishing significance as fewer and fewer organisations are still using IE7 as their corporate browser (thank God!).
I was looking to display the output of a SharePoint CQWP as a Grid view using a custom template in XSLT.  I was able to achieve this by following this very helpful blog:
http://paulgalvinsoldblog.wordpress.com/2007/12/09/display-content-query-web-part-results-in-a-grid-table/

This was fine for me in IE8, IE9, Firefox and Chrome.

The problem came when I browsed to the site using IE7. See image:

As can be seen, a blank set of tags was being inserted by SharePoint (<>) which was creating a line break.

How did I fix it? By using Child and Descendant selectors in CSS.

in my custom style sheet, I added the following code:
tr>td>table>tbody>tr>td>table>tbody>tr>td>div>div>ul>li>table>tbody li {
height: 1px; overflow: hidden; display: none; visibility: hidden;
}

HTH. :)

People Picker Field PDFs vs. Office Documents

I was having intermittent issues with a custom alert email that I had created.  It would send most of the time, but would inexplicably not always work.
A better programmer than me would have set up exception catching that wrote to the Event Viewer but I couldn't seem to get that to work (might blog on that one later).
Alas, after much frustration I noticed that the alert emails were only firing for Office Documents (eg. Word, Excel) and not for PDFs.
This perplexed me so I opened up SharePoint Manager and browsed down to the items I was trying to render.
On comparison of the properties I was trying to render to the Alert email, I noticed that Office Documents stored People Picker user names as 15;#John Smith, while PDF documents store People Picker User names as just 15.
So my short-cut function that previously read:
       private string GetPPorLookupValue(string LongString)
        // Takes People Picker or Lookup column input in the format: 15;#John Smith
        // splits it and returns "John Smith" (to right of '#')
        {
            string[] SplitArray = LongString.Split('#');
            return SplitArray[1].ToString();
        }

Needed to be made a little bit more robust by actually referring to the User Listing in SharePoint:

        private int GetPPorLookupInteger(string LongString)
        // Takes People Picker or Lookup column input in the format: 15;#John Smith or 15
        // splits it and returns integer 15 (to left of ';', if exists)
        {
            string[] SplitArray = LongString.Split(';');
            int UserNum = Convert.ToInt32(SplitArray[0]);
            return UserNum;
        }

        private string GetUserName_ID(int UserID, SPAlertHandlerParams ahp)
        // Takes User ID as a SharePoint User ID: 12
        // returns username "John Smith"
        {
            string username = "Unknown User";
            if (!(UserID == 0))
            {
                SPWeb thisweb = new SPSite(ahp.siteUrl + ahp.webUrl).AllWebs[0];
                foreach (SPUser user in thisweb.SiteUsers)
                {
                    if (user.ID.Equals(UserID))
                    {
                        username = user.Name;
                        break;
                    }
                }
                thisweb.Dispose();
            }
            return username;
        }

string ReportUpdater = GetUserName_ID(GetPPorLookupInteger(item.Properties["ReportUpdater"].ToString()), ahp);