SharePoint Column: Count Related Items

I also refer to this as a reverse lookup field.  In this post I’ll show you how to display the number of items that look up to any given item.  For example, if there is a list of doctors and a list of patients, each patient can have one doctor, but any doctor can have multiple patients.  In this post, I’ll show you how to display the number of items that look up to each doctor.

  1. Create a list of patients.
  2. Create a list of doctors, you can rename the title field to be called “Full Name”.
  3. In the patient list, create a lookup field that points over to the list of doctors, to get the doctor’s full name.  All the rest of the settings on the screen will just be default values.
    200-image_7_7A05C450.png
  4. Go over to the doctors list.  Create a new column, as a lookup column, call it Count Patients.  For Get Information From, pick the Patients list.  For the column, choose Doctor (Count Related).
    200-image_8_7A05C450.png

That’s it!!

Now you can see that your new column tells you how many items in the other list look up to itself:

200-image_9_7A05C450.png

Tim Ferro taught me this trick in his post here:  Pie Chart with Counts

Technorati Tags: ,

19 comments

  • Jake @ Pioneering Evolution

    Excellent post!!! Thanks Laura

    Like

  • Alexey Krasheninnikov

    This doesn’t work with multi-lookups, unfortunately.

    Like

  • Have you observed Laura this will not filter any column and export to excel gives us a blank sheet

    Like

  • Is there a way then to create a new column that contains the concatenation of the Doctor’s name and # of patients. Something like =CONCATENATE([Doctor],”-“,[Count])
    It does not seem to let me use the count field in this way.

    Like

  • Thakhi, I’m not surprised by that.

    Jeff,
    One thing you could do would be to create a view on a web part with just those 2 columns, or just display them next to each other. Or if it really needs to be its own column, you could do it in a data view web part or use a workflow to put those values together.

    Like

  • Have you tried using that column in a workflow? I tried to reference in a workflow and it does not work. I even tried a simple workflow to just log the value of the Lookup – Event (count related) field and it fails.

    Like

  • Zach,
    No I’ve never tried it in a workflow. Doesn’t surprise me that it doesn’t work, though.

    Like

  • Wow that is very nice post

    Like

  • Say the patient list in the example had a status column which could contain the values “Current” or “Non-Current” and you wanted the count in the doctors list to only count current patients. How can that be achieved?

    Like

  • You could do grouping, and group the doctors list by the patient status if you’re pulling it over along with the lookup field. Grouping automatically shows totals. There isn’t a way to filter what the count counts, though.

    Like

  • is there a way to get the count related value passed to a workflow in SharePoint designer 2013 ? I need this value to enable if condition in my workflow ( i.e. if count related value =0 then … )

    Like

  • This isn’t working for me, I cant see count related column no matter which list I take

    Answer asap can reply me on : jinivthakkar@gmail.com

    Like

  • PATRICK PICKERING

    Interesting commentary , I loved the info ! Does anyone know if I would be able to access a template Calendar form to use ?

    Like

  • Hi,

    Is there a way to link the count related column to a specific view instead of whole list data.

    Thanks
    Virender

    Like

  • Is it possible to use this reverse lookup to link two lists for purposes of populating linked web parts?
    I want to create a web part page that lists tasks assigned to a user, and when a task is selected, it should the list item that the task was generated from.

    Thanks!

    Like

    • You can do that with web part connections. Put web parts of each list on a page and connect them. Then, selecting an item will show the related items in the other list

      Like

    • I have tried this, in numerous variations, and have only succeeded from List A to List B.
      List A is a submission list with a workflow attached that creates tasks on List B.
      I thought SharePoint would automatically relate the two lists due to this, but it did not.

      Originally I created web parts for each and tried to link then, but no fields existed between the two lists to relate them.
      So I actually found an article of yours from 2011 about creating the site column lookup field, adding it as a task field in the workflow, and assigning it at the beginning of task creation. This worked perfectly.

      I now can create web parts and select an item from List A, and associated tasks in List B do appear.
      BUT I want to achieve the reverse – I want users to be able to select their Task in List B, and see the original submission in List A.
      I tried using the fields created above, but nothing appears.
      So I tried creating a reverse lookup (count related) to see if that would work – but after linking, still I get no records from List A.

      I appreciate your fast response, and taking the time to help me with this!
      I am still new to SharePoint, but this seems like this shouldn’t be so hard!
      I don’t know if I am doing something wrong, or if I a just trying to do something that it isn’t designed to do…

      Thanks again!

      Like

  • I am trying to do this in SharePoint Online and I just don’t see the Count Related option. Is there a feature that needs to be activated? Do you know if this is available in SharePoint Online E1? Thank you!

    Like

Leave a reply or question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s