Sharepoint lookup field in list

A lookup field takes its value from a field in another list. The list that provides data for the lookup field is called the lookup list. To get a reference to the lookup list, access the SPFieldLookup object's LookupList property. To determine which field in the lookup list is providing information, access the LookupField property.

Although the SPFieldLookup class does have a constructor, it is often more convenient to call the AddLookup method of the SPFieldCollection class. The AddLookup method has an overload that you can use to create a lookup to a list in a different Web site from the one where you are creating the SPFieldLookup object.

Microsoft SharePoint Foundation supports multiple-column lookups. In this case, a primary column establishes the relationship with the lookup list by pointing to a field in the target list, and one or more secondary columns point to other fields in the target list. You can create the lookup field for a secondary column by calling the AddDependentLookup method of the SPFieldCollection class. For a secondary column, the IsDependentLookup property returns true. For a primary column, IsDependentLookup returns false and the IsRelationship property returns true.

SharePoint Foundation supports referential integrity for list items by making it possible for lookup fields to set deletion constraints on the lookup list. You do this through the RelationshipDeleteBehavior property, which takes one of the following SPRelationshipDeleteBehavior enumeration values:

Cascade . Deleting an item from the lookup list causes all related items to be deleted from the list that contains the lookup field.

Restrict . Prevents an item on the lookup list from being deleted if related items exist on the list that contains the lookup field.

None . No constraint (the default).

In order to specify either Cascade or Restrict, the user must have SPBasePermissions.ManageLists permission on the lookup list. In addition, both Cascade and Restrict require that the lookup field be indexed. Before setting the RelationshipDeleteBehavior property to either of these values, first set the Indexed property to true.

using System;

using Microsoft.SharePoint;

namespace RelatedLists
{
class Program
{
static void Main(string[] args)
{
using (SPSite siteCollection = new SPSite("http://localhost"))
{
using (SPWeb site = siteCollection.OpenWeb())
{
SPList lookupList = site.Lists.TryGetList("Customers");
SPList relatedList = site.Lists.TryGetList("Pending Orders");

if (lookupList != null && relatedList != null)
{
// Create the primary column.
string strPrimaryCol = relatedList.Fields.AddLookup("Customer ID", lookupList.ID, true);
SPFieldLookup primaryCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strPrimaryCol);

primaryCol.LookupField = lookupList.Fields["ID"].InternalName;
primaryCol.Indexed = true;
primaryCol.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;
primaryCol.Update();


// Create the secondary columns.

string strFirstNameCol = relatedList.Fields.AddDependentLookup("First Name", primaryCol.Id);
SPFieldLookup firstNameCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strFirstNameCol);
firstNameCol.LookupField = lookupList.Fields["First Name"].InternalName;
firstNameCol.Update();

string strLastNameCol = relatedList.Fields.AddDependentLookup("Last Name", primaryCol.Id);
SPFieldLookup lastNameCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strLastNameCol);
lastNameCol.LookupField = lookupList.Fields["Last Name"].InternalName;
lastNameCol.Update();

string strPhoneCol = relatedList.Fields.AddDependentLookup("Phone", primaryCol.Id);
SPFieldLookup phoneCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strPhoneCol);
phoneCol.LookupField = lookupList.Fields["Phone"].InternalName;
phoneCol.Update();
}
}
}
Console.Write("\nPress ENTER to continue...");
Console.ReadLine();
}
}
}

The above code will creates a relationship between the Customers list and the Pending Orders list. The application calls the AddLookup method to add a primary lookup field named Customer ID to the Pending Orders list and points the field at the ID field on the Customers list. The new Customer ID field is indexed and set to restrict deletions from the lookup list.

After creating the primary lookup field, the application creates three secondary fields named First Name, Last Name, and Phone. The application creates these fields by calling the AddDependentLookup method of the object that represents the fields collection of the Pending Orders list.

Interpreting  the value of a lookup field.


using System;
using Microsoft.SharePoint;

namespace ConsoleApp
{
class Program
{
static void Main(string[] args)
{
using (SPSite site = new SPSite("http://localhost"))
{
using (SPWeb web = site.RootWeb)
{
SPList list = null;
foreach (SPList webList in web.Lists)
{
if (webList.BaseType == SPBaseType.Issue)
{
list = webList;
break;
}
}
if (list != null)
{
Guid fieldId = Guid.Empty;
foreach (SPField field in list.Fields)
{
if (field is SPFieldLookup)
{
fieldId = field.Id;
break;
}
}

if (fieldId != Guid.Empty)
{
SPFieldLookup lookupField = list.Fields[fieldId] as SPFieldLookup;
Console.WriteLine("Lookup field: {0}", lookupField.Title);

foreach (SPListItem item in list.Items)
{
object rawValue = item[fieldId];
if (rawValue != null)
{
Console.WriteLine("\nItem: {0}. {1}", item.ID, item.Title);
Console.WriteLine("Raw value: {0}", rawValue);

if (lookupField.AllowMultipleValues)
{
SPFieldLookupValueCollection values = item[fieldId] as SPFieldLookupValueCollection;
foreach (SPFieldLookupValue value in values)
{
PrintValue(value);
}
}
else
{
SPFieldLookupValue value = new SPFieldLookupValue(rawValue.ToString());
PrintValue(value);
}
}
}
}
}
}
}
Console.Write("\nPress ENTER to continue...");
Console.ReadLine();
}

public static void PrintValue(SPFieldLookupValue value)
{
Console.WriteLine("Lookup item ID: {0}", value.LookupId);
Console.WriteLine("Lookup item value: {0}", value.LookupValue);
}
}
}