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.
- Create a list of patients.
- Create a list of doctors, you can rename the title field to be called “Full Name”.
- 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.
- 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).
That’s it!!
Now you can see that your new column tells you how many items in the other list look up to itself:
Tim Ferro taught me this trick in his post here: Pie Chart with Counts
Technorati Tags: SharePoint,Lookup columns
Excellent post!!! Thanks Laura
This doesn’t work with multi-lookups, unfortunately.
It works with multiple look ups
Awesome. Notice that my answer to that post was almost EIGHT years ago. Microsoft must have changed / fixed something since then, cool.
Have you observed Laura this will not filter any column and export to excel gives us a blank sheet
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.
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.
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.
Zach,
No I’ve never tried it in a workflow. Doesn’t surprise me that it doesn’t work, though.
Wow that is very nice post
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?
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.
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 … )
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
Interesting commentary , I loved the info ! Does anyone know if I would be able to access a template Calendar form to use ?
Hi,
Is there a way to link the count related column to a specific view instead of whole list data.
Thanks
Virender
Nope
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!
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
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!
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!
Thanks for posting, very helpful!
Laura, you use this feature in Episode 48 of SharePoint Power hour and I haven’t been able to replicate it. In the video, you set the workflow to calculate “IF CurrentItem:Max is less than or equal to CurrentItem:Registrants (the count related lookup field)” but my workflow fails every time I use this statement (I can tell from the log history). I don’t think SP recognizes the count related lookup field as a number because the workflow works fine if I replace “currentItem:Registrants” with “0”. Does anyone have a solution to this? I’m not sure how to utilize a MAX/Count Related when I cannot create calculations off of the count related reverse lookup field.
It’s been so long since I’ve done that. Try doing a set workflow variable action, and set that count as a number variable, then use the number variable in the if statement?
Is there a way to display the patients in a dialogue box when the count is selected? I. E. Doctor X has 6 patients, and when the 6 is clicked it will display the names of the patients stored in the patients list?
No, not out of box. If you are in SharePoint online, read my posts about formatting list columns, you could customize it to make a hyperlink.
Thanks for this post. How to do the same for multiple columns :
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
how to display the other column counts from Table 2.
Sorry, there isn’t an out-of-box way to do that, with that technology/setting.
Hi Laura, I can’t get this to work. Can U share what are the columns in the Patients List ? I presumed you have the name of the patient and one other column which is the Doctor attending to this patient.
No, that post is 8 years old, the solution probably doesn’t work anymore.
How to get the data of count column using pnp.js ? Any idea? Or how would u write a query to get those counts using javascript?
I don’t know code or JavaScript.