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.

Post Title Column

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.

Lookup Count 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.

RSS Settings

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.