5JAN 2010

SP 2010: How To – Relational lists in SharePoint 2010


Posted by Tobias Zimmergren

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

Introduction

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:

[PIC]

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:
    image 
  3. Add some sample items in the list like this:
    image

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:
    image
    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:
image

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:
image

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:
image 

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:
image

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!

Summary

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.

Enjoy!

  • 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

      Boonrs,
      The images are now working again. Enjoy.
      Tobias.

  • 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.
      Enjoy.
      Tobias.

  • 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

    Sharepoint
    Site

    Sharepoint
    Services

    Sharepoint
    Designer

    Sharepoint
    Consulting

    Sharepoint
    Server

  • 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?