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.

Comments (13)

  1. does the # symbol mean a lookup wherever you put it?

  2. MSDN Archive says:

    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

  3. Amir says:

    how do you create exact comment list as provide on the blog template?

  4. Greg says:

    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!

  5. saefeldin says:

    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.

  6. Great article, thanks for sharing this information!

  7. Jeff says:

    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!

  8. sadari says:

    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

  9. tricktrick says:

    i am unable to see the images i the article

  10. Scott says:

    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?

  11. Chris says:

    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.

  12. gabi23 says:

    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?

  13. Praveen says:

    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.

Skip to main content