SharePoint Lookups with Lists in Different Sites (Cross-Site Lookup)

The Challenge

In SharePoint, you have the ability to create a column using the Lookup field data type which will allow you to build a relationship in to another List.  This gives you one-to-many and many-to-many relationships.  The challenge most people have with this feature is its perceived ability to link only Lists that reside in the same Site.

The Strategy

The strategy for solving this is to use Site Columns and Content Types.  You can create a Site Column in one Site and use it in a child-site.  The Lookup Site Column in the parent site will reference a List in that Site and be associated with a List Content Type that is created in a child-site.  Refer to the solution below for a step-by-step example.

Overview

The solution below uses the basic Site Column and Content Type features that are available out-of-the-box.  Below is an overview of the steps that will be taken.

1. Create a Projects Site.
   1. Create a Project Portfolio Item Content Type.
   2. Create a Project Portfolios List and associate the Project Portfolio Item Content Type to it.
   3. Create a Site Column, of the Lookup type, that points to the Project # in the Project Portfolios list.
2. Create a Project Site; used to manage a Project.
   1. Create a new Project Tasks List.
   2. Add the Project Number Site Column that was created in the parent Site.

 
Figure 1 – Example Site Structure for Managing IT Projects

The Solution

The first step in this solution requires you to create a Projects Site, a new Content Type and a few metadata columns.  I recommend giving this a try in a “sandbox” before you put in production.

Note – This example assumes the site structure as found in Figure 1.  The steps below will start by creating a Projects Site under the IT Site.

Follow the steps below to accomplish this…

Create a Projects Site

1. Create a new Site using Projects for the Title and projects for the URL; you can provide optional information for the other fields, but it is not necessary for this example solution; see Figure 2 below.

The Projects site is the main landing and parent to all project specific collaboration sites.

Note – When in a MOSS 2007 environment, I personally recommend using the Publishing Site template for department sites and site such as Projects used in this example.  This provides you with a feature rich publishing environment where more than one page can be managed; more on this philosophy in another article.

2. Press the Create button.
3. If you used the Publishing Site template, you can publish the site itself by clicking the Publish button on the Page Editing Toolbar.
   

Figure 2 – Create a Project Site.

Create a Project Portfolio Item Content Type

1. On the Projects site, click the Site Actions -> Site Settings -> Modify All Site Settings menu option.
2. On the Site Settings page, in the Galleries section, click the Site content types link.
3. On the Site Content Type Gallery page, click the Create link.
4. On the New Site Content Type page, in the Name and Description section, Name the Content Type Project Portfolio Item.
5. For the Content Type Description, type New Project Portfolio Item.
6. For the Parent Content Type, select List Content Types from the Select parent content type from dropdown list.
7. For the Parent Content Type, select Item from the dropdown list.
8. In the Group section, select New and type IT Custom Content Types for the new group name.

Your screen should now look similar to Figure 3 below.

Figure 3 – New Project Portfolio Item Content Type.

9. Click the OK button.
10. On the Site Content Type: Project Portfolio Item page, in the Columns section, click the Add from new site column link.
11. On the New Site Column: Projects page, in the Name and Type section, enter a Column name of Project #.
12. Leave the data type as Single line of text.
13. Scroll down to the Group section, select New group and enter a name of IT Custom Site Columns.
14. Leave all other values at their defaults.
15. Click the OK button.

Your screen should now look similar to Figure 4 below.

Figure 4 – Project Portfolio Item Content Type

It is important to note that under normal circumstances, I would include additional metadata columns to support the requirements of a “real” Project Portfolio item.  This would include information such as Start Date, Expected Completion Date, Project Managers, Project Team Members, and Description and so on.  I have kept this simple as to demonstrate the concept of this articles purpose.

Create a Project Portfolios Custom List
Now that we have created a new Project Portfolio Item Content Type, we need to assign it to a list and use it!

1. Navigate back to the Projects Site by clicking the Projects link in the breadcrumb.
2. On the Projects site, above the Quick Launch bar, click the View All Site Content link.
3. On the All Site Content page, click the Create link.
4. On the Create page, in the Custom Lists section, click the Custom List link.
5. On the New page, in the Name and Description section, enter a list Name of Project Portfolios.
6. For the Description, enter Portfolios for all IT technical projects.
7. Click the Create button.
8. On the Project Portfolios list page, click the Settings -> List Settings menu option.
9. On the Customize Project Portfolios page, in the General Settings section, click the Advanced settings link.
10. On the List Advanced Settings: Project Portfolios page, in the Content Types section, click Yes to Allow management of content types.
11. Click the OK button.
12. On the Customize Project Portfolios page, in the Content Types section, click the Add from existing site content types link.
13. On the Add Content Types: Project Portfolios page, select the IT Custom Content Types group.
14. Add the Project Portfolio Item Content Type.
15. Click the OK button.

Add Items to the Project Portfolios List
Now that we have created the new Project Portfolios custom list, we need to add an item or two.  For the purposes of this article, it doesn’t matter what project information you add but you may find it of benefit to add at least two new portfolio items.

Figure 5 – Project Portfolios List with Projects Added

Creating a New Project Number Site Column
Now that we have a Project Portfolios list, with a couple of items added, we need to create a Site Column that references the Project # metadata column.  This new Site Column is the key to linking lookup lists across site boundaries.

1. Click the Site Actions -> Site Settings -> Modify All Site Settings menu option.
2. On the Site Settings page, in the Galleries section, click the Site Columns link.
3. On the Site Column Gallery page, click the Create link.
4. On the New Site Column: Projects page, in the Name and Type section, Name the new Site Column Project Number.
5. For the Type, click the Lookup (information already on this site) radio button.
6. For the Group, select the existing IT Custom Site Columns.
7. In the Additional Column Settings section, select the Project Portfolios item from the Get information from dropdown list.
8. Select the Project # item from the In this column dropdown list.
9. Click the OK button.

Create a Project Sub-site
The next step in this example is to create a project sub-site.  For our example, the project site will be called MOSS 2007 Intranet Implementation and used only for the purpose of demonstrating how we can link lists that cross site boundaries.

1. From the Projects site, click the Site Actions -> Create Site menu option.
2. On the New SharePoint Site page, in the Title and Description section, enter a Title of MOSS 2007 Intranet Implementation.
3. In the Web Site Address section, enter a URL of moss2007.
4. Choose one of the Collaboration Site Templates such as Team Site or Blank Site; virtually Site Template can be used here.
   Note – It is becoming much more common to use the Publishing Site even for collaboration needs such as a project.
5. Click the Create button.

Create a Project Tasks List
Now we will create a Project Tasks list and add the Project Number metadata column that was created in the parent Projects site; this will add the cross-site boundary list lookup ability.

1. In the upper left corner, above the Quick Launch, click the View All Site Content link.
2. On the All Site Content page, click the Create link.
3. On the Create page, in the Tracking section, click the Project Tasks link.
4. On the New page, in the Name and Description section, enter a Name of Project Tasks.
5. Click the Create button.

Add the Project Name Metadata Column

1. On the Project Tasks page, click the Settings -> List Settings menu option.
2. On the Customize Project Tasks page, in the Columns section, click the Add from existing site columns link.
3. On the Add Columns from Site Columns: Project Tasks page, select the IT Custom Site Columns group.
4. Add the Project Number column.
5. Click the OK button.
6. At this point, you can rearrange the metadata columns as deem appropriate for your needs; see Figure 6 below.

Figure 6 – Project Tasks List with a Project Number Lookup

Testing Our New Solution
You can now test the new solution by adding a few items to the Project Tasks list.  Notice, the Project Number lookup data is being derived from the Project Portfolios list in the parent site; i.e. crossing a site boundary!

Where Can We Go From Here?
The example given above does have some limitations.  Project Portfolios are managed in a Projects site that is a child of IT.  The only way you can implement this type of cross-site boundary list linking is to create all of your IT specific projects sites below the Projects site itself.  If you wish to deliver this same type of functionality in a much more global manner, i.e. across all projects in your organization, I would recommend a custom field type.  You can implement a custom field and make it available to all sites and have it reference a Project Portfolio list on any site in your environment.  Where would this type of implementation get you?  You could manage a single list of all projects across your entire organization; thus making it easier to obtain consistent metrics.

Conclusion
Site Columns (metadata) and Content Types are extremely powerful when you think of different ways they can be used.  The approach described in this article is commonly used for managing global lists of Divisions, Departments, and Products and virtually anything else you need!