Deploying Relational Lists using CAML in SharePoint 2010

Posted Wednesday, February 10, 2010 2:46 PM by CoreyRoth

One of my favorite new features in SharePoint 2010 is the ability to have lists relate to each other and the fact that you can maintain referential integrity.  Now, I am sure there are at least 50 posts out there already that show you how to do this using the UI.  The UI is great, but you are a developer and that means you might want to actually deploy these lists elsewhere someday.  Now I have to admit, using the save site as template feature is great for moving sites and content around but you may want a more granular deployment of a pair of lists in a single feature.  The cool thing is the save site as template feature is extremely useful for discovering the underlying CAML to build things in SharePoint which is how I discovered relational lists work today.  This post has some great screenshots of how to export a site template as a wsp.

For today’s example, I have a list of categories and a list of products.  The products list has a lookup column which references the categories list.  Here is what the products list looks like.

RelationalLists

On this view, you can see that I am linking to the Categories list and I am displaying the Id and Description from that list.  Here is how my lookup column is defined.

RelationalListsLookupColumn

Creating a list with CAML in SharePoint 2010 is really not much different than in previous versions.  In order to set up this lookup column, we need to look at the Schema.xml of the Products list.  It turns out that the column that the user chooses is still a lookup column but so are the additional fields.  They just have additional attributes defined.  Let’s look at the definition of the lookup column the user interacts with first.

<Field Type="Lookup" DisplayName="Category" Required="FALSE" EnforceUniqueValues="FALSE" List="Lists/Categories" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" Indexed="TRUE" RelationshipDeleteBehavior="Cascade" ID="{c59b5e18-54c2-408a-a231-b88c3e939e90}" SourceID="{$ListId:Lists/Products;}" StaticName="Category" Name="Category" RowOrdinal="0" />

I got this schema by doing an export as mentioned above.  There are a few things you might notice here.  The list name and SourceID refer to the lists by URL instead of a hardcoded Id.  The value of Indexed is also set to true.  This is because relational lists require indexed columns.  You also get prompted to index the column when you set it up through the UI.  The last thing to note is the RelationshipDeleteBehavior attribute.  It can have a value of Cascade, Restrict, or none.  This corresponds to relationship behavior you see in the UI screenshot above.

The additional fields from the parent list are also lookup fields.   They just have a few key differences.

<Field Type="Lookup" DisplayName="Category:ID" List="Lists/Categories" WebId="8c918dd2-ef1f-4822-b3d2-f587d622d203" ShowField="ID" FieldRef="c59b5e18-54c2-408a-a231-b88c3e939e90" ReadOnly="TRUE" UnlimitedLengthInDocumentLibrary="FALSE" ID="{2264d6b5-7001-40e1-b295-236b9965c833}" SourceID="{$ListId:Lists/Products;}" StaticName="Category_x003a_ID" Name="Category_x003a_ID" />

<Field Type="Lookup" DisplayName="Category:Description" List="Lists/Categories" WebId="8c918dd2-ef1f-4822-b3d2-f587d622d203" ShowField="Description" FieldRef="c59b5e18-54c2-408a-a231-b88c3e939e90" ReadOnly="TRUE" UnlimitedLengthInDocumentLibrary="FALSE" ID="{a0ab273e-5989-42ba-950d-3add1c1025e0}" SourceID="{$ListId:Lists/Products;}" StaticName="Category_x003a_Description" Name="Category_x003a_Description" />

First, the FieldRef attribute has the Id of the first lookup field we defined.  This is what ties them together.  The second thing is that the ShowField attribute refers to the name of the field in the lookup list.  The last thing to note is that they are marked as readonly.  One more thing to point out is that the static name is encoded with x003a which is the color you see in the DisplayName.  This is also used in the FieldRef elements you will also have on any ConentType element thats uses these fields in your schema.xml.  For example.

<FieldRef ID="{c59b5e18-54c2-408a-a231-b88c3e939e90}" Name="Category" Required="FALSE" />

<FieldRef ID="{2264d6b5-7001-40e1-b295-236b9965c833}" Name="Category_x003a_ID" ReadOnly="TRUE" />

<FieldRef ID="{a0ab273e-5989-42ba-950d-3add1c1025e0}" Name="Category_x003a_Description" DisplayName="Category:Description" ReadOnly="TRUE" />

You might not see much value in this post yet, but I think you might in the future.  The tools to work with SharePoint have improved greatly, but you still will need to work with some CAML from time to time I believe.  You may not be building it from scratch any more, but it is still good to know your way around it.  As a developer I often get frustrated when I run into posts showing how to do something through the UI when I really need to know how to build something declaratively or programmatically.  Hopefully this will be helpful.

Comments

# Deploying Relational Lists using CAML in SharePoint 2010 &#8211; Corey &#8230; | Drakz Free Online Service

Pingback from  Deploying Relational Lists using CAML in SharePoint 2010 &#8211; Corey &#8230; | Drakz Free Online Service

# re: Deploying Relational Lists using CAML in SharePoint 2010

Sunday, August 22, 2010 5:58 PM by Muel

Well done for posting on this - everyone else has gone the UI route. Duly bookmarked! Thanks!

# re: Deploying Relational Lists using CAML in SharePoint 2010

Wednesday, September 22, 2010 6:11 AM by kate

great post man. this area very undocumented and a struggle to include into feature deployment. thanks

# re: Deploying Relational Lists using CAML in SharePoint 2010

Sunday, February 20, 2011 7:15 PM by Rajesh

Thanks for the nice post. I appreciate if you can provide the same with progrmatic approach.(not CAML but object model)

# re: Deploying Relational Lists using CAML in SharePoint 2010

Tuesday, May 8, 2012 12:00 AM by Sakshi

Great post. I was looking for this only. Will try it now

# re: Deploying Relational Lists using CAML in SharePoint 2010

Wednesday, October 10, 2012 9:04 PM by nudier

Can you post a link where the whole project can be download

# re: Deploying Relational Lists using CAML in SharePoint 2010

Thursday, November 15, 2012 8:27 AM by ange

Thanks a lot!

Just a note: x003a is for colon, not color (typo in the text)...

Leave a Comment

(required) 
(required) 
(optional)
(required)