SharePoint 2010: Lookup lists and relationship

by  Sandeep Nakarmi 

SharePoint 2010 introduces new improvements in lookup lists. They are:

1. Projected fields into child lists
2. Joins
3. Enforce relationship
   • Restrict delete
   • Cascade delete

Projected fields into child lists. In SharePoint 2007, you can create a lookup field that will be linked to a field in another list. Here, only one field of the parent list such as ‘name’ or ’title’ etc., which user opts to associate the lookup field to, will be displayed in child list. SharePoint 2010 adds to this ability where more fields of the parent list can be added to child list. I.e. A lookup on the ID of another list can bring across multiple fields of that list into child list.

 

Joins Programmatically speaking, this will enable us to join two lists and perform a query against the joined lists and retrieve the fields from both the lists. This can be accomplished via API, CAML or LINQ.

 

Enforced relationship Another improvement in SharePoint 2010 lookup lists is enforced relationship. You can choose to reject delete of the item if it has been referenced elsewhere or you can have SharePoint automatically cascade deletes the referencing items as well. So, if an item of a list is referenced in another list via lookup

• Restrict delete will prevent deletion of that item since it is referenced in another list.
• Cascade delete will also delete the referencing items when this item is deleted.

So SharePoint 2010 sites can be made to hold relational data in ways similar to traditional relational database systems.

 

Walkthrough

 

Following walkthrough will demonstrate the new improvements mentioned above.

 

1) Create a list named ‘Company’ with fields ‘Company Name’, ’Address’, ’City’, ‘Country’ etc.

2) Add some company data to the list.

3) Create a list named ‘Employee’ with fields ‘Employee Name’, ‘Age’, ‘Contact’, ’Address’ etc.

4) Now since an employee is associated with a company, we will create a lookup column in ‘Employee’ list to associate an employee with a company. Create a lookup column named ‘Company’ in ‘Employee’ list, set ‘Get information from:’ attribute to ‘Company’ list, and select a field of ‘Company’ list that you would like to associate to this field.

Then check the fields of ‘Company’ list that you would like to add to the ‘Employee’ list. This lookup field will bring across all those fields of a company that has been checked. You can see that these fields are now added to the ‘Employee’ list.

5) Add an employee and select a company from the lookup list. You will now see additional fields of ‘Company’ list projected into this list.

6) Go to edit column settings of lookup column ‘Company’ in ‘Employee’ list. You will see a section where you can for specify relationship. Check ‘Enforce Relationship Behavior’ and select ‘Restrict delete’.

7) Now delete the company from ‘Company’ list for which an employee has been added in ‘Employee’ list. This is the error message that is displayed as a result of ‘Restrict Delete’ relationship that was set it step above.

It clearly states that the item cannot be deleted because an item in ‘Employee’ list is related to this item in ‘Company’ list.

8) Now set the relationship behavior of lookup column to ‘Cascade delete’ and try to delete the same company again.

Here you will be prompted a message stating that items in ‘Empoyee’ list are linked to this item and will be deleted too.

When you click ‘OK’, both company and employee of that company will be deleted.

So here we saw new improvements in lookup lists introduced in SharePoint 2010.