Relational Lists in SharePoint 2010

Posted Monday, October 19, 2009 5:10 PM by CoreyRoth

In WSS3, there really was no supported way to do relational lists.  Most of us just used lookup columns and we sort of got by.  Of course there was no included support for maintaining referential integrity unless we included it ourselves.  SharePoint 2010 really takes this up a notch with some nice new features.  It also starts with the use of Lookup Columns.

When you go to create a lookup column, you will see that there are a few new options.  We still set the list we want (sorry no cross-site support in the UI still) and a value field.  However, there is a new option to maintain referential integrity at the bottom of the page.  Here it gives us the option to cascade delete from the parent list to the child list or to simply prevent the delete if any child items still exist. 

That’s not all though!  Often times in the child list, we would like to see data from the parent list.  For example, we’re viewing invoices and we want to see the actual name of the custom on the invoice and not just the Id.  In the pervious version, this required us to implement custom code to query the list and use LINQ or whatever to make a join.  Fyi, CAML now supports joins, but you probably don’t care because you’ll never use it again due to LINQ to SharePoint.  What is very cool is that it displays a list of site columns from the parent list and will show them on the child list for use in views, etc.  This makes it very easy to produce a good experience to the user out of the box when working with this type of data.


# re: Relational Lists in SharePoint 2010

Tuesday, November 10, 2009 8:36 AM by Al Kizyma


I'm in the process of deploying an Access 2007 application on a sharepoint 3.0 site.  However I am concerned about the lack of referential integrity and join type and how this will impact my data down the road.

Any suggestions?



# re: Relational Lists in SharePoint 2010

Tuesday, November 10, 2009 8:56 AM by CoreyRoth

If you are migrating your application from Access to SharePoint today, it is true that there is no out-of-the-box solution for maintaining referential integrity or for doing joins.  You can get around this some, by writing some ItemEventReceivers that enforce the integrity for you but its still not an ideal situation.  As for the joins you can write two separate CAML queries and join the results together with LINQ.  Again, not really ideal.

SP2010 helps this a bunch because it does have support for maintaining integrity and the query syntax now supports joins.  

# re: Relational Lists in SharePoint 2010

Monday, March 1, 2010 9:52 AM by Paolob

Do relational lists in Sharepoint server 2010 preserve all relational constraints? For example is it possible to define a primary key composed by 2 or more columns?

I'm searching also if it is possible, when you insert a new item in a list,  filter combo data considering the value set in another field. For exaple if you select the item "Canada" in the first combo box, is it possible to have a second combo box that updates it's data to match canadian towns?

Finally, the new feature that permits to see data from the parent list is available only in views or also when you insert a new item in a list?

Leave a Comment