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.



17 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
    3. You're right Mad White Hattter, this doesn't work in SP2013

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

    ReplyDelete
  9. Does this only work for the Title column?

    ReplyDelete
  10. Hello, my "department" column in the “Employee Data” list is a calculated value, is there a way to copy this into the lookup column so that it can be counted? Thanks.

    ReplyDelete
  11. I have used to find the total count of records under one project ID. How to find other few more column's count under same Project ID.

    For Example : I have a Table 1 :

    Project ID Artefact Inprogress Completed Reviewed
    1 HLD Yes
    1 LLD Yes
    1 Process Doc Yes


    In Table 2 ,

    I need to display like below :

    Project ID Total Artifacts inprogress Completed Reviewed
    1 3 2 1 0

    In Table 2 , total Artifact can be displayed using the logic that you have mentioned above. Wondering how to display the other column counts from Table 2.
    Can you guide me ?

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...