How to link two lists in Microsoft SharePoint

 Apr 24, 2014

Almost all information about an item, called a record, is recorded in small chunks of data called 'fields.' For example, information about an employee is recorded by fields such as 'name,' 'surname,' 'department,' 'manager,' and etc. Quite often, these fields are categorised in a way so that they can logically fit under one category, and the rest under a different category. For example, name and surname can fit under the 'Personal' category while fields such as 'department' and 'manager' can fit under the 'Departments' category.

In database terminology, each category is called a 'table' while in Microsoft SharePoint terminology, each category is called a 'list.' Therefore, we now have information about an item or record that is spanning across more than one table or list. To relate these records together, we need to introduce the idea of 'Join,' which is a feature that will link the two tables together so that a record in one table can find its continuation in the other table.

In databases, we create a 'Join' using the keyword JOIN, and then the database engine does the rest of the work. In SharePoint, if we are going to 'Join' lists together we need to use a 'Web Part' that is designed for this purpose. So let’s see how we can accomplish such a link in SharePoint.

Let’s say that we have a list that shows employees details. We’ll call this list 'Personal.' Let’s also assume that we have another list that shows all employees who have taken time off. We’ll call this list 'TimeOff Application.' To be able to link theses two lists together, we need to put them both on one page using 'List View Web Parts.' So here’s what we should do.

How to link two lists in Microsoft SharePoint

  1. Create an empty page or use an existing one.
  2. Go to 'Site Actions' and select 'Edit Page.'
  3. From the 'Insert' tab, select 'Existing List' and then highlight your first list (in our case, we're using the 'Personal' list). Click on the 'Add' button on the right.
  4. Similarly, add your second list (this time, we'll use the 'TimeOff Application' list).
  5. Now save your page by clicking on 'Save' and then close the 'Format Text' tab.

How to filter the newly joined list

What we want to do now is be able to select an employee by clicking on an item in 'Personal' and also, have the 'TimeOff Application' filtered to only show dates taken off by the selected employee. Here's how you would you do this.
  1. Go to the edit view again by clicking on 'Site Actions' and then selecting 'Edit Page.'
  2. You will see some blue borders around your Web Parts. Move your mouse to the top right hand corner of the border around your second list. A little down arrow will appear. Click on it and select 'Edit Web Part.' You should now see a pane appearing on the right hand side of your page. (Note: if you can’t see the pane, scroll to the right and then scroll up).
  3. Now select the down arrow again, click on 'Connections' and under 'Get Filter Values From,' choose the name of your first list.
  4. A small window will appear prompting you to indicate the field that will join the two lists together. In our case, it’s 'EmpNo.' Select your relevant fields and click 'Finish.'

That’s all you need to do. Now click the OK button on the 'Edit Web Part Pane' on the right hand side and the page is ready to be used. You will now notice that a new column 'Select' has been added to the first list, which shows a double headed arrow icon for each entry on the list. By clicking on any of the icons, you are indicating that you want the second list to be filtered based on that entry, which is exactly what SharePoint is doing. We are selecting EmpNo 4354 and the TimeOff Application list is filtered to only show times taken off by that employee.

You need to remember that this is only one way of applying a link between two lists. There are other ways that you can do so, mostly by using Filter Web Parts, which we'll save for another time.

How do your Excel skills stack up?   

Test Now  

How do
your Excel skills
stack up?

Grade your skills now

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
top
Back to top