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

  1. jaloplo says:

    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.

  2. Paal Amundsen says:

    You can also just use the id:

    listItem["ProductLookUp"] = 5;

  3. Garth says:

    Hi,

    I had a strange error where it worked… then didn’t work (thought I was going crazy).

    I was creating a file item in a document library and was also checking the file in (by default it was checking it out to me which was undesirable)

    2 hrs of my life which I’ll never have back later I found out it was because I was checking an item in AFTER the lookup whereas in the first attempt I’d checked it in before ;). For some reason this was overwriting the value so it always returned blank.

    ###### This was WRONG:  

    myFile.Item["MyLookupField"] = new SPFieldLookupValue(3, "Product Name");

    myFile.CheckIn("Checkin Message", SPCHeckinType.MajorCheckIn);

    ###### This was RIGHT:  

    myFile.CheckIn("Checkin Message", SPCHeckinType.MajorCheckIn);

    myFile.Item["MyLookupField"] = new SPFieldLookupValue(3, "Product Name");

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

  5. Ganapati says:

    Hi, thank you a lot Sridhar. You have solved my problem and saved enough time.

  6. Prabhakar says:

    Hi Sridhar,

    I have published an InfoPath form into a document library. I want to add items dynamically to it. I used your adding a new item code. But it generates following error:

    To add an item to a document library, use SPFileCollection.Add()

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: Microsoft.SharePoint.SPException: To add an item to a document library, use SPFileCollection.Add()

    How to go about it. Please help me.

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

    Sridhar

  8. Chris Sammt says:

    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 Sammut

  9. Naresh says:

    Great help!!!!

    You saved a life…..thanks man…thanks a lot

  10. Omer Leshem says:

    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_M

  11. James Zheng says:

    Very inspirational…!

    Thank you very much for your esteemed work!

  12. Khan says:

    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/sharepoint

  13. Carla Cardoso says:

    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 ME

  14. Naveen Jangir says:

    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?

  15. David D. says:

    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!

  16. Brandon Clark says:

    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!

  17. Viraj Vashi says:

    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 picker

  18. tejukaturi says:

    Can u tell how to add multiple values in a field.I mean how should we mention more than 1 Id?

  19. Sudheera says:

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

    Thnaks

  20. RKC says:

    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.

  21. Ankur says:

    Thanks a lot for such a great article..Keep tht going….:)

  22. dominique says:

    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

    Dom

  23. Sigeg P says:

    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.

  24. Max Jayapaul says:

    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.