Update "lookup" fields in SharePoint 2007
Lookup fields were innovative feature offered in SharePoint Portal Server 2003 that eased design/development efforts in terms of providing an interface that allows users to pick up dynamically changing values.
Consider a retail store scenario, where users would order products by creating new items in a SharePoint list. The product catalog will usually be dynamic. Look up field can help in such cases by providing a ready-to-use control and taking care of referential integrity between the field and source list.
However, lookup fields in SharePoint 2003 did not offer very many programmability options (well, practically none). Lookup field cannot be updated or added, assigning value to a lookup field programmatically would fail with reason that it's readonly.
I guess it's because of the following possible reasons:
- The relationship between the source and the lookup field isn't maintained in a traditional database referential integrity way, but it's logically designed deep inside one of the tables in SharePoint content database.
- If it is not exposed as a "readonly" property, it might break the relationship and thus causing unwanted behavior.
- There's no pull and push of data here, but just a "lookup", which means if you change your source list, the values in the lookup field will change accordingly. This probably is the most important design requirement and so other options were removed (i.e., updating lookup field value).
Though there are reasons to explain, it was a cause of concern for many a developers.
However, in MOSS 2007, things with respect to lookup fields have changed. With all due respect, I personally feel it's still a bit messy and it could have been better, but with small effort, lookup fields can be added/modified programmatically.
The class MOSS offers is SPFieldLookupValue. This will help in adding/updating list items that have one or more of their fields as lookup fields.
The below code snippet is just an enumeration of a list that provides data to lookup field.
string strResult = string.Empty;
SPSite site = new SPSite("<sharepoint_url>");
SPWeb web = site.OpenWeb();
SPListItemCollection listItems = web.Lists["sourceList"].Items;
foreach(SPListItem listItem in listItems)
{
strResult += listItem["ID"].ToString() + ";#" +
listItem["Product Names"].ToString() + System.Environment.NewLine;
}
tbresults.Text = strResult;
The output of the above is:
1;#A
2;#B
3;#C
4;#D
5;#E
Lookup fields are internally referenced as a combination of ID & field value in the above format. I presented it in this format so that it would be easy to correlate with what it is represented internally. Coming to the method in question: SPFieldLookupValue, takes 2 values - (int)lookupId & (string)lookupValue. And quite obviously, it's the source field's ID & value. Below code snippet shows how to update a lookup field's value programmatically.
string result = string.Empty;
SPSite site = new SPSite("<sharepoint_url>");
SPWeb web = site.OpenWeb();
SPListItemCollection listItems = web.Lists["listwithlookupfield"].Items;
foreach (SPListItem listItem in listItems)
{
if (listItem["Quantity"].ToString() == "100")
{ // update Product lookup field
result += "Current value: " + listItem["ProductLookUp"].ToString() + System.Environment.NewLine;
listItem["Product"] = new SPFieldLookupValue(1, "A");
listItem.Update();
result += "Updated value: " + listItem["ProductLookUp"].ToString();
}
}
tbresults.Text = result;
Adding a new item also works the same way. A sample below:
string result = string.Empty;
SPSite site = new SPSite("<sharepoint_url>");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["listwithlookupfield"];
SPListItem listItem = list.Items.Add();
listItem["Title"] = "testLookupDirect"
listItem["ProductLookUp"] = new SPFieldLookupValue(5,"E"); // add a new lookup field referencing the id & value present in the source list
listItem["Quantity"] = (int)200;
listItem.Update();
tbresults.Text = listItem.UniqueId.ToString();
As seen above, the parameters lookupid & lookupValue represents the id & value of the source list. Just did a test on what happens when a lookupid & lookupValue combination that's not present in the source is provided programmatically. Since the property (meaning the lookupid;#lookupvalue combination) is stored as is and retrieved when needed, any new combination like 20;#test that is not present in the source list is simply ignored. The code completes without errors, however, the values against the lookup field is empty.
Just thought of blogging this information as there were several enquires on this. If you find a better way of adding/updating lookup fields, do let me know.
Get the latest SharePoint Server 2007 SDK and explore the plethora of APIs and sample available in it.
Comments
Anonymous
August 25, 2007
PingBack from http://msdnrss.thecoderblogs.com/2007/08/25/update-quotlookupquot-fields-in-sharepoint-2007/Anonymous
September 02, 2007
Hi Sridhar, This is a good article and was the start point of a difficult problem I was in. But, I think there is another elegant way to do this, implementing SPFieldLookupValue and SPFieldLookupValueCollection objects.Anonymous
September 03, 2007
You can also just use the id: listItem["ProductLookUp"] = 5;Anonymous
October 17, 2007
The comment has been removedAnonymous
November 23, 2007
Great Job Sridhar's You also have different behavior with a multi-select lookup or a people field. Ive written something about that here http://apichot.blogspot.com/2007/06/sharepoint-2007-champs-lookup-et-people.htmlAnonymous
July 28, 2008
Hi, thank you a lot Sridhar. You have solved my problem and saved enough time.Anonymous
July 28, 2008
The comment has been removedAnonymous
July 28, 2008
Hi Prabhakar, You should do what the error message tells you to do... An items list is different from document library list. Form Library is similar to document library in SharePoint. You should use the SPFileCollection.Add() method instead. You can refer to the latest SDK for insight/example on using this API. Cheers, SridharAnonymous
August 20, 2008
Hi, I wish to add a lookup field to data from a list on another list which is on another site... Is this possible? thank you Kind regards Chris SammutAnonymous
October 16, 2008
Great help!!!! You saved a life.....thanks man...thanks a lotAnonymous
November 19, 2008
The way to deal with this problem in SP 2003 is to just set the item's ID in the lookup cell. listItem["FieldName"] = itemID; 'itemID' represents the row number of the item from the external list. Here is a list of contacts: ID - Name 1 - Omer 2 - Leshem 3 - Lesh_M This is an item from another list, the list has LookUp field that's binded to the contacts' names. listItem["Contact"] = 3; // Lesh_MAnonymous
January 10, 2009
Very inspirational...! Thank you very much for your esteemed work!Anonymous
February 04, 2009
Hi Sridhar ! Thanks for the excellent post !! Can you tell me if there is a way in infopath where we can update(or add a new item) to a custom list which contains 'People or group' type column without using custom C# code. I mean by settings the infopath rules/data connections/sharepointAnonymous
February 05, 2009
I create the class Lookup to validate the fields of Lookup and insert them in SharePoint. When I create a document, and call this class gives me the following error to save the fields Lookup ... ERROR: Value does not fall within the expected range Class Lookup: public class WPLookUP : Indice { DropDownList dropDownList; SPFieldLookup lookup; public WPLookUP(SPFieldLookup l, string defaultValue) : base(l) { lookup = l; dropDownList = new DropDownList(); bool exist = false; SPList list; defaultValue = "Escolha uma opção"; dropDownList.Items.Add(new ListItem(string.Empty, string.Empty)); using (SPSite site = new SPSite(WebSettings.SiteUrl + WebSettings.DCSite)) { using (SPWeb web = site.OpenWeb()) { list = web.Lists.GetList(new Guid(lookup.LookupList), false); SPListItemCollection items = list.Items; foreach (SPListItem spitem in items) { ListItem item = new ListItem(); string value = (string)spitem[lookup.Title]; SPFieldLookupValue lookupValue = (SPFieldLookupValue)lookup.GetFieldValue(value);-> Error item.Text = lookupValue.LookupValue; item.Value = lookupValue.LookupValue; item.Attributes.Add("LookupId", lookupValue.LookupId.ToString()); if (defaultValue != null) { if (defaultValue.Equals(spitem[lookup.LookupField].ToString())) { item.Selected = true; exist = true; } } dropDownList.Items.Add(item); } } } } Help MEAnonymous
June 11, 2009
Hi Sridhar, Many thanks for sharing a useful information about lookup fields. In my case i am retrieving a lookup field value in my custom search results. There it is being displayed with its ID 1;#A 2;#B 3;#C 4;#D 5;#E And i want to display only the values. Any help how i can override this beahvior(adding ID with value) of lookup field in sharepoint?Anonymous
June 21, 2009
I'm having problems with adding lookup values from javascript. The field always ends up empty. Could someone be kind enough to post a small dump of the SOAP call that is exchanged with MOSS when adding an item with lookup meta-data? I would really appreciate it!Anonymous
September 23, 2009
If I am using a lookup field in a list, how can I use the value retrieved to populate other fields in my active list. For instance, if I select a contact from another list, what is the easiest way to have it fill in the address and phone# also? Thanks!Anonymous
October 28, 2009
I have one custom list whose name is ‘Defects’ and in that custom list i have one lookup control whose name is ‘Project Name’(Which comes from Project details list) and one more column i have which is ‘Assigned To’(which is people picker). I want to display display users in ‘Assigned To’ column based on Project name column. For better understanding… Project name: Lookup control(Dropdown list) Assigned To: People pickerAnonymous
February 23, 2010
Can u tell how to add multiple values in a field.I mean how should we mention more than 1 Id?Anonymous
March 14, 2010
Hi Sridhar, I was reading your blog..regarding lookup. hey can i restrict the lookup in an alpha order or filter by [Me] because my lookup field list is very huge.. ThnaksAnonymous
September 03, 2010
Would be nice if you could lookup to a view instead of having to lookup against an entire list/library. Lotus Notes has had that option available for more than a decade.Anonymous
July 06, 2011
Thanks a lot for such a great article..Keep tht going....:)Anonymous
July 25, 2011
Hello, I am trying to populate llokup field in a SharePoint list from nvarchar (DT_STR or DT_WSTR) but apparently I fails each time... If I remove the lookup fields the SSIS package works fine... so I think it is the sysntax sent to the SharePoint List which is wrong... Do I need to use a Cast formula or a String formula to change the data sent? Thanks DomAnonymous
April 10, 2012
I think Microsoft must consider to develop similar feature of lookup fields but without storing the ID, so whatever changes happened in source table it will not affect tables that look up to the source table.Anonymous
January 30, 2014
Very useful update. I am so used to setting the lookup values directly. However I've noticed that when MS Office documents are involved and document property promotion occurs, it sometimes fails unless we set the lookup values using the SPFieldLookupValue class.