How to work with dates using XSL stylesheet

Calculating the difference between two dates and then do a decision based on the outcome can sometimes be tricky with XSL, because one only have the dates as numbers more or less. You don't have an Object of DateTime like you have with C# etc.

One requirement I often met is to format fields based on if a task is overdue or not, with some more logic like yellow lights to if its close to the due date. The easiest way to do this is often attaching a xsl:stylesheet to the list view. The tricky part is getting the days, months, years right. You have to compare the due date of the task up against the values for today, this is easy when they are in the same month when you can take the difference on days. The problems starts when they are in different months, and especially with months like February with leap years.

What I always do is that I first create all the variables for day, month and year for both the due date and today's date.

<xsl:variable name="MonthCurrent" select="number(substring-before(ddwrt:FormatDate(ddwrt:Today(),1033,1),'/'))"/>
<xsl:variable name="DayCurrent" select="number(substring-before(substring-after(ddwrt:FormatDate(ddwrt:Today(),1033,1),'/'),'/'))" />
<xsl:variable name="YearCurrent" select="number(substring-after(substring-after(ddwrt:FormatDate(ddwrt:Today(),1033,1),'/'),'/'))" />
<xsl:variable name="DueDay"  select="number(substring-before($thisNode/@DueDate,'.'))"/>
<xsl:variable name="DueMonth" select="number(substring-before(substring-after($thisNode/@DueDate,'.'),'.'))" />
<xsl:variable name="DueYear" select="number(substring-after(substring-after($thisNode/@DueDate,'.'),'.'))" />

Note that I have in this situation dates separated with ".", like 22.11.1983. But my system dates uses a "/", and returns the date as 11/22/1983.


Next part is to find how many days it is in the month:

Doing it this way it even checks for leap years.

Finally we can find number of days til due date. This is a simplified approach, because in most cases one don't need that many days before the due date. In this scenario its set to max 20 days.

This is the variable you can use later in a stylesheet to check the number of days a task or similar are overdue or not. 

Provision Calculated Fields with Formula

This will be a short note.

I had an issue as always when I try to make something  in Visual Studio. Today it was the first time I had to create calculated field using less than and greater than. And I'm not the most experienced one when it comes to setting up the XML, and it went bad.

I was stuck on how to get the "<" and ">" symbols in.

I could not find any information about it, then I started to think about HTML and entity names. And of course that was the solution, &lt; and &gt;

<Formula>=IF([RisikoRisikofaktor]&lt;0,"Green",IF([RisikoRisikofaktor]&gt;14,"Red","Amber"))</Formula>

Add Lync Presence to SharePoint List column with XSL Stylesheet

One might wonder why one needs this when you have the People field with this ability already. The reasons could be many, but one of them are to get presence and easy access to people not directly associated with your site and Active Directory. And often you have these people added to your Lync contact list. Many companies run their Lync with open federation, meaning they can freely communicate with other IM networks.

In a project site there is most likely a contact list, this lists does not only include people involved with the site, it could be suppliers, clients not involved on the project site etc.

There is many ways to this, but I'll take the Out of the box approach, starting simply by adding the Contact list that ships with SharePoint.

Next I add a column to the list, just a Single line of text, this could be named anything you like, but in this example I named it Lync.


Now drop the Contact list in a web zone (or you could edit it in the views). When you go into Edit Web Part and open Miscellaneous you see there is an option to add a XSL Link:

What we need to do now is create a XSL file with a style sheet, this style will add presence based on the Email field in the Contact list. I could have made another field for the SIP address, I'm just lazy since I have never seen anyone used something else than the email for SIP. You could just create a new column in the list called SIP, and use that instead of the Email field. I also use the Title(LastName) and FirstName fields for this.
PS: Make sure those three fields, and the Lync field is marked as visible in the view used by the Web Part.


Now open your favourite text editor, I like to use Notepad++ for work like this.

<xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal" xmlns:o="urn:schemas-microsoft-com:office:office">
 <xsl:include href="/_layouts/xsl/main.xsl"/>
 <xsl:include href="/_layouts/xsl/internal.xsl"/> 
 <xsl:template name="FieldRef_Text_body.Lync" match="FieldRef[@Name='Lync']" mode="Text_body">
  <xsl:param name="thisNode" select="."/>
  <xsl:variable name="etternavn">
   <xsl:value-of select="$thisNode/@Title" />
  </xsl:variable>
  <xsl:variable name="fornavn">
   <xsl:value-of select="$thisNode/@FirstName" />
  </xsl:variable>
  <xsl:variable name="fulltNavn">
   <xsl:value-of select="concat($fornavn,' ',$etternavn)" />
  </xsl:variable>
  <xsl:variable name="itemID">
   <xsl:value-of select="$thisNode/@ID" />
  </xsl:variable>

  <xsl:variable name="sipAfter">
   <xsl:value-of select="substring-after($thisNode/@Email,':')" />
  </xsl:variable>
  <xsl:variable name="sip">
   <xsl:value-of select="substring-before($sipAfter,'&#34;')" />
  </xsl:variable>

   <div id="PresenceLink_{$itemID}"><span class="ms-imnSpan"><img border='0' height='12' src='/_layouts/images/imnhdr.gif' onload="IMNRC('{$sip}')" ShowOfflinePawn='1' style='padding-right: 3px;' id="PresencePawn_{$itemID}" alt='pawn' /> <a href='' id="ProfileLink_{$itemID}"><xsl:value-of select="$fulltNavn" /></a></span></div>
 </xsl:template>
</xsl:stylesheet>

This XSL overrides the default template, and adds a div tag with html in the column Lync instead. First I create a set of variables, they are in Norwegian; etternavn(last name), fornavn(first name), fulltNavn(Full Name), itemID, sipAfter and sip.

The three first variables explains them self, but the reason I use substring-before and subring-after on the Email is because SharePoint(or an IE plugin, haven't checked that) creates a link from emails, with <a href=mailto:> etc. First I take everything after the char ":" and then substrings everything before the '&#34',  '&#34' is the equivalent to double quote(").

That's all the variables needed to build the presence. If you view the source of any SharePoint site with presence you'll see the same HTML code. I haven't added ProfileLink etc (well its just a href, could be to anything), I just left it in there to show, but it's possible to add a link to lets say the UserProfile, but since these are for non-members of SharePoint its no point. Note: They need unique IDs, that's why I use the  itemID variable.   


Then its time to save the file and upload it to SharePoint, I just dropped it in the Style Library.

Open the Web Part again and add the link to the file you uploaded.

The result: