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 (33)

  1. Chris F. says:

    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!

  2. Joe B says:

    This doesn’t clear anything up for me … where do I put the code you specify?

    Posts that have all the keywords that make google match my searches to your content but don’t give me any answers really annoy me.

    Also are these custom fields available in the "in this column" dropdown when looking up a field from one list that resides in another.  OOTB custom fields are not available in this dropdown and that sucks.

  3. Josh G. says:

    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.

  4. Steve B says:

    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?

  5. Steve B says:

    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).

  6. Nick says:

    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)

  7. Nick says:

    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.

  8. Peter.dk says:

    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.

  9. Steve B says:

    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.  

  10. Saurabh says:

    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…..

  11. Nathan says:

    Have you tried doing this with a LookupMulti?  The list populates, but I am unable to select multiple items on the list.

  12. lzandman says:

    @Nathan: Don’t use LookupMulti. You have to leave the type to "Lookup" and add an extra attribute Mult="TRUE" in the Field declaration.

  13. Javier says:

    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.html

  14. Vadim Dzyuban says:

    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

    Vadim

  15. Tested it, doesn't work. says:

    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 ?

  16. Scott says:

    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.

  17. xx says:

    awesome!  I was ommitting the lists/ from list property once i added that it worked.

  18. Hoang Ha says:

    Thank you very much. It’s worked for me

  19. Michhes says:

    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.

    -Michhes

  20. Rafael says:

    It DOES work… neat solution! Thanks!

  21. 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"/>

    Cheers

  22. Nick says:

    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?

  23. Tulasi says:

    Hi,

    If I want to add two items of lookup field to an item how can i achieve this.

    Thanking You,

    Tulasi

  24. Perry says:

    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?

  25. joshuag says:

    I’ve fixed the code so it now wraps if the browser window isn’t big enough.  Hope this helps you out!

  26. Phil says:

    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 added

  27. Perry says:

    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.

  28. Tom says:

    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. Thanks

  29. I FIGURED IT OUT!!! says:

    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…

    1) I have two list definitions: Tickets and TicketStatus

    2) Tickets has a Lookup field called "Status" referring to the TicketStatus list

    3) 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.

    4) So, in my Features.xml file, I purposely reversed them:

        <ElementManifests>

    <ElementFile Location="TicketStatus/Schema.xml"/>

    <ElementFile Location="Tickets/Schema.xml"/>

    </ElementManifests>

    5) 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. 🙂

  30. maddy says:

    thanks for the amazing post. I have been digging from long on this..Finally ur idea worked..ty..

  31. cherry says:

    British reporters are known for doing almost anything to get a Mulberry Bags. But reports that a newspaper secretly listened to telephone messages of murdered schoolgirls and other private citizens have produced Mulberry Handbags and anger.

    On Friday, British police arrested Andy Coulson, former editor of Mulberry Bag Britain's best-selling newspaper, News of Mulberry Outlet the World. The investigation led him to Mulberry UK Sale resign in January as communications director to Prime Minister David Cameron.

    The arrest came in a widening investigation of Mulberry UK telephone hacking. Other accusations include paying police for mulberry shoulder bags information on stories. The Reuters news agency reported that Mr. Coulson was released on Mulberry Bag UK until a date in October.

    Prime Minister Cameron promised Men's Mulberry Bags Friday that a judge will lead a full public inquiry into Women's Mulberry Bags the case after police complete their investigation.

    DAVID CAMERON: "Murder victims, terrorist victims, families who have lost loved ones, sometimes defending our country, that these people could have had their phones hacked into, in order to generate stories for Mulberry Bags  Mulberry Handbags  Mulberry Bag   Mulberry Outlet  Mulberry UK  mulberry bayswater bag  Mulberry Alexa Bag, is simply disgusting."

Skip to main content