Lookup (Count Related) – The magic behind SharePoint’s # Comments Column

When we were designing blogs in SharePoint, we didn't just want to build a great blog, we wanted to build a great blog using the platform capabilities the SharePoint provides.

One of the new platform features we built to support blogs is the Lookup (Count Related) Column type. A lookup count column is a special type of computed field that computes the number of items in the target list that point to the current item.

Here's how it works for blogs:

The Comments list has a column called "Post Title" which is a Lookup to the Posts list.

The Posts list has a column called "# Comments" which is a Lookup to the Comments list. SharePoint knows that Post Title is a lookup to the current list, and so it displays the option to make this a Count Related column.

You can use this functionality in your sites anywhere you want to count up the number of items that lookup to the current item.

A great place this would be useful is if you wanted to create a podcast or screencast blog. The default SharePoint blog does not support enclosures in the RSS feed, but SharePoint document libraries do support enclosures. You could create a document library for your posts, where the document is the sound or video file. You could then enable comments by creating a comment list that looks up to the library, and a lookup count column that points back to the comments list.

The other interesting RSS setting for document libraries is "Link RSS items directly to their files". When this is set to No, the link will go to the display form for the document library item. When it is set to Yes, the link will go to the document.

Comments

• Anonymous
  February 26, 2007
  does the # symbol mean a lookup wherever you put it?

• Anonymous
  February 27, 2007
  Hi Stefan, The # symbol means "Number of", so "# Comments" is just shorthand for number of comments.  We used this so that the "All Posts" view wouldn't be unneccessarily wide. -Jackie

• Anonymous
  March 27, 2007
  how do you create exact comment list as provide on the blog template?

• Anonymous
  November 15, 2007
  Hola Jackie! Thanks for sharing this... We are trying to provide ratings functionality in a similar way that you've designed comments.  We have a rating list that has a related "Post Title" column and so we can then utilise a #rating column in our posts list - now the part that I can't work out is how to get an average rating and not just a count? Any ideas? Thanks!

• Anonymous
  March 08, 2008
  could this be used in custom list to count items in it ? is there a way to count items based on their values, e.g: close, in progress, completed.

• Anonymous
  March 24, 2008
  Great article, thanks for sharing this information!

• Anonymous
  March 26, 2008
  Great post! I wanted to have the blog categories indicate how many posts were in each category. It seems that this magic would work there as well. Unfortunately I modified the category column in the Posts list to allow multiple values. I don't get the option to "make this a Count Related column" when I add a "# posts" column to the category list, so I guess the lookup count column functionality only works for single-valued lookup fields. Cheers!

• Anonymous
  April 23, 2008
  Hi jakie,   I am looking for same scenario. but your (blog) page is not displaying the images. please place the image so that viewers can view the images too.. thanks sadari

• Anonymous
  June 03, 2008
  i am unable to see the images i the article

• Anonymous
  July 03, 2008
  Can you turn the number into a hyperlink that links to the items? Eg if my main list has 4 sub items, can click on the number 4 and see the items?

• Anonymous
  July 15, 2008
  I'm seeing that if I update anything else on this splistitem object like Body, in the case of blogs, that this # Comments field becomes something like "0;#0" and doesn't display when binding to this data. Displays ok from oob sharePoint pages though.

• Anonymous
  July 29, 2008
  Hello, I've inserted some comments on my Blog posts, which is created on the SharePoint 2007 paltform, but the comments are not linked with the posts (there is no content aprovel setings on it). Do you have an advise for me?

• Anonymous
  April 25, 2011
  Great Post... I dont know if you guys thought of this basic feature.  When a blog post is deleted all comments associated with it should also be deleted.  Any attachments to the blog should be deleted as well.  I am assuming that it is a bug and will get resolved in the next patch cycle. If not please help me undersand the rational behind it.