Relationship in SharePoint Lists

Hi,

In this article we will see the new concept called relationship which was highly anticipated in SharePoint 2010 and which was lacking in MOSS 2007.

After a wait now it’s finally here with SP 2010. We can define the delete constraint over the lists. When we have a lookup column in child list pointing to a parent list and if we have defined the cascading delete constraint then we can allow deleting all child records when parent record is deleted or we can restrict deleting parent record when child record exists. This is the same behavior that we have in SQL Server delete constraint.

So let’s go and open our Employee Leave lists that we created for our earlier posts. If you have not gone through it, I would recommend you go through it to understand more concepts of SP 2010.

Go to list settings – Click on Employee ID lookup field which is pointing to Employee parent list’s Employee ID field and scroll down to find this.

Do remember that to enforce the constraint on field, that field must be indexed. If it’s not, SharePoint will prompt you and index it for you.



Here we have put in a restriction on delete that means if we go and try deleting record from Employee List; it should not allow us to do so. So let’s try that out.

Go to Employee List, and delete any existing record that has lookup item exist in Employee Leave list and see what you get.




Now define the same constraint of Employee Projects that we have in our site. And now try to delete item from Employee List and see what you get.



So what happens is when you have multiple lists having lookup reference to the parent lists, error indicates you list name one by one, not all lists at once. I mean here we should be expecting that error should say that item cannot be deleted because an item in the employee leave and Employee Projects list is related to the Employee list. But then this can be a cumbersome effort because we never come to know that how many lists are referring lookup.

Well, but we now at least have this functionality available ready.

If you have defined two lists which have lookup reference to the parent list and if one list allows cascading delete and one do not, then parent list item will not be deleted because one list does not allow cascading delete. And this is what it should be.

When every list allows cascading delete then when you delete item from the parent list, it delete all related records from all related lists which have lookup field referenced to the parent list field. It also actually shows you the names of all lists which have the association while you are deleting the parent item. Check this out.




It is showing Employee Projects as well as Employee Leave, click on Ok to delete all related items in child lists as well as parent item.

I hope now you will have a fair idea about the new relationship behavior functionality in SharePoint 2010.