SharePoint Calculated Columns with Dates

I recently had a request to create a view on a list and group the view by the year and quarter of a particular date. i.e. the list in question has a date column, say "Start Date", and the view should be grouped by the year of the date e.g. 2007, and then by the quarter i.e. 1, 2, 3, or 4 depending on the month.

I did this by creating two new calculated columns on the list. The first column is called "Year", is of type "Single line of text", and has the formula shown below:

=TEXT(YEAR([Start Date]),"000")

I created this column as a single line of text instead of a numeric column because a numeric column would show the year "2007" as "2,007" with a comma in it which I didn't want.

The second column is called "Quarter", is of type "Number", and has the formula shown below:

=ROUNDDOWN(((INT(MONTH([Start Date]))-1)/3),0)+1

Hopefully someone will find these useful as there isn't a whole lot of information out there on the format and syntax of the calculated columns. However, some good examples can be found here.

Determining Whether a User is a Member of a SharePoint Group or Not . . .

By using the SPUser.Groups property you can easily enumerate the groups that a user has been assigned to. However one problem with this approach is that if the user is a member of a domain group that has been allocated to a SharePoint group, then this group does not appear in SPUser.Groups. e.g. say that a user account is assigned to SharePoint groups SP1, SP2 and SP3 and that the user is a member of an AD group, ADGroup1, which in turn is assigned to SharePoint group SP4. When enumerating SPUser.Groups only SP1, SP2, and SP3 will be listed even though the user is a member of SP4 indirectly via his membership of the ADGroup1 group.

So, how can we ascertain whether a particular user is a member of the SharePoint group or not, taking into account AD group membership ? The short answer is use the SPGroup.ContainsCurrentUser property as shown below:

string siteUrl = "http://localhost"
string userName = "DOMAIN\\username";
string groupName = "SharePoint Test Group";

SPSite siteCollection = new SPSite(siteUrl);
SPWeb site = siteCollection.OpenWeb();

SPGroup testGroup = site.Groups[groupName];
if (testGroup != null)
{
Console.WriteLine(
String.Format("Is current user in group={0}",
testGroup.ContainsCurrentUser.ToString()));
}

Obviously this only tells you whether the user account running the current context is a member of the group or not. In order to ascertain whether a different user is in the group you'll need to create an SPSite object using the appropriate SPUserToken to give you the appropriate context to use. An example can be seen below:

string siteUrl = " http://localhost ";
string userName = "DOMAIN\\anotheruser";
string groupName = "SharePoint Test Group";

SPSite siteCollection = new SPSite(siteUrl);
SPWeb site = siteCollection.OpenWeb();
SPUserToken userToken = site.AllUsers[userName].UserToken;

// Use an SPSite and SPWeb that have the context of the appropriate user
using (SPSite contextSiteColl = new
SPSite(siteUrl, userToken))
{
using (SPWeb contextSite = contextSiteColl.OpenWeb())
{
SPGroup testGroup = contextSite.Groups[groupName];
Console.WriteLine(String.Format("Username = {0}, got group {1}.",
userName, groupName));
if (testGroup != null)
{
Console.WriteLine(String.Format("Is current user in group={0}",
testGroup.ContainsCurrentUser.ToString()));
}
}
}

Office 2003 an 2007 Client Co-existence Problems

I came accross a problem a while ago where IE crashed when trying to select "Edit in Microsoft Office Word" from a document's context menu in a document library. It turned out that this was related to the fact that I had Office 2007 Professional installed, but also had Project 2003 installed i.e. a co-existence problem with the 2003 and 2007 versions of the Office client apps.

I managed to get around this problem by renaming the owssupp.dll located in C:\Program Files\Microsoft Office\Office 12 to something else e.g. owssupp.dllx which stopped IE crashing but gave me the problem that I got a pop-up telling me I needed IE 5.x or greater.

Mark Wilson kindly pointed me to an article by JPOX which provides the perfect workaround and now everything is working as it should.

Intelligent Document Templates

I had an email from a colleague asking me how to go about creating a document template where the content of the template contains metadata from the content type's metadata. I thought this was valuable to others as I haven't seen anything posted about this, so I thought I'd write this post to explain how to achieve this.

Upon completion, you will have a two-way template where data entered into a document based on the template will populate the document's metadata. Also, metadata entered into a document's properties panel will auto-populate the document's content.

Imagine we have a content type called "Contract" assigned to a document library and that the content type has the following metadata:

• Contract Number: Number (0 D.P.)
  
• Contract Date: Date & time (date only)
  
• Customer Name: Single Line of text
  
• Contract Duration: Choice (1 year, 3 years, 5 years)
  

To create an intelligent document template:

1. Click the "New" button on the document library to create a new document based on the Contract content type – this will fire up Word 2007 and open a blank document of content type Contract.
   
2. Save the document locally instead of back into SharePoint, say to the desktop.
   
3. Open the "Insert" tab within Word 2007.
   
4. Drop down the "Quick Parts" menu, and open the "Document Properties" sub-menu.
   
5. Here you will see a list of all the properties of the document, including our custom metadata defined above.
   
6. Select the property you are interested in from the menu and a quick part is inserted into the document at the location of the cursor.
   
7. Continue this approach to add additional quick parts, then save the document locally again.
   
8. Open the settings for the Contract content type (Home Site -> Site Actions -> Site Settings -> Modify All Site Settings -> Site content types, then select the "Contract" content type).
   
9. Click on the "Advanced Settings" link for the Contract content type.
   
10. Select the "Upload a new document template" radio button and browse to the file we saved locally.
    
11. Leave the other settings at their default values and select "OK".
    
12. Now go back to the document library where the Contract content type is being used and click the "New" button to create a new document. This new document will be based on the new template and will contain the quick parts that we added previously.
    
13. Completing the metadata in the document properties panel will complete the content in the quick parts in the document's content. Likewise completing the quick parts will populate the document's metadata properties.
    

Hope this all makes sense . . .