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).
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