Add SharePoint lookup column declaratively through CAML XML
UPDATE: In the comments I’ve seen some people saying that this doesn’t work for them. One thing that I discovered recently is that, in order for this to work, the URL specified in your schema.xml file MUST MATCH the List attribute on all your Field references in field.xml and schema.xml. If these don’t match, it will not work. Hope that helps the folks that were having trouble.
I've seen a lot of statements recently that say the only way to add a lookup column through a feature is through code. There are even some interesting solutions around this using Feature Receivers out on codeplex here and here. When I first started hearing these statements I didn't believe it, so I decided to try to build a feature using only CAML that will create a list with a lookup column. As it turns out this isn't really that difficult, but it did take some digging. When you are doing this there are two place that you have to be concerned about. The first is when defining the column as a site column. When you define content types and custom list schemas, you should first start with your site columns. I usually place these in a file called fields.xml, this is also what you will see if you take a look through the out of the box list schemas. Here is an example of one of my site columns of the Lookup type
<Field ID="{2FF1B484-6D70-449c-8E5C-904E4D5971E1}" Name="Leader" Group="My Custom Columns" Type="Lookup" DisplayName="Leader" List="Lists/Leaders" ShowField="Title" PrependId="TRUE"/>
There are three properties here that are not found on a standard site column definition. The first is "List", this, as you might have guessed, points to the URL of the list in the site. In this example I know I am looking for the Title column in a list that is found under Lists/Leaders. The next property to pay attention to is "ShowField", you guessed it, this is the field that you want to show in your lookup column. The last property is "PrependId". I'm not 100% sure on this but I think this has something to do with whether the lookup column returns the id in front of the value. The out of the box columns set this to "TRUE" and I do like to get back my item ID with my value so I set it to TRUE as well. Disclaimer: I never tested what setting it to "FALSE" does.
The next thing you will want to do is add this column to a custom list schema that you define. If you aren't familiar with custom list schemas Ted Pattison's book Inside WSS 3.0 is a great place to get familiar with this. Quickly summarized, whenever you create a custom list, there must be a schema.xml file in a folder that is the same name as the list name. That sounds kind of confusing, but describing how to build custom list schemas is out of the scope of this post. Ted's book explains this really well however, and if you are a SharePoint 2007 developer and haven't read this yet, go buy it. Anyway, in the custom list schema one of the first sections is called "Fields". In this section, if I wanted to add my custom lookup site column to my custom list I would use the following line of CAML.
<Field ID="{2FF1B484-6D70-449c-8E5C-904E4D5971E1}" Name="Leader" Type="Lookup" DisplayName="Leader" List="Lists/Leaders" ShowField="Title"/>
Again, if you haven't worked with this stuff before this might seem kind of redundant. Truthfully it is, but that is just how custom list schemas work, so get used to it. Really, that is all there is to it, everything else you want to create (content types, list templates, list instances) is all the same for lookup columns as it is a standard text column.
Hope that helps to clear up some confusion around lookup columns.
Comments
Anonymous
March 24, 2008
Yay! Thanks for the timely post. I really needed to do this same thing, but I was quite discouraged after reading Bil Simser's blog post from a while back about how he couldn't figure this out. The point your blog post makes that I would probably not have thought to try is that the "List" attribute of the lookup <Field> element is the url of the list and not (as the WSS SDK docs say) the name of the list. Two thumbs up. Thanks again!Anonymous
April 10, 2008
The comment has been removedAnonymous
April 10, 2008
Joe B. This XML should be in your fields.xml file in your custom list feature definition. This is a lookup column, lookup columns specifically exist to "lookup" values from another list. My use of this column was getting values from a completely custom list. I'm sorry that this didn't give you everything you were looking for, if you can post with some more specific questions maybe I can be of more help? Hope that helps.Anonymous
May 12, 2008
I tend to agree with your opening comment - I can't quite believe the feature doesn't allow you to provision a Lookup Field without faffing about with feature receivers. However, I've been trying this stuff and not got very far, despite using the relative URL to the new List Instance I've created. I haven't got the source code to hand right now to post here, but should the List attribute be site relative or web application relative? For instance, my site collection uses a managed path - do I need to include this as well?Anonymous
May 12, 2008
Ok - forget my last post. I've also managed to provision the field using the CAML definition. To confirm a couple of points: I entered a dummy value for the List attribute and received an error - on looking at the code where the error was raised I confirmed for myself that the List attribute is resolved when you enter a list name. That was the first step for me as I now knew it was possible using CAML. After I returned the List attribute value to show my List Name I then had other problems - the field was provisioned but no lookup was set up so it meant the field was still broken. I pared down the contents of the field definition and was left with the same as Josh mentions above with the ShowField also there. I left ShowField in as I wanted to use the Title field. But, it appears this was the problem. Removing the ShowField attribute (I could not use Title was the link but would have had to use the Title-with-link column instead) fixed it (it defaults to the Title-with-link field).Anonymous
June 03, 2008
Hi I am really surprised to see your post since I was searching for a better solution for the same problem. But when I tried your technique, I am getting the following error. Plase let me know where I am making mistake. Exception from HRESULT: 0x80040E07 at Microsoft.SharePoint.Library.SPRequestInternalClass.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd) at Microsoft.SharePoint.Library.SPRequest.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)Anonymous
June 03, 2008
just putting my element file contents for the reference here... <Field ID="{2FF1B484-6D70-449c-8E5C-904E4D5971E1}" Name="Master" Group="Custom Columns" Type="Lookup" DisplayName="Master" List="Lists/Master" ShowField="Title" PrependId="TRUE"/> I have 2 lists named Master and Child and error is getting when I try to add this custom site column to my "child" list. I tried modifying the ID attribute also.Anonymous
June 14, 2008
Get te same error as Nick.....but it works like a dream when I put the guid of the list instead of the lists/mylist in the list attribute.Anonymous
June 16, 2008
Further to my last post, it looks like caching had been playing around here and gave the impression that it worked ok. I used the Feature on a separate machine and i did not work. I provisionsed the Field ok, but the connectivity to the require List is just not available. So, basically, it just does not work. Using a GUID works without problem, using a URL never works. Although what I posted previously was correct, i.e. the stack trace of the error I got did seem to indicate that the List Name would be resolved, but it hasn't worked like that in practice.Anonymous
July 10, 2008
No Gaffey......It doesn't work for me.......also.....I am using the exact same thing......only one change...... <Field ID="myNewGUID" Name="TestName" Group="Test Group" Type="Lookup" DisplayName="TestDisplayName" List="Lists/Tests" ShowField="ID" PrependId="TRUE"/> but it doesn't show any thing......it shows blank....dropdown....whereas my List has 10 Items.....Anonymous
August 07, 2008
Have you tried doing this with a LookupMulti? The list populates, but I am unable to select multiple items on the list.Anonymous
August 08, 2008
@Nathan: Don't use LookupMulti. You have to leave the type to "Lookup" and add an extra attribute Mult="TRUE" in the Field declaration.Anonymous
September 11, 2008
Hi, i get the same error. The problem is that the GUID changes each time the a list is created. Check this out: http://www.sharepointnutsandbolts.com/2007/04/creating-list-based-site-columns-as.htmlAnonymous
March 30, 2009
Does anybody know if is there any way to provision a lookup field and a target lookup list on a separate web apps (not separate sites/site collections) using caml, object model or their combination ? I need to provision the same lookup field on multiple user's sites and the data in this lookup should be retrieved from a source lookup list administered on separate web app. It seems that sharepoint does not allow to do that ? Thanks VadimAnonymous
April 15, 2009
hmmm, I've tested as following this article, but it can't bind to the list with the name...is anyone make it works without the list's GUID ?Anonymous
June 05, 2009
To make this method work, the field definition must exist in a list definition since the wiring up to the GUID happens when you the provision the list, rather than when you add a column to an existing content type/list.Anonymous
June 05, 2009
awesome! I was ommitting the lists/ from list property once i added that it worked.Anonymous
June 17, 2009
Thank you very much. It's worked for meAnonymous
July 27, 2009
Just echoing your updated text since I missed it when I first skim-read this post: "the URL specified in your schema.xml file MUST MATCH the List attribute on all your Field references in field.xml and schema.xml. If these don’t match, it will not work" I made these match up and everything worked. -MichhesAnonymous
August 18, 2009
It DOES work... neat solution! Thanks!Anonymous
September 11, 2009
Here is an example of the custom lookup column as I added it to a custom list schema <Field Type="LookupMulti" Required="FALSE" List="Lists/LookUp" ShowField="Title" Name="myLookUp" DisplayName="myLookUp" Mult="TRUE" Sortable="True"/> CheersAnonymous
October 20, 2009
I want to add the lookup site column to a content type. The example states how to add it to a list definition but does not seem to work for adding the column to a content type defined in the package. Does the content type have to be defined in a feature that activates after the feature that contains the lookup column?Anonymous
January 18, 2010
Hi, If I want to add two items of lookup field to an item how can i achieve this. Thanking You, TulasiAnonymous
February 11, 2010
The code is wrapped in pre tags, and is too wide for the narrow blog column, and is hidden behind the template stuff on the right. Is there an easy way to see all of the offscreen code?Anonymous
February 12, 2010
I've fixed the code so it now wraps if the browser window isn't big enough. Hope this helps you out!Anonymous
February 15, 2010
Thanks for the post it worked for me when I added the list's guid instead of the url. I also added a reference to my content type xml file and my lists schema xml file, so that I could use it with existing custom lists that needed updating. fields.xml <Field ID="GUID" Name="LookupColumn" DisplayName="Lookup Column" Group="MY Site Columns" Type="Lookup" List="List GUID" ShowField="ColumnName" PrependId="TRUE" /> ctp.xml <FieldRef ID="Site Column GUID" Name="SiteColumnName"/> Schema.xml <Fields> <Field ID="Site column GUID" Name="Site Column Name" DisplayName="Site Column Display name" Group="My Site Columns" Type="Lookup" List="List GUID" ShowField="Lookup Column Name" PrependId="TRUE" /> </Fields> <ViewFields> <FieldRef Name="Site Column Name" /> </ViewFields> There are 2 <ViewFields> sections in the schema.xml file where your column reference needs to be addedAnonymous
May 05, 2010
If you are deploying a site column, and if you have a feature receiver running, it seems that you can also set the WebID property of the SPFieldLookup, and then it can be used from subwebs as well.Anonymous
May 13, 2010
I've also played a bit with the "Lists/ListName" form. You can read about the results here: http://pholpar.wordpress.com/2010/05/14/declaratively-adding-a-lookup-field-to-a-list-schema-using-the-name-of-the-referenced-list/Anonymous
September 19, 2010
I recommend to try <a href="http://sharepointfields.com">http://sharepointfields.com</a http://sharepointfields.comAnonymous
November 05, 2010
What if I have not yet created my target list? Can I still say List = "Lists/TestList" when I have yet to create TestList? I need to do this because I have 2 lists that each point to the other. Hence, one lookup column needs to be defined when its target list has yet to be created. ThanksAnonymous
April 22, 2011
joshuag: you may want to add this to your update as well... For me, the URL was EXACTLY the same in both files. However, the Lookup still wasn't working. So, I thought I'd try something. Based on the List Definition still needing to be created first so that SharePoint knew what its GUID was, I had a hunch and I was correct. Here's the deal...
- I have two list definitions: Tickets and TicketStatus
- Tickets has a Lookup field called "Status" referring to the TicketStatus list
- But, notice something...VS2010 lists them in alphabetical order and this is how they are deployed by default to SharePoint. So Tickets definition was being created BEFORE the TicketStatus definition and therefore, could not receive a good GUID.
- So, in my Features.xml file, I purposely reversed them: <ElementManifests> <ElementFile Location="TicketStatus/Schema.xml"/> <ElementFile Location="Tickets/Schema.xml"/> </ElementManifests>
- This creates the TicketStatus list definition FIRST, and now the Ticket's Status lookup field has been assigned the correct GUID. Everything works like gravy. :)
Anonymous
May 02, 2011
thanks for the amazing post. I have been digging from long on this..Finally ur idea worked..ty..Anonymous
October 16, 2011
The comment has been removed