Friday 9 March 2007

Creating lookup columns as a feature

This is the second article in a series aimed at explaining the process of creating a MOSS site using SharePoint features. For the full series contents, see my introduction.

Last time we looked at the process of creating some SharePoint lists using VSeWSS. Sure, creating a list is a simple end-user task using the SharePoint UI, but in some scenarios such as when your site is a highly controlled internet/WCM site (or generally anywhere where we have multiple environments for dev/QA/staging/production) this deployment technique doesn't really cut it. Instead we probably want to use something more automated and repeatable than recreating such site artifacts manually each time. SharePoint 2007 supports this with Features.

Today we'll look at creating site columns which get their data from lists (lookup columns). This is a fairly common set-up, used for things like assigning metadata to a page or performing some other classification using a restricted set of values. Often the user would select the appropriate value using a dropdown shown when creating/editing a page.

Note that a similar method is to define a site column with several CHOICE elements, as below:-



However, this doesn't offer the same functionality as retrieving the values from a list. Consider the following:-

  • lists can have multiple columns whereas a choice element is effectively just one column
  • lists can have item-level permissions
  • lists can have events, workflow, versioning etc etc.

So it's clear many scenarios are better served from a site column which gets it's data from a list.

Now, when creating site artifacts as a feature, the developer will typically construct the definition in CAML, or allow VSeWSS to do this for him/her. Sometimes however, it's just not possible to do what you want with CAML. In these cases, the solution is to use a feature receiver. This is a class in compiled code (hopefully you still remember this ;-)) in which you override some methods and use the SharePoint API to define what should happen when the feature gets activated.

So why is it not possible to create a lookup column with CAML? After all, the following fragment successfully creates a site column which gets it's data from the list with the GUID specified in the 'List' attribute:-

<field id="{ae8e6ab8-b151-4fa4-8694-3cd24ad3b1bc}" type="Lookup" displayname="Locations" required="FALSE" list="{853CEC87-259E-47CA-97A7-42630F882FB7}" showfield="LinkTitleNoMenu" unlimitedlengthindocumentlibrary="FALSE" group="COB Metadata" sourceid="{8c066b26-5a3e-4e1b-85ec-7e584cf178d7}" staticname="Locations" name="Locations">

The answer is because list GUIDs are not deterministic. When a list gets created, whether through the UI, CAML or the API, it's GUID is assigned by SharePoint. There is no way to create a list with a GUID you have assigned. And if a list gets a new GUID each time it's created, this means it will have a different GUID in each of your dev/QA/staging/production environments. If this is your scenario suddenly that CAML fragment isn't so useful. Using this technique I would have to update the list GUID in my site column's <field> element and rebuild my site column feature every time the list got deployed to a new environment (or even redeployed). Clearly, this isn't pretty since, in addition to the extra effort, you're no longer deploying the exact same thing to live that has been tested in staging.

Hence I'd suggest any artifacts which reference a list should not refer to it in a declarative CAML. A better idea is to dynamically retrieve the list's GUID using the API (i.e. in a feature receiver), and create your site column in code. Briefly, the technique I use is this:

  • define site column in CAML using the fragment above
  • in my feature receiver, read this XML into memory
  • replace the list GUID with the real value retrieved from the API
  • create the site column using SPWeb.Fields.AddFieldAsXml(sXml) where sXml is the XML <field> element as a string
So this is kind of a cross between creating the site column in CAML and creating it in code. This gives a certain amount of flexibility since other properties of the column can be changed without having to recompile the code (simply change the value in the CAML, next time the feature runs it will read the modified values).

Note one other thing you are likely to need to do is to set the LookupWebId property on the column. This allows your column to be used in different sites in your site collection, yet still correctly reference the same list in your (for example) root site.

I'll post some sample code to do this in forthcoming post.

Assuming your CAML and the code to find the ID of your list was valid, you should see that you now have a site column which gets it's data from the list you specified when the feature is activated:-


One thing to note is that it's not really possible to delete the site column when the feature is deactivated. Generally, tidying up in this way is something you should do, but a site column cannot be deleted when it is has been provisioned on a list and has data. In this case, it's valid to not do any work on feature deactivation.

We're now well on our way to having page layouts which use content types with lookup columns. Phew! Next time, creating content types as a feature.

42 comments:

Anonymous said...

Hello Chris,
many thanks for you blog there is several day that I am looking for such a content explanation on how to create a content type with a lookp field, as well as having info why it is good to use features.

Big thanks I will wait for your sample code with a lot of impatience.

Laurent

Anonymous said...

Try to use list="Lists/PageCategoryGroups" instead of list="{853CEC87-259E-47CA-97A7-42630F882FB7}". Works fine with site collection scoped lists.

Regards,
renatasDOTlauzadisAThotmailDOTcom.

Chris O'Brien said...

Renatas, I tried your suggestion but I still can't get this to work.

The WSS SDK does say the site collection is a valid scope for a list instance, but I wasn't able to get a site column to successfully get data from the list. Interestingly VSeWSS failed when trying to deploy lists at site collection scope, so I had to create the list feature manually.

I found there are no errors at feature activation, but the 'Get information from' property of the site column is empty. Additionally, when I add the site column to a custom list, I get:

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)

What was the process of steps you went through?

Anonymous said...

Hi,

We have created some Field types using UI. Now we want to create Content Type based upon those site columns. As per the schema we need to give the ID for fieldRef. How can i get the ID for my field colums from UI. I tried the following approach for getting the ID but it give me text not the ID.

1. Go the Site Setting
2. Click on site columns
3. click on the site column name which ID i wants
4. Check the URL for FieldId but it is string. not the id.


Could you please help me out.

Chris O'Brien said...

Hi,

One way of finding the ID of a site column (field) through the UI is as follows:

- add the site column to a content type through the UI ("Add from existing columns")
- click on the link to the column on the manage content type page (ManageContentType.aspx)
- the field ID is passed in the querystring to ManageContentTypeField.aspx - the parameter name is "Fid"
- remember to convert the %7B and %7D to { and } respectively before using this ID as a FieldRef value

I would say though that if you are creating the content types using a feature, you should create the site columns as a feature too, as detailed in this post.

Hope this helps..

Ezz said...

I tried renatas suggestion and it didn't work.

Using Chris' method above I was able to recreate the lookup field correctly.

I'm using SharePoint WSS 3.0 with MOSS 2007.

Anonymous said...

Hi,

i'm trying to insert a Lookup Field as site column for a List that is stored in a different site. My Lookup Field works fine while using it inside of the Home-Portal-Site. Bit if i use it in a Subsite it doesn't work, the drop-down-menu of the lookup field is empty. I figuered out that the LookupWebId is set wrong. Is there a possibility to set this LookupWebId in the schema.xml file using CAML? Or is there an other way to buid such a kind of coross-site-lookup field.

Best regards

Chris O'Brien said...

Unfortunately the CAML schema for the 'Field' element doesn't allow you to specify a value for the LookupWebId - there is no such attribute. Hence, it will always default to the current web, which is why it works for you when creating a lookup field against a list in the current web. The only way (AFAIK) to do what you want to use the API in some way, such as the approach detailed in this article.

Cheers,

Chris.

Anonymous said...

Am I missing the sample code? This is a great article and I'd love to see the code.

Cheers,
brad

Chris O'Brien said...

Hi Brad,

The code is linked to from another article - have a look at Sample code - creating list-based site columns as a feature.

Cheers,

Chris.

Unknown said...

Thanks for the code Chris.

Content deployment fails if you use this feature.

First thing to do:
One thing that's missing (as others have reported) is the lookupWebId. It needs to be filled by the code the same way you fill the listid.
Will remove an exception (Value cannot be null. Parameter name: g at System.Guid..ctor(String g) at ...")

Secondary:
If you have a list item that's using one of these columns and try to do a content deployment it will fail.
Exception:
FatalError: Length cannot be less than zero.
Parameter name: length
at System.String.InternalSubStringWi...

I have yet to find a way around the second part.

Chris O'Brien said...

Hi Søren,

Hmm, that's disappointing there's an issue with content deployment. I didn't use this solution in a site which uses content deployment so that's interesting.

I wonder if it could be some other issue though? I amended the codeplex code with the bug fix suggested by another user - there was an issue when the field name/display name were different. Maybe your problem could be related to that earlier bug?

Also I'm confused about your comment about setting the LookupWebId. The code already sets this property no?

Cheers,

Chris.

Anonymous said...

Hi chris,

Even I am also trying to put lookup fields in my list definitions generated by microsoft solution generator.But It's not working.What should I do?whether I need to create event receiver for this?I tried giving path instead of guid.But it is showing HRESULT exception..I am using WSS 3.0

Regards
Simi

Chris O'Brien said...

Simi,

I'm not 100% sure but I don't think the Solution Generator is capable of this.

So you'll need to somehow use the API to add these columns, similar to the way I describe here.

HTH,

Chris.

Anonymous said...

Thanks for your Reply,

Simi

Anonymous said...

I am not sure i was looking the latest code.
createLookupColumn(currentWeb, sFinalCaml, sListName);

This method was taking the last parameter as column name but we are passing the listname. As exception is ignored in attemtodelete , column is always is created successfully.

During deactivation we are passing the Name but the web.Fields[] work only with displayname,index and guid and not with the name attribute. So we never delete the column during deactivation.

So i tried modifying the code to use Id during activation and deactivation which seems to be working correctly.

Regards
Subbu

Anonymous said...

Hello

In Chris' example the lookup field is always populated by a specific list. That doesn't work for me. I need a lookup column for my publishing site that always is populated from current Site's Pages list. Therefore the columns list guid is different depending on where the page containing this column is created. Please help!!

Chris O'Brien said...

Hi Egholm,

If I understand correctly I would have thought it should be entirely possible to do what you're trying to do. From my sample code, surely you just need to change:

SPList referencedList = currentWeb.Site.RootWeb.Lists["LocationsList"];

to

SPList referencedList = currentWeb.Lists["Pages"];

..and then later in the code, change the name of the column you are trying to reference?

HTH,

Chris.

Anonymous said...

Hi Chris
I believe the code you want me to change is in the featurereciever. If that is correct, this code will only be activated once, and will be set to current webs pages list. If I afterwards create a sub site the Loopup list will contain pages from the parent sites pages list. That I don't want. The Lookup list needs to contain the new sub sites pages list. Maybe I misunderstand you:o)

I've tried my scenario without using features, but only UI and it didn't work either, so maybe it isen't possible at all.

Regards
Egholm

Chris O'Brien said...

Hi Egholm,

Yes I did mean in the Feature receiver. What about having the Feature scoped at web level, and then activating the Feature on new subwebs then? Combined with my suggested code change that should work.

Also see Site definitions: custom code in the site creation process for an example of how to hook this up so that the code runs automatically whenever a new web (or site) is created.

HTH,

Chris.

Anonymous said...

Solution generator has very limited possibilities indeed. Does anyone know better tool to reverse engineer Sharepoint site definition?

Chris O'Brien said...

Hi Obieg,

That's a fair statement yes. I don't really think anybody else has something which will reliably reverse-engineer a full site. However, there are some handy bits and pieces out there. Check out Andrew Connell's WCM custom STSADM commands. These will allow you to get the Feature XML for your site columns and content types.

Cheers,

Chris.

Anonymous said...

I used the blank List attribute as suggested in the Site Column CAML then used the following code in the feature receiver to set the correct list attribute for the lookup column after I had created the list. It seems to work fine and only needs 3 lines of code.

SPFieldLookup lookupColumn = (SPFieldLookup)MySite.Fields.GetFieldByInternalName("MyLookupSiteColumnName");
lookupColumn.LookupList = MyListID.ToString();
lookupColumn.Update();

Chris O'Brien said...

Hi Chris,

That looks like it could be a good way to set the value, thanks for the post. Where does the MyListID variable come from though?

Cheers,

Chris.

Anonymous said...

I don't think so. I think error in cross site lookup

jendeeda said...

For those of you getting: HRESULT: 0x80040E07 - I was trying to add the content type to my list and was receiving this error. I was using the List= set to use the internal list name. I could get it to work using the GUID but I wanted to use the list name. Found i had to remove the ShowField parameter and everything worked. The ShowField defaults to Title anyway.

Anonymous said...

If you want to create a LookupMulti you will get an error when trying to use the column(not sure why). I just removed the ShowField from the CAML declaration to this, and it worked fine.

Field Type="LookupMulti"
DisplayName="Mylookupmulti"
Required="FALSE" List="Listname"
Mult="TRUE" Sortable="FALSE"
UnlimitedLengthInDocumentLibrary="FALSE"
Group="My Site Columns"
ID="{b844f0ac-6136-4b5c-9329-fd89498e8fec}"
SourceID="{2A6199D1-58F9-47d6-9E84-AEB2C4702FB5}"
StaticName="Mylookupmulti" Name="Mylookupmulti"

Thanks for your effort and code Chris. Great work!

Frode Magnussen

Anonymous said...

Wait, ideally a custom field type should. Like Cross Site lookup field .. we can have a field which will look up current web list.
may be that should be my weekend project

Anonymous said...

Hi Chris,

I'm trying to come up with a somewhat similar solution to this, but instead, what I'm trying to do is to include in a site definition, a list with a custom field that I've developed. The custom field is a lookup type that gets its data to a list... I'm really lost on how to achieve this, especially the one about including a custom field (as a site column) on a site definition

I hope you could discuss about it on one of your future blogs.

Thanks!

Anonymous said...

To create lookup column without having to put guid in "List" attribute of the column element u the target list instance must be created before creating the lookup column and put the URL of that list instead of a guid in the List attribute.
EX: I have a list definition name SupplierListDef and i have a list definition named OrdersListDef and i want the OrdersListDef to have a SupplierField column that is a lookup on a list instance i will create as SupplierListDef list.

I will put a column <Field name.... Type="Lookup" List="Lists/Suppliers" .../­­­>
in the OrdersListDef list definition

Note that List="Lists/Suppliers" is the URL of the Suppliers list instance that will be created....


For the example i will use a single feature.

So in the feature element manifest i will add my 2 list definition... then after i will create instance of these but u should remember that the orders list instance MUST be created after the suppliers list so that WSS can replace the URL by the guid of that instance...

Chris O'Brien said...

@JP,

Thanks for this - I've heard others have success with this but I ran into problems when I tried it (see earlier comments). It is more convenient though (no custom code) so will try again when I next have this requirement.

Thanks,

Chris.

P.S. I notice on this post that people seem to be having mixed luck with this approach - http://blogs.msdn.com/joshuag/archive/2008/03/14/add-sharepoint-lookup-column-declaratively-through-caml-xml.aspx. Will give it another go myself soon though :-)

ravi said...

Chris,
I am trying to create a lookup field which will pull values from a different site collection. I was able to do everything except the value won't save back to the document library in which the field is created. I ended up seeing that Lookupwebid won't get updated to the web with the new site collection. Is there a way I can achieve Cross Site Collection Lookup field?

Chris O'Brien said...

@Ravi,

I'm not sure exactly why you're running into the problem you're seeing, but another option could be to try SharePoint Solutions Cross Site Lookup field, which basically does the same thing as my code here.

HTH,

Chris.

kuk said...

Hi Chris I am creating a lookup field in my feature which will have data populated from a column of list that is created by another feature. To make it More Clear.
I have a feature Client which will create a client LiST in my site.

Now i have another feature named contract where client column of contract will have all the title of Client Lists Items.
How can it be done.. pLZ cam you guide me

Chris O'Brien said...

@Kuk,

The process I describe in this article could be used to accomplish this. Essentially you need to write code in your Feature's receiver - here you will get a reference to your 'client' list, obtain the list GUID, then set this in the XML as described above. You'll also need to obtain the ID of the 'client' list's parent web, and set the LookupWebID of the column you are creating to this value.

You can download the code I mentioned at http://www.codeplex.com/SP2007LookupFields - hopefully that will help you along your way.

Alternatively, as I mentioned to the previous commentor, the the SharePoint Solutions Cross Site Lookup field might also help.

Cheers,

Chris.

Anonymous said...

Hello Chris,

I am trying to use the URL solution, but I'm having no joy with it. Getting the HRESULT Exception that others have mentioned. Did you ever get back to trying that out?

Tee.

Chris O'Brien said...

@Tee,

Afraid I didn't no - would still be interested to hear a defininitive answer on that one though..

Best of luck,

Chris.

Unknown said...

Hi Chris, enjoy your posts.

If I've manually created columns and content types in Production, can I then create the columns/content types in Development as a feature using the GUID's from Production then promote them to Production?

Thanks - Dan

Chris O'Brien said...

@Dan,

More important than the Feature ID are the Field IDs and Content Type IDs. If these are the same, they will fail to create as the artifacts already exist. If you change them, creation will succeed but you will effectively be creating a new set of artifacts, not replacing the exising ones.

The only way out of this situation is to provision new fields and somehow migrate the content over to the new artifacts - not easy.

It's 100% more preferable to create artifacts as Features from day 1 if that's the route you want to take (and it typically should be in a structured solution).

HTH,

Chris.

Peter Holpar said...

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/

Chris O'Brien said...

@Peter,

Useful research, thanks. I guess the conclusion is that it can only be done declaratively (e.g. List="Lists/MyList") when the field is provisioned at the same time as the list (e.g. in the same Feature). Otherwise we have to use the API with an approach similar to this article.

Thanks again..

Chris.

Anonymous said...

Like Egholm a few posts up I also need the lookup to change depending on which site/web I'm on.

If I create a lookup field it always points to a specific list.

I need for it to point to http://localhost/pages/ when I'm in the root site of the collection or to http://localhost/subsite/pages/ when I'm in a sub site.

Can anyone come up with a solution for this? I'm quite stuck at the moment.