Using Look up fields in SharePoint 2010, we can force a relationship behavior.

Microsoft have now provided with few new options when working with Look up Fields:

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

Projected Fields

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

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

Relational integrity

SharePoint 2010 and relations in lists, you would wonder how it handles the relational integrity.

There are generally two relational integrity options:

  • Restricted delete 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.
  • Cascade delete Cascade delete means that when you’re trying to delete an item which has relations - it’ll delete the related items as well.

Step by step

Create a parent list

  • Create a new List named “ParentList”
  • Create a new Column in that list with the following settings:

11

  • Add some sample records in the list :

12

Create a child list

  • Create a new List named “ChildList”
  • Create a new Column in that list with the following settings:

13

Kindly note that I have checked the City check box. This will create a Projected Field against the look up automatically. Now you can view that information which exist in the parent list directly in the child list.

Test the projected field functionality

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

14

Forcing relational behavior

When you create (or change settings for) a look up field (like the “ParentLink” field), you can change settings for the relational behavior.

Go to “ParentLink” column, or when you create a new look up field - and see the below dialog box:

15

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

Restricted Delete

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

161

Cascade Delete

If you choose the “Cascade Delete” option for look up field instead, you will be sending the items to the Recycle Bin . Then it will look like this in the recycle bin:

171

You have the icon that looks like a relational diagram in your recycle bin  this means that you have deleted an item that may have deleted linked items. If you restore this item it will automatically restore all of the items that were originally deleted.