Wednesday, April 20, 2011

Lookup Count Related

Just a quick post that I will dive into later after more testing but this is how I am using the CountRelated option to count comments for a blog I am building:

<Field Name="NumComments" ReadOnly="TRUE" Type="Lookup" DisplayName="Comments" CountRelated="TRUE" List="Lists/Comments" ShowField="PostTitle" ID="{Guid Here}" StaticName="NumComments" />

So let's say you have a list called posts for a blog or just about any list will do. I created the field NumComments above. It is a lookup field to the Comments list looking for the field PostTitle. This will actually count the number of comments that have the title of the Blog item in the Comments list in the PostTitle field. This is how the comments field in the OOB blog sites in WSS work.

So another use might be to count the number of times certain keywords or categories show up in a list. The main key is that CountRelated attribute. The rest of the field definition is a standard lookup field. So instead of displaying text, it displays a number which is the count of times the lookup shows up in the selected list. So this field is defined in a parent list and the Lists/Comments in the example would be the child list. ShowField is the name of the field that you are looking up that contains the value you want to count. That is pretty cool!

8 comments:

  1. Hi, you got any more info on this? I'd like to do something like this. I have a parent and child list and would like to have a column on the parent list showing a count of items in the child list.

    Does your solution do this type of thing?

    ReplyDelete
  2. Actually yes, this is exactly what this does. I will update the post with more info.

    ReplyDelete
  3. Hi, awesome... this is exactly what I need. How do I add it to an existing list in SPD 2010. I can't seem to get to the list definition to add the important bits you mention in the post.

    ReplyDelete
  4. I will be honest in that I do not know if you can do this in SPD. I will have to look into that. This is something that you would either have to do by creating the list in Visual Studio or using Object Model code and a feature to programmatically add the field. However, I will try to see if it can be done in SPD and get back to you as soon as I find out!

    ReplyDelete
  5. As far as I can tell, you can not do much in this case using SPD 2010. If you are prepared to do some heavy work, you could use code to modify the field definition. You could do this using .Net object model code, or with jQuery and web services. Each method would work fine. If you have VS 2010 and are creating a list or field, you could add the CountRelated property there.

    ReplyDelete
  6. Hey, Thanks for the help... Ended up doing it with VS2010 based on this article:
    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfieldlookup.countrelated.aspx#Y364

    It's all wokring now... :P

    ReplyDelete
  7. Cool. That was sort of what I meant when I said using Code. The initial definition is when you are actually creating the field in a list definition. Thanks for visiting my blog and I hope you may find something useful here in the future!

    ReplyDelete
  8. This can be done through the UI - just pick the related list and choose the lookup column for the parent list. It will have (Count Related) beside it. Works like a charm.

    ReplyDelete