SP 2010: How To – Relational lists in SharePoint 2010

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren


One of the new cool things in SharePoint 2010 is the fact that we now have relational lists. In my previous article about List Joins, I talked about how you programmatically can fetch and join information from more than one list with the improved SPQuery object.

In this article I will give you an overview of what capabilities you get out of the box when installing SharePoint 2010 – in terms of relational data in lists.

Relational Lists in SharePoint 2010 – Overview

By utilizing Lookup fields in SharePoint 2010, we can enforce a relationship behavior that we previously would have to work very hard to achieve.

Microsoft have now provided us with a few new options when working with Lookup Fields:

  1. Joins between lists
  2. Projected Fields
  3. Relational integrity

Joins between lists

As mentioned, we have the capability to create relationships between lists in SharePoint 2010. This is quite easy to do using the browser UI, which I will soon demonstrate step-by-step.

Projected Fields

With projected fields we have the capability to pull information from a parent list into the view of the child list.

This basically mean that you can reference and display information from a parent list, in your child list. The fields are Read-Only but enables you to get a much nicer joined view.

Relational integrity

With SharePoint 2010 and relations in lists, you would of course wonder how it handles the relational integrity. E.g. what happens if I delete or try to delete something in the parent list?

Well, there’s generally two relational integrity options:

  1. Restricted delete
    Basically the restricted delete option enables you to enforce a behavior that means that you can’t delete any items that have relations from the list. E.g. if the item you’re trying to delete have a bunch of child-items, you cannot delete them.
  2. Cascade delete
    Cascade delete on the other hand, means that when you’re trying to delete an item which has relations – it’ll delete the related items as well.

Delete and Recover related items – Recycle Bin

A question I got the other day was:

"If I delete an item in my parent list and have cascading delete so all my child items are deleted, how do I restore them if I made a mistake?"

Quite simple my dear Watson, you utilize the recycle bin. When you delete an item using Cascading Delete, the item and it’s related items are placed in the Recycle Bin. From there you can obviously easily recover the items as well. This is what an item with relations looks like in the recycle bin:


Step by step – Do it yourself

Alright – so we’ve covered some of the basics of relational lists, nothing fancy. But now we want to create some lists and have relationships between them – so let’s get on with it!

1. Create a parent list

  1. Create a new Custom List named "ParentList"
  2. Create a new Coumn in that list as per the following settings:
  3. Add some sample items in the list like this:

2. Create a child list

  1. Create a new Custom List named "ChildList"
  2. Create a new Column in that list as per the following settings:
    Please note that I checked the City checkbox. This will create a Projected Field against the lookup automatically so you can view that information which exist in the parent list – directly in the child list.

3. Test out the projected fields functionality

Add some new items in the ChildList to see that when you add an item and choose a company from the ParentList it will automatically show the projected field ("City") as a read-only field in the child-list:

4. What about enforcing relational behavior?

I’m glad you asked. When you create (or change settings for) a lookup field (like the "ParentLink" field), you have the ability to change settings for the relational behavior.

Go to your "ParentLink" column, or when you create a new lookup field – and see the following dialog:

From this dialog as you can see, you have the ability to make the necessary settings for your fields.

Restricted Delete

If you choose the "Restricted Delete" option, you will see the following behavior when trying to delete an item that has related items:

Cascade Delete

If you choose the "Cascade Delete" option for your lookup field instead, you’ll be sending the items directly to the Recycle Bin instead. Then it’ll look like this in the recycle bin:

Note, that you have the icon that looks like a relational diagram in your recycle bin – this means that you’ve deleted an item that may have deleted linked items. If you restore this item (in our case, TOZIT) it will automatically restore all of the items that were originally deleted.

So, we have the kind of enforced relationships we’ve longed for since the dawn of days!


Voila. Easy as 1-2-3, you have created two very simple lists and created a relationship between them – and optionally you can enforce this relationship using the "Enforce relationship behavior" settings for Restrict Delete or Cascade Delete.


Follow me

Tobias Zimmergren

Product Owner, Cloud Offerings at Rencore GmbH
Tobias Zimmergren delivers high-quality articles about business and technology around the Microsoft scene.

Tobias focuses on advisory and consultancy for the Office 365 and SharePoint offerings from Microsoft.
Follow me
  • Yarono5

    I have a question: I have created two lists:
    1. Jobs
    2. Employers
    I wish to create a one-to-many relationship between the jobs and the Emploers lists (i.e. An Employer can have multiple jobs).
    I have created these two lists and created the lookup column. However, I have this problem when I add a new job and set its employer on the form and save. When I go to employers it doesn’t automatically add the newly created job.

  • Hasibk014

    Nice ..

  • http://twitter.com/retractedspace Richard Pickles

    Only problems I can find is
    1. I want to lookup a column which is a ‘multiple lines of text’ but this does not show
    2. When a user edits the child level list the looked up columns don’t show.

    • http://www.markadrake.com/ Mark Drake

      Richard did you ever find a solution for multiple lines of text?

  • Roze

    how we can query list based on city(projected field) ?

  • Boonrs

    Images are not displaying for me. Perhaps your image links are broken?

    • http://www.zimmergren.net/ Tobias Zimmergren

      Thanks for the heads up, I’ll be sure to look into it – the images are broken since I migrated the blog.

    • http://www.zimmergren.net/ Tobias Zimmergren

      The images are now working again. Enjoy.

  • Mcduff359

    Can’t see the images…

    • http://www.zimmergren.net/ Tobias Zimmergren

      Thanks for the heads up, I’ll be sure to look into it – the images are broken since I migrated the blog.

    • http://www.zimmergren.net/ Tobias Zimmergren

      Mcduff359, the images are working now.

  • BryanG

    Thanks for the post. One question – In my parent table, I have included an existing site column – Customer. I don’t seem to be able bring this column in as a projected field in the child table – it’s not even an option in the list (other non ‘existing site column’ values are available).

  • Pingback: SharePoint Relational Lists « Sladescross's Blog()

  • Koecrit

    can i join external list and custom list ?

    how to use linked data source (external list & custom list), data source doesn’t appear in data view web part ?

    • Echo Schmidt

      I’m trying to make an external list and a custom list join as well. So far no luck. I would love to know if you found a way to make this occur.

  • rhowe01

    Thank you. This was exactly what I needed.
    I did learn the hard way that the “parent link – city” column in the parent list can not be “choice” column, as it will not show up in the check box options. I suspect it can only be a single line of text column. it seems this may have tripped up some of the other commenters too.
    Thank you again!!

  • http://post404.com/ Randall “texrat” Arnold

    Very useful. To take it a step further, and filter one list based on another (3 lists involved), I found this tutorial extremely clear and helpful: http://basquang.wordpress.com/2010/03/29/cascading-drop-down-list-in-sharepoint-2010-using-infopath-2010/

  • disqus_MISGJpUvWQ

    Thank you for this wonderful procedure. I have added the child webpart to my Parent in the Editform. Is there any way when creating a child record to automatic populate the parent link to the Title of the selected record. Right now I have to select it from a dropdown.

  • Video0160

    SharePoint Online delivers the powerful features of SharePoint without the associated overhead of managing he infrastructure on your own. Flexible management options ensure that you still retain the control you need to meet the compliance requirements of your organization. You can purchase SharePoint in the cloud as a standalone offering or as part of an Office 365 suite where you could also get access to Exchange, Lync, the Office clients and web apps.
    Sharepoint developer






  • BPTI

    Can you use the native SP 2010 Form to retrieve and write to these lists?
    For ex you have two lists
    1. Employer Information
    2. Employee Information

    The list can manifest itself as an elegant form for one of these lists. Can I do something same to have these two relational lists combined to present themselves in one form along with only the data elements that I need?

  • joshua

    is there a way to create a parent – child – grandchild relationship in sharepoint that is displayed in a list that is able to be exported to Excel?

  • Arun

    thank u

  • alioune

    Thank you :)

  • Asif Hameed

    If i have two Sites in the same site collection i.e. Site A and Site B
    and i have one custom list in each site i.e. Site A.ParentList and Site B.ChildList

    then can we related both the custom list i.e. ParentList.Title referred in ChildList.ParentTitle??
    Please guide me asap.

    Asif Hameed (KSA)

  • Daniel

    Hi Tobias. I made a ‘list1’ (let’s call like this for better explanation) lookup one field of the ‘fatherlist’, no problem till there, but I need that another list, the ‘list2’ replicate the selection of the lookup field of the the ‘list1’, so the ‘list2’ show the result of ‘list1’. Is it possible? It would be a lookup of a lookup?

    Hope you can help me out.

    Thank you for the attention.

  • Deep

    Hi Tobias,
    Thank you for this write-up; extremely helpful. However, it seems like when I build a relationship table pulling data from two parent tables, I can only pull fields that are single line text (they don’t show up “Add a column to show up each of the additional fields” section otherwise). Wondering if you have a workaround – I want to also show fields in the relationship table that are dropdowns/date etc. in the parent table. Any help will be greatly appreciated.

  • Nelson Nona Nadal

    This is a nice sample, however, I have a question on the projected field (“City”) as a read-only field. How can I access that projected field/column? For example, instead of “City”, the projected field is a number (Ex: “Rate”) , I want it be multiplied by the column in my child List which is Ex:”HoursWorked”). How can I do it, to access it or to have a reference of it? Many thanks again.

    Parent List :

    Contract……… 8
    Probationary.. 5

    Child List
    PAYROLL List

    MR A….Regular…………10…………………..8……………..80
    MR A …Regular…………10…………………..5……………..50
    MR B …Contract……….. 8 ………………….8……………..64
    MR C …Probationary…. 5……………………8……………..40

    How will I access the CATEGORY:Rate and multiply to PAYROLL:HoursWorked, thanks.