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.