Monday, 2 January 2012

How to create count related lookup column in SharePoint custom lists?

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.

Let’s say I have two lists Departments and Employee Data. Now in Department list I want to add new column which will calculate how many employees are there in that department.

Here’s how we need to setup our lists:

1) Create a Department list. And add your department values in a title column.


2) Create an Employee Data list. In “Employee Data” list I renamed ‘Title’ column to ‘Employee Name’. And then add column ‘Department’ which will be lookup column pointing to the title column in the Department list.



3) After adding some test values in “Employee Data” list the list will look like this



4) Now go to “Department” list, add a column of type "Lookup" referring to the “Employee Data” list and you'll notice that in the drop-down area where you define the lookup, you'll have a new option called "Count Related". This is here automatically because it recognizes that the “Department” list has a lookup pointing back to this one. Select that Count Related option.



5) Now your Department list will have a column counting how many employees are associated with that Department.



This doesn’t require any coding or JavaScript. A hidden SharePoint magic.



13 comments:

  1. This is great and so are you for sharing this!!

    ReplyDelete
  2. Hi,

    Thanks for the very useful info.

    However, how can I restrict adding more than 15 employees to a single department in this example?

    Perhaps we need to use some validation rules, but actually I need to know how.

    ReplyDelete
  3. However, how can I restrict adding only 25 employees to a single department in this example?.and also need to send a mail on success or failure.Can u help on this?

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Isnt this similar to Groupby which displays item count of a particular status? What is the reason to use this approach. Please clarify.Awaiting your response

    ReplyDelete
  6. Hi,
    Thank you so much for the help I appreciate it.

    ReplyDelete
  7. Why don't I see this at all with SharePoint 2013?

    ReplyDelete
    Replies
    1. Could u make it working sharepoint 2013 ? I can't yet

      Delete
    2. Hi, This also works in 2013 as well.

      Delete
  8. Why is this one of the most useful features hidden by Microsoft? Thanks a looooot, Pawan, for unhiding this.

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...