How to: Create a Multiple-Column Lookup
Applies to: SharePoint Foundation 2010
In Microsoft SharePoint Foundation, a lookup column is a column that is configured to display a value from a column on another list. When users create a new item, they do not type a value for the lookup column. Instead, they select a value from a drop-down list of the values in the column on the other list. In SharePoint Foundation 2010, you can create a multiple-column lookup where a primary lookup column is used to select an item on the target list and one or more secondary lookup columns show values from other columns in the same list item.
The target of a lookup column can be one of the following field types: Counter, DateTime, Number, Text. For more information about these field types, see the SPFieldType enumeration. In addition, the Calculated field type can be a target if the output is text. For more information, see the OutputType property of the SPFieldCalculated class. The Computed field type can be a target if lookups are enabled for the field. For more information, see the EnableLookup property of the SPFieldComputed class.
The way in which you create a multiple-column lookup depends on whether you want to define columns for a new list or add columns to an existing list. In either case, you will most likely create the columns in the context of a Feature. For information about implementing a Feature, see Using Features in SharePoint Foundation.
Creating Lookup Columns on a New List
When you create lookup columns on a list that you are also creating as part of the same Feature, the easiest way to define the columns and the list is to write declarative XML. The SharePoint development tools in Microsoft Visual Studio 2010 include several templates that do some of the work for you. The templates also build a solution package for your Feature, automatically deploy the package when you press F5, and retract the solution when you stop debugging. This makes it very easy to conduct an iterative development process.
You can define primary and secondary lookup columns as part of the definition for a new list (that is, as part of the List Schema). However, the best practice is to create site columns, include them in a site content type, and use the content type as the basis for a new list. This approach promotes reusability because you can use the columns on other lists. It also enables you to maintain and update the column definitions independent of the list definition.
Before you begin, you must gather some information about the list that will be the target of the lookup columns. First, you must know how to identify the target list. A lookup column, as is the case with any column, is defined by a Field element in an XML file. The target list for a lookup column is identified by the value of the List attribute on the Field element. The attribute value can be either a list ID (a GUID) or a web-relative URL. The second option, a web-relative URL, works only when the target list does not yet exist. In this case, you must create the target list in the same Feature as the one that creates the lookup column.
If the target list already exists, you can get its ID by writing a simple console application that gets a reference to the SPList object that represents the list and prints the value of the ID property to the console. The value that you want is returned by the ToString("B") method of the System.Guid class.
using (SPSite site = new SPSite("https://localhost"))
{
using (SPWeb web = site.RootWeb)
{
SPList list = web.Lists.TryGetList("Contoso Cutomers");
if (list != null)
Console.WriteLine(list.ID.ToString("B"));
}
}
You can also get the ID by using Microsoft SharePoint Designer 2010. Open the website, select Lists and Libraries in the navigation pane, right-click the name of the list, and then select List Settings. Look for List ID under List Information. Copy the entire GUID, including braces.
In addition, you will need the internal name of each field in the target list that one of your lookup fields will point to. The internal name is the value of the Name attribute on the Field element if you are looking at the field definition, or the value returned by the InternalName property of the SPField object if you are gathering your information by writing code.
Tip
You can find the internal names for built-in columns by browsing the documentation for the SPBuiltInFieldId class. The name of each field in the class is the internal name of a built-in column. For example, the internal name of the Created By field is Created_x0020_By.
The general procedure for creating a multiple-column lookup on a new list involves the following tasks:
Define the lookup columns as site columns.
Define a content type that uses the lookup columns.
Define a list and list instance based on the new content type.
Deploy, activate, and test the Feature.
To define lookup columns as site columns
In Visual Studio 2010, create a new project using the Empty SharePoint Project template for SharePoint 2010.
In the SharePoint Customization Wizard, select Deploy as a sandboxed solution, and then click Finish.
In Solution Explorer, right-click the Features folder, and then select Add Feature.
The Feature designer appears. You can edit the title and description, and you can select a scope for the Feature. Either Web or Site is an appropriate scope for this application. For more information, see Element Scope.
(Optional) Create a target list for the lookups.
To create the target list from a custom list definition, follow the procedures that are described in How to: Create a Custom List Definition. To create the target list from an existing list definition, follow these steps:
In Solution Explorer, select the project, and on the Project menu, select Add New Item.
In the Add New Item dialog box, select the List Instance template, and type a name for the list. Click Add.
Follow the prompts in the SharePoint Customization Wizard.
The wizard asks you to choose a list template and to specify a URL for the list.
Create the field definition for the primary lookup column.
In Solution Explorer, click the project, and on the Project menu, select Add New Item.
In the Add New Item dialog box, select the Empty Element template, type a name for the element (for example, "SiteColumns"), and then click Add.
Visual Studio uses the name that you type as the name of a folder that it adds to the project. Inside the folder, Visual Studio creates a file named Elements.xml.
In Elements.xml, insert the following XML inside the Elements element:
<!-- Primary lookup column --> <Field Type="Lookup" Name="" ID="" List="" ShowField="" DisplayName="" Required="" Group="" />Type a value for the Name attribute.
This is the internal name of the field. The name must be unique within the collection that is returned by the AvailableFields property of the website where this field will be created. SharePoint Foundation amends the value if necessary to guarantee its uniqueness.
In Visual Studio, on the Tools menu, select Create GUID. In the Create GUID dialog box, select Registry Format, click Copy, and then click Exit.
A new GUID is copied to the clipboard.
Specify a value for the ID attribute by pasting the new GUID (including braces) between the quotation marks.
Specify a value for the List attribute.
The List attribute identifies the lookup column's target list. If this list already exists, the value is the list ID, a GUID (including braces).
If the target list does not yet exist, you can use a web-relative URL (such as Lists/Contoso Customers) as the value of the List attribute. However, you must create the list as part of the same Feature that creates the lookup column. (See Step 4.) Use the value of the Url attribute on the ListInstance element that creates the target list.
Specify a value for the ShowField attribute.
The value of the ShowField attribute is the internal name of the target field on the list identified by the List attribute.
Specify a value for the DisplayName attribute.
This is the name that is displayed for the column in list views and on forms. The maximum length is 255 characters. The value can be a reference to a localized resource in the format $Resources:String. For more information, see Localizing SharePoint Solutions.
Set the Required attribute to either true or false.
Specify true if users who are completing the New or Edit form for a list item should be required to select a value for the field. Specify false if the field can be empty.
(Optional). Specify a value for the Group attribute.
The Group attribute specifies the name of a group in which to place the column when it is listed in the Site Columns gallery. If a group with that name does not yet exist, it is created. Defining a group for columns that you create can make them easier to maintain.
Create the field definition for a secondary lookup column.
In Elements.xml, insert the following XML inside the Elements element:
<!-- Secondary lookup column --> <Field Type="Lookup" FieldRef="" Name="" ID="" List="" ShowField="" DisplayName="" Required="" Group="" ReadOnly="TRUE" />Specify a value for the FieldRef attribute.
This attribute references the field definition for the primary lookup column. Use the value of the primary lookup's ID attribute, but omit the braces.
Specify values for the remaining attributes as in Step 5.
Create field definitions for each of the remaining secondary lookup columns in your Feature by repeating Step 6 as many times as necessary.
At this point, your field definitions should resemble the following example, which defines a multiple-column lookup to the Contoso Customers list.
<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="https://schemas.microsoft.com/sharepoint/">
<!-- Primary lookup column: CustIDLookup -->
<Field Type="Lookup"
Name="CustIDLookup"
ID="{2A670022-84AF-4C51-84F7-8E1B6025F104}"
List="Lists/Contoso Customers"
ShowField="ID"
DisplayName="Customer ID"
Required="true"
Group="Contoso Columns"/>
<!-- Secondary lookup column: CustFirstNameLookup -->
<Field Type="Lookup"
FieldRef="2A670022-84AF-4C51-84F7-8E1B6025F104"
Name="CustFirstNameLookup"
ID="{F0AF2CA5-EA71-46F7-9536-036539BA8C5C}"
List="Lists/Contoso Customers"
ShowField="FirstName"
DisplayName="First Name"
Required="false"
Group="Contoso Columns"
ReadOnly="TRUE" />
<!-- Secondary lookup column: CustLastNameLookup -->
<Field Type="Lookup"
FieldRef="2A670022-84AF-4C51-84F7-8E1B6025F104"
Name="CustLastNameLookup"
ID="{1D7973CE-49CD-46BD-9355-81014246578D}"
List="Lists/Contoso Customers"
ShowField="Title"
DisplayName="Last Name"
Required="false"
Group="Contoso Columns"
ReadOnly="TRUE" />
<!-- Secondary lookup column: CustPhoneLookup -->
<Field Type="Lookup"
FieldRef="2A670022-84AF-4C51-84F7-8E1B6025F104"
Name="CustPhoneLookup"
ID="{0F51E81B-2001-4A52-B33A-0F401814118B}"
List="Lists/Contoso Customers"
ShowField="HomePhone"
DisplayName="Phone"
Required="false"
Group="Contoso Columns"
ReadOnly="TRUE" />
</Elements>
To define a site content type
In Solution Explorer, click the project, and on the Project menu, select Add New Item.
In the Add New Item dialog box, select the Content Type template, type a name for the content type (for example, "CustomerOrder"), and then click Add.
The Choose Content Type Settings dialog box appears.
Select a base content type to inherit from. Click Finish.
Visual Studio uses the name that you typed as the name of a folder to add to the project. Inside the folder is a file named Elements.xml. This is where you define the content type.
Open the Elements.xml file in the editor.
Note
You have the correct file open if it contains an Elements element that contains a ContentType element that contains a FieldRef element.
(Optional) Visual Studio sets the Inherits attribute on the ContentType element to TRUE. If you intend to override the characteristics of any inherited fields (for example, by changing the display name), set the value of the Inherits attribute to FALSE.
Reference the primary lookup column by pasting the following XML inside the FieldRefs element:
<FieldRef ID="" Name="" DisplayName="" Required=""/>Set the ID attribute and the Name attribute to the same values as those attributes have on the Field element that defines the primary lookup column.
The ID attribute is case-sensitive, so make sure that you copy the value exactly as it is on the Field element. Be sure to include the braces.
Specify values for the DisplayName attribute and the Required attribute.
Repeat Steps 6–8 for each secondary lookup column.
Repeat Steps 6–8 for any inherited columns whose characteristics you want to modify.
For more information, see How to: Reference a Column in a Content Type.
Your completed content type definition should resemble the following example.
<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="https://schemas.microsoft.com/sharepoint/">
<!-- Parent ContentType: Item (0x01) -->
<ContentType ID="0x0100b4160be88d9a4d669f690459bdcf43ff"
Name="Contoso - Order"
Group="Contoso Content Types"
Description="Represents a customer order"
Inherits="FALSE"
Version="0">
<FieldRefs>
<FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" DisplayName="Description" Required="TRUE"/>
<FieldRef ID="{bc91a437-52e7-49e1-8c4e-4698904b2b6d}" Name="LinkTitleNoMenu" DisplayName="Description" ReadOnly="TRUE" />
<FieldRef ID="{82642ec8-ef9b-478f-acf9-31f7d45fbc31}" Name="LinkTitle" DisplayName="Description" ReadOnly="TRUE"/>
<FieldRef ID="{2A670022-84AF-4C51-84F7-8E1B6025F104}" Name="CustIDLookup" DisplayName="Customer ID" Required="TRUE"/>
<FieldRef ID="{F0AF2CA5-EA71-46F7-9536-036539BA8C5C}" Name="CustFirstNameLookup" DisplayName="First Name" Required="FALSE"/>
<FieldRef ID="{1D7973CE-49CD-46BD-9355-81014246578D}" Name="CustLastNameLookup" DisplayName="Last Name" Required="FALSE"/>
<FieldRef ID="{0F51E81B-2001-4A52-B33A-0F401814118B}" Name="CustPhoneLookup" DisplayName="Phone" Required="FALSE"/>
</FieldRefs>
</ContentType>
</Elements>
To define a list and list instance
In Solution Explorer, click the project, and on the Project menu, select Add New Item.
In the Add New Item dialog box, select the List Definition from Content Type template, type a name for the list (for example, "OrdersListDefinition"), and then click Add.
The Choose List Definition Settings dialog box appears.
Select the content type that includes the lookup columns, select the Add a list instance for this list definition check box, and then click Finish.
Visual Studio uses the name that you typed as the name of a folder to add to the project. Inside the folder is a file named Elements.xml that contains a ListTemplate element; a file named Schema.xml that contains a List element; and a folder named ListInstance1 that contains another Elements.xml file with a ListInstance element.
Open the Schema.xml file.
Notice that the ContentTypes element contains a copy of your content type definition, and the Fields element has copies of the field definitions for your lookup columns.
Scroll down to the Views element. Notice that two views are defined, but neither view contains FieldRef elements that reference your lookup columns.
Copy the FieldRef elements for any columns that you want to be rendered in a list view, and paste them inside the ViewFields element of the view's definition.
Only the ID attribute and the Name attribute are required on FieldRef elements inside a ViewFields element.
The Views element of your Schema.xml file should look something like the following example.
<Views>
<View BaseViewID="0" Type="HTML" MobileView="TRUE" TabularView="FALSE">
<Toolbar Type="Standard" />
<XslLink Default="TRUE">main.xsl</XslLink>
<RowLimit Paged="TRUE">30</RowLimit>
<ViewFields>
<FieldRef Name="LinkTitleNoMenu"></FieldRef>
<FieldRef ID="{2A670022-84AF-4C51-84F7-8E1B6025F104}" Name="CustIDLookup" />
<FieldRef ID="{F0AF2CA5-EA71-46F7-9536-036539BA8C5C}" Name="CustFirstNameLookup" />
<FieldRef ID="{1D7973CE-49CD-46BD-9355-81014246578D}" Name="CustLastNameLookup" />
<FieldRef ID="{0F51E81B-2001-4A52-B33A-0F401814118B}" Name="CustPhoneLookup" />
</ViewFields>
<Query>
<OrderBy>
<FieldRef Name="Modified" Ascending="FALSE">
</FieldRef>
</OrderBy>
</Query>
<ParameterBindings>
<ParameterBinding Name="AddNewAnnouncement" Location="Resource(wss,addnewitem)" />
<ParameterBinding Name="NoAnnouncements" Location="Resource(wss,noXinviewofY_LIST)" />
<ParameterBinding Name="NoAnnouncementsHowTo" Location="Resource(wss,noXinviewofY_ONET_HOME)" />
</ParameterBindings>
</View>
<View BaseViewID="1" Type="HTML" WebPartZoneID="Main" DisplayName="$Resources:core,objectiv_schema_mwsidcamlidC24;" DefaultView="TRUE" MobileView="TRUE" MobileDefaultView="TRUE" SetupPath="pages\viewpage.aspx" ImageUrl="/_layouts/images/generic.png" Url="AllItems.aspx">
<Toolbar Type="Standard" />
<XslLink Default="TRUE">main.xsl</XslLink>
<RowLimit Paged="TRUE">30</RowLimit>
<ViewFields>
<FieldRef Name="Attachments">
</FieldRef>
<FieldRef Name="LinkTitle">
</FieldRef>
<FieldRef ID="{2A670022-84AF-4C51-84F7-8E1B6025F104}" Name="CustIDLookup" />
<FieldRef ID="{F0AF2CA5-EA71-46F7-9536-036539BA8C5C}" Name="CustFirstNameLookup" />
<FieldRef ID="{1D7973CE-49CD-46BD-9355-81014246578D}" Name="CustLastNameLookup" />
<FieldRef ID="{0F51E81B-2001-4A52-B33A-0F401814118B}" Name="CustPhoneLookup" />
</ViewFields>
<Query>
<OrderBy>
<FieldRef Name="ID">
</FieldRef>
</OrderBy>
</Query>
<ParameterBindings>
<ParameterBinding Name="NoAnnouncements" Location="Resource(wss,noXinviewofY_LIST)" />
<ParameterBinding Name="NoAnnouncementsHowTo" Location="Resource(wss,noXinviewofY_DEFAULT)" />
</ParameterBindings>
</View>
</Views>
To deploy, activate, and test the Feature
Press F5 to deploy the solution and activate the Feature.
On the Site Actions menu, select Site Settings.
Under Galleries, click Site columns. Verify that the primary lookup column exists in the site columns collection.
Click the name of your primary lookup column. On the Change Site Column page, under Add a column to show each of these additional fields, verify that the names of the secondary lookup columns are selected.
Go back to the Site Settings page. Under Galleries, click Site content types.
Click the name of your content type. On the Site Content Type Information page, under Columns, verify that the primary lookup column is listed.
In the left navigation pane, click the name of your list.
On the ribbon, click List, and then click List Settings.
On the List Settings page, under Columns, verify that the primary and secondary lookup columns are listed.
Return to the list view. Try adding a new item to the list. (You might have to add items to the target list first.)
When you are finished, close the browser to stop debugging.
Visual Studio deactivates the Feature and retracts the solution.
Next Steps
A complete Feature should remove objects that it has created when the Feature is deactivated. Visual Studio tries to do as much of this as it can in order to smooth the rough edges around an iterative development process, but in a production environment your Feature is expected to clean up after itself. To do that, you must write some code.
The correct place to put cleanup code is in the FeatureDeactivating method of a subclass of the SPFeatureReceiver class. How you proceed here depends somewhat on internal policy. For example, you probably do not want to delete lists if they contain valuable data. In that case, you must provide a way to store the data so that users can recover it. Apart from this sort of consideration, you should consider the following general guidelines when you write the code for the FeatureDeactivating method:
Delete list content types before deleting the site content type from which they are derived.
You can call the static method SPContentTypeUsage.GetUsages to get information about where a content type is being used. For an example, see the Delete method in the SPContentTypeCollection class.
Delete site content types before deleting the site columns that they reference.
Delete all secondary lookup columns before deleting the primary lookup column on which they depend.
For an example, see the implementation of the FeatureDeactivating method at the end of the next section, "Adding Lookup Columns to an Existing List."
Log exceptions and failures so that an administrator can troubleshoot if necessary.
Adding Lookup Columns to an Existing List
You can add lookup columns to a list that already exists by writing server code that uses the SharePoint Foundation object model. The best way to do this is to create a Feature and write the code in an event receiver for the Feature. Your code creates the lookup columns when the Feature is activated.
Note
The following procedure assumes that the list that is the target of the lookup columns exists in the same website as the list that contains the lookup columns. Cross-web lookups are possible. For more information, see the LookupWebId property.
To add a multiple-column lookup to an existing list
In Visual Studio 2010, create a new project using the Empty SharePoint Project template for SharePoint 2010.
In the SharePoint Customization Wizard, select Deploy as a sandboxed solution. Click Finish.
In Solution Explorer, right-click the Features folder, and select Add Feature.
The Feature Designer opens. You can edit the title and description, and you can select a scope for the Feature. Either Web or Site is an appropriate scope for this application. For more information, see Element Scope.
Right-click the feature name (by default, Feature1), and then select Add Event Receiver.
Visual Studio creates a subclass of the SPFeatureReceiver class.
Uncomment the FeatureActivated method.
In the FeatureActivated method, write code to get either the current website (if the scope of the Feature is Web) or the root website of the site collection (if the scope is Site).
You can do this by accessing the Feature property of the SPFeatureReceiverProperties object that is passed to the method as the properties parameter. The Feature property returns an SPFeature object. The Parent property of this object returns an SPWeb object boxed as type Object if the Feature's scope is Web or a boxed SPSite object if the scope is Site.
The following code example demonstrates the technique.
SPWeb web = properties.Feature.Parent as SPWeb; if (web == null) { SPSite site = properties.Feature.Parent as SPSite; if (site != null) web = site.RootWeb; }Dim web As SPWeb = TryCast(properties.Feature.Parent, SPWeb) If web Is Nothing Then Dim site As SPSite = TryCast(properties.Feature.Parent, SPSite) If site IsNot Nothing Then web = site.RootWeb End If EndInstantiate two SPList objects, one for the list that will receive the lookup columns, and another for the list that is the target of the lookup columns.
For example, the following code example creates objects that represent the Contoso Orders list and the Contoso Customers list.
SPList customers = web.Lists.TryGetList("Contoso Customers"); SPList orders = web.Lists.TryGetList("Contoso Orders");Dim customers As SPList = web.Lists.TryGetList("Contoso Customers") Dim orders As SPList = web.Lists.TryGetList("Contoso Orders")Instantiate SPField objects to represent the columns that will be the targets of the lookup columns.
For example, the following code instantiates objects to represent four columns on the Contoso Customers list that will be targets of lookup columns on the Contoso Orders list.
SPField custID, custLName, custFName, custPhone; try { custID = customers.Fields.GetField("ID"); custFName = customers.Fields.GetField("First Name"); custLName = customers.Fields.GetField("Last Name"); custPhone = customers.Fields.GetField("Contact Phone"); } catch (ArgumentException ex) { // One of the fields was not found on the Customers list. System.Diagnostics.Trace.WriteLine(logEntry + ex.Message); return; }Dim custID, custLName, custFName, custPhone As SPField Try custID = customers.Fields.GetField("ID") custFName = customers.Fields.GetField("First Name") custLName = customers.Fields.GetField("Last Name") custPhone = customers.Fields.GetField("Contact Phone") Catch ex As ArgumentException ' One of the fields was not found on the Customers list. System.Diagnostics.Trace.WriteLine(logEntry + ex.Message) Return End TryCreate the primary lookup column by calling the AddLookup method of the SPFieldCollection object that is returned by the Fields property of the list that will receive the lookup columns.
The AddLookup method has three parameters:
displayName - A string that contains the display name for the lookup column.
lookupListId - A GUID that is the ID of the target list.
bRequired --- A Boolean that indicates whether users must select a value for the lookup column on New and Edit forms.
The method returns a string that contains the internal name of the lookup column. You can retrieve an object that represents the new column by passing the internal name to the GetFieldByInternalName method.
For example, the following code creates a lookup column on the Contoso Orders list that has a display name of Customer ID and targets the Constoso Customers list.
string strPrimary = orders.Fields.AddLookup("Customer ID", customers.ID, true);Dim strPrimary As String = orders.Fields.AddLookup("Customer ID", customers.ID, True)Set the LookupField property of the primary lookup column so that it points to a column on the target list.
You can retrieve an SPField object that represents the new primary lookup column by passing the internal name returned by the AddLookup method as an argument to the GetFieldByInternalName(String). Then cast the SPField object as type SPFieldLookup before accessing the LookupField property. This property accepts a string that contains the internal name of the target field.
The following code sets the LookupField property of the column that was created in the example for the previous step.
SPFieldLookup primary = orders.Fields.GetFieldByInternalName(strPrimary) as SPFieldLookup; primary.LookupField = custID.InternalName; primary.Update();Dim primary As SPFieldLookup = TryCast(orders.Fields.GetFieldByInternalName(strPrimary), SPFieldLookup) primary.LookupField = custID.InternalName primary.Update()Create the secondary lookup columns by calling the AddDependentLookup method of the field collection that belongs to the list that will have the lookup columns.
The AddDependentLookup method has two parameters:
displayName - A string that contains the display name for the lookup column.
primaryLookupFieldId - A GUID that is the ID of the primary lookup column.
Like the AddLookup method, the AddDependentLookup method returns a string that contains the internal name of the new column.
The following line of code creates a Last Name column that is dependent on the primary lookup column that was created in a previous example.
string strLName = orders.Fields.AddDependentLookup("Last Name", primary.Id);Dim strLName As String = orders.Fields.AddDependentLookup("Last Name", primary.Id)Set the LookupField property of the new secondary lookup column by following the same procedure as in Step 10.
Repeat Steps 10–11 for each secondary lookup column that you want to add.
(Optional.) Add one or more lookup columns to list views by calling the Add method of the SPViewFieldCollection object that is returned by each view's ViewFields property.
Example
The following example shows the complete code for a subclass of the SPFeatureReceiver class that creates and deletes lookup columns on the Contoso Orders list. Code in the FeatureActivated method creates a primary lookup column named Customer ID that looks up the value of the ID column on the Contoso Customers list. The code then creates secondary lookup columns for First Name, Last Name, and Phone, and it adds the secondary columns to the default view on the Contoso Orders list.
The example also includes code for the FeatureDeactivating method, which deletes all the lookup columns that are created in the FeatureActivated method. Note that you must delete all secondary lookup columns before you delete the primary lookup column on which they depend.
using System;
using System.Runtime.InteropServices;
using Microsoft.SharePoint;
namespace Contoso.Orders.Features.CustomerLookups
{
[Guid("49e72c4b-cfcb-4665-9c2a-fdadf0a3b018")]
public class CustomerLookupsEventReceiver : SPFeatureReceiver
{
// Define static field names.
const string STATIC_CUST_ID = "CustID";
const string STATIC_CUST_FNAME = "CustFName";
const string STATIC_CUST_LNAME = "CustLName";
const string STATIC_CUST_PHONE = "CustPhone";
public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
string logEntry = properties.Definition.DisplayName + " activation error: ";
// Get an object representing either the current website (if the scope is Web)
// or the root website (if the scope is Site).
SPWeb web = properties.Feature.Parent as SPWeb;
if (web == null)
{
SPSite site = properties.Feature.Parent as SPSite;
if (site != null)
web = site.RootWeb;
}
if (web == null)
{
System.Diagnostics.Trace.WriteLine(logEntry + "Invalid scope");
return;
}
// Get objects for the two lists.
SPList customers = web.Lists.TryGetList("Contoso Customers");
SPList orders = web.Lists.TryGetList("Contoso Orders");
if (customers == null || orders == null)
{
System.Diagnostics.Trace.WriteLine(logEntry + "List not found");
return;
}
// Get the fields from the Customers list that will be seen on the Orders list.
SPField custID, custLName, custFName, custPhone;
try
{
custID = customers.Fields.GetField("ID");
custFName = customers.Fields.GetField("First Name");
custLName = customers.Fields.GetField("Last Name");
custPhone = customers.Fields.GetField("Contact Phone");
}
catch (ArgumentException ex)
{
// One of the fields was not found on the Customers list.
System.Diagnostics.Trace.WriteLine(logEntry + ex.Message);
return;
}
/*
* Create a multi-column lookup on the Orders list.
*/
// Create the primary column: Customer ID.
string strPrimary = orders.Fields.AddLookup("Customer ID", customers.ID, true);
SPFieldLookup primary = orders.Fields.GetFieldByInternalName(strPrimary) as SPFieldLookup;
primary.LookupField = custID.InternalName;
primary.StaticName = STATIC_CUST_ID;
primary.Update();
// Create a secondary column: First Name.
string strFName = orders.Fields.AddDependentLookup("First Name", primary.Id);
SPFieldLookup ordersFName = orders.Fields.GetFieldByInternalName(strFName) as SPFieldLookup;
ordersFName.LookupField = custFName.InternalName;
ordersFName.StaticName = STATIC_CUST_FNAME;
ordersFName.Update();
// Create a secondary column: Last Name.
string strLName = orders.Fields.AddDependentLookup("Last Name", primary.Id);
SPFieldLookup ordersLName = orders.Fields.GetFieldByInternalName(strLName) as SPFieldLookup;
ordersLName.LookupField = custLName.InternalName;
ordersLName.StaticName = STATIC_CUST_LNAME;
ordersLName.Update();
// Create a secondary column: Phone.
string strPhone = orders.Fields.AddDependentLookup("Phone", primary.Id);
SPFieldLookup ordersPhone = orders.Fields.GetFieldByInternalName(strPhone) as SPFieldLookup;
ordersPhone.LookupField = custPhone.InternalName;
ordersPhone.StaticName = STATIC_CUST_PHONE;
ordersPhone.Update();
// Add columns to the default view.
AddToDefaultView(orders, strFName);
AddToDefaultView(orders, strLName);
AddToDefaultView(orders, strPhone);
}
public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
{
string logEntry = properties.Definition.DisplayName + " deactivation error: ";
// Get an object representing either the current website (if the scope is Web)
// or the root website (if the scope is Site).
SPWeb web = properties.Feature.Parent as SPWeb;
if (web == null)
{
SPSite site = properties.Feature.Parent as SPSite;
if (site != null)
web = site.RootWeb;
}
if (web == null)
{
System.Diagnostics.Trace.WriteLine(logEntry + "Invalid scope");
return;
}
// Get the Orders list.
SPList orders = web.Lists.TryGetList("Contoso Orders");
if (orders == null)
{
System.Diagnostics.Trace.WriteLine(logEntry + "List not found");
return;
}
// Delete the lookup fields.
// Note that you must delete dependent lookup fields
// before deleting the primary lookup field.
SPFieldLookup primaryLookup = orders.Fields.TryGetFieldByStaticName(STATIC_CUST_ID) as SPFieldLookup;
if (primaryLookup != null && !primaryLookup.IsDependentLookup)
{
string[] dependentLookupNames = primaryLookup.GetDependentLookupInternalNames().ToArray();
if (dependentLookupNames.Length != 0)
{
foreach (string dependent in dependentLookupNames)
{
orders.Fields.Delete(dependent);
}
}
primaryLookup.Delete();
}
}
public void AddToDefaultView(SPList list, string fieldName)
{
if (list != null && list.Fields.ContainsField(fieldName) && !list.DefaultView.ViewFields.Exists(fieldName))
{
SPView defaultView = list.DefaultView;
defaultView.ViewFields.Add(fieldName);
defaultView.Update();
}
}
}
}
Option Explicit On
Option Strict On
Imports System
Imports System.Runtime.InteropServices
Imports Microsoft.SharePoint
<GuidAttribute("c17f29fb-670e-41ba-af43-8a1e97c1cae5")> _
Public Class CustomerLookupsEventReceiver
Inherits SPFeatureReceiver
' Define static field names.
Const STATIC_CUST_ID As String = "CustID"
Const STATIC_CUST_FNAME As String = "CustFName"
Const STATIC_CUST_LNAME As String = "CustLName"
Const STATIC_CUST_PHONE As String = "CustPhone"
Public Overrides Sub FeatureActivated(ByVal properties As SPFeatureReceiverProperties)
Dim logEntry As String = properties.Definition.DisplayName + " activation error: "
' Get an object representing either the current website (if the scope is Web)
' or the root website (if the scope is Site).
Dim web As SPWeb = TryCast(properties.Feature.Parent, SPWeb)
If web Is Nothing Then
Dim site As SPSite = TryCast(properties.Feature.Parent, SPSite)
If site IsNot Nothing Then
web = site.RootWeb
End If
End If
If web Is Nothing Then
System.Diagnostics.Trace.WriteLine(logEntry + "Invalid scope")
Return
End If
' Get objects for the two lists.
Dim customers As SPList = web.Lists.TryGetList("Contoso Customers")
Dim orders As SPList = web.Lists.TryGetList("Contoso Orders")
If customers Is Nothing OrElse orders Is Nothing Then
System.Diagnostics.Trace.WriteLine(logEntry + "List not found")
Return
End If
' Get the fields from the Customers list that will be seen on the Orders list.
Dim custID, custLName, custFName, custPhone As SPField
Try
custID = customers.Fields.GetField("ID")
custFName = customers.Fields.GetField("First Name")
custLName = customers.Fields.GetField("Last Name")
custPhone = customers.Fields.GetField("Contact Phone")
Catch ex As ArgumentException
' One of the fields was not found on the Customers list.
System.Diagnostics.Trace.WriteLine(logEntry + ex.Message)
Return
End Try
'
' Create a multi-column lookup on the Orders list.
'
' Create the primary column: Customer ID.
Dim strPrimary As String = orders.Fields.AddLookup("Customer ID", customers.ID, True)
Dim primary As SPFieldLookup = TryCast(orders.Fields.GetFieldByInternalName(strPrimary), SPFieldLookup)
primary.LookupField = custID.InternalName
primary.StaticName = STATIC_CUST_ID
primary.Update()
' Create a secondary column: First Name.
Dim strFName As String = orders.Fields.AddDependentLookup("First Name", primary.Id)
Dim ordersFName As SPFieldLookup = TryCast(orders.Fields.GetFieldByInternalName(strFName), SPFieldLookup)
ordersFName.LookupField = custFName.InternalName
ordersFName.StaticName = STATIC_CUST_FNAME
ordersFName.Update()
' Create a secondary column: Last Name.
Dim strLName As String = orders.Fields.AddDependentLookup("Last Name", primary.Id)
Dim ordersLName As SPFieldLookup = TryCast(orders.Fields.GetFieldByInternalName(strLName), SPFieldLookup)
ordersLName.LookupField = custLName.InternalName
ordersLName.StaticName = STATIC_CUST_LNAME
ordersLName.Update()
' Create a secondary column: Phone.
Dim strPhone As String = orders.Fields.AddDependentLookup("Phone", primary.Id)
Dim ordersPhone As SPFieldLookup = TryCast(orders.Fields.GetFieldByInternalName(strPhone), SPFieldLookup)
ordersPhone.LookupField = custPhone.InternalName
ordersPhone.StaticName = STATIC_CUST_PHONE
ordersPhone.Update()
' Add columns to the default view.
AddToDefaultView(orders, strFName)
AddToDefaultView(orders, strLName)
AddToDefaultView(orders, strPhone)
End Sub
Public Overrides Sub FeatureDeactivating(ByVal properties As SPFeatureReceiverProperties)
Dim logEntry As String = properties.Definition.DisplayName + " deactivation error: "
' Get an object representing either the current website (if the scope is Web)
' or the root website (if the scope is Site).
Dim web As SPWeb = TryCast(properties.Feature.Parent, SPWeb)
If web Is Nothing Then
Dim site As SPSite = TryCast(properties.Feature.Parent, SPSite)
If site IsNot Nothing Then
web = site.RootWeb
End If
End If
If web Is Nothing Then
System.Diagnostics.Trace.WriteLine(logEntry + "Invalid scope")
Return
End If
' Get the Orders list.
Dim orders As SPList = web.Lists.TryGetList("Contoso Orders")
If orders Is Nothing Then
System.Diagnostics.Trace.WriteLine(logEntry + "List not found")
Return
End If
' Delete the lookup fields.
' Note that you must delete dependent lookup fields
' before deleting the primary lookup field.
Dim primaryLookup As SPFieldLookup = TryCast(orders.Fields.TryGetFieldByStaticName(STATIC_CUST_ID), SPFieldLookup)
If primaryLookup IsNot Nothing AndAlso Not primaryLookup.IsDependentLookup Then
Dim dependentLookupNames As String() = primaryLookup.GetDependentLookupInternalNames().ToArray()
If dependentLookupNames.Length > 0 Then
For Each dependent As String In dependentLookupNames
orders.Fields.Delete(dependent)
Next
End If
primaryLookup.Delete()
End If
End Sub
Public Sub AddToDefaultView(ByVal list As SPList, ByVal fieldName As String)
If list IsNot Nothing AndAlso list.Fields.ContainsField(fieldName) AndAlso Not list.DefaultView.ViewFields.Exists(fieldName) Then
Dim defaultView As SPView = list.DefaultView
defaultView.ViewFields.Add(fieldName)
defaultView.Update()
End If
End Sub
End Class
See Also
Reference
AddLookup(String, Guid, Boolean)
AddDependentLookup(String, Guid)