Lookup Site Column

So in my past two posts, we created the site columns, content types, list definitions and list instances to create two lists, one for departments and one for job postings. In this post we are going to bring them together using a lookup column, this is going to let our job posting list feed from the departments list so that as we update our departments they are automatically available in our job posting list.

If you haven't gone through my previous posts here are the links, they're rather trivial if you're already familiar with SharePoint, but if you're a noob like me, then here you go.

• Site Columns & Content Types
• List Definition & Instance

Let's get started with our Lookup Site column, go back to your site columns elements file and add the following:

  <Field ID ="{C0CC842C-8059-482A-AE40-0B5601FB4B60}"
         Name ="SPJ_DepartmentLookup"
         DisplayName="Department Lookup"
         Type="Lookup"
         List="Lists/Departments"
         Title="Department"
         ShowField="Title"
         OverwriteInChildScopes="TRUE"
         Overwrite="TRUE" />

With our Site Column created next we have to add it to our Job Posting Content type, so go ahead pop open the JobPosting Content type and add it to the fields:

<FieldRef ID ="{C0CC842C-8059-482A-AE40-0B5601FB4B60}" Name ="SPJ_DepartmentLookup" />

Now Deploy your Lookup.Schema to add your new lookup site column to the site columns collection and to the job posting content type. With that complete you've come to a fork in the road. you may notice that just by adding the site column to the content type it doesn't affect your list instance at all. This is because your list instance in based on your list definition which used your content type at the time it was instantiated, that is to say that the connection doesn't exist when you build a list definition it uses the content type at the time of development and subsequent changes to the content type are not reflected in the list definition.

Your options are as follows

• delete the list definition and rebuild it using your new content type
• try to modify the list definition schema

I recommend the former over the latter of the options, once you've rebuilt your list definition go into the elements file and make sure that Type property is unique to all other list definitions.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <!-- Do not change the value of the Name attribute below. 
         If it does not match the folder name of the List Definition project item, 

         an error will occur when the project is run. -->
    <ListTemplate
        Name="JobPosting_LD"
        Type="10001"
        BaseType="0"
        OnQuickLaunch="TRUE"
        SecurityBits="11"
        Sequence="410"
        DisplayName="JobPosting_LD"
        Description="My List Definition"
        Image="/_layouts/images/itgen.png"/>
</Elements>

With that complete, open up the elements file for your list instance and ensure that the value of the TemplateType property matches up with the previous type value:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ListInstance Title="Job Posting"
                OnQuickLaunch="TRUE"
                TemplateType="10001"
                FeatureId="a2c12d31-b651-4eee-9214-9f4d970d0b4a"
                Url="Lists/JobPosting"
                Description="List of all Corporate Job Postings">
  </ListInstance>
</Elements>

Now you can go ahead and deploy your Lookup.Data project, awesome great we're done now...

Unfortunately not so fast, SharePoint does this awesome thing where it gives a list instance a brand new GUID every time it's deployed, meaning that you have to rewire your lookup columns after every single deployment.

Luckily I have a script for you that will do the trick, execute the following script in PowerShell to fix your lookup column, just remember to specify your own URL and port number.

$url = "http://urlpath:port"
 
function LoadSharePointPowerShellEnviroment
{
write-host
write-host "Setting up Powershell enviroment for Sharepoint" -foregroundcolor Blue
Add-PSSnapin "Microsoft.Sharepoint.PowerShell" -ErrorAction SilentlyContinue
Write-host "Sharepoint PowerShell Snapin loaded." -foregroundcolor Green
}

try
{
    LoadSharePointPowerShellEnviroment
 
    $RootSite = new-object Microsoft.SharePoint.SPSite($url)
    $RootWeb = $RootSite.rootweb
 
$JobPostingList = $RootWeb.lists["Job Posting"]
$field = $JobPostingList.fields["Department Lookup"]
$DepartmentList = $RootWeb.lists["Department"]
 
    Write-Host -ForegroundColor red  $field.lookupWebId.toString() to $RootWeb.id.toString()
    Write-Host -ForegroundColor red  $field.lookupList.toString() to $DepartmentList.id.toString()
 
    $field.schemaXml = $field.schemaXml.replace($field.lookupWebId.toString(), $RootWeb.id.toString())
$field.schemaXml = $field.schemaXml.replace($field.lookupList.toString(), $DepartmentList.id.toString())
                 
    $field.pushChangesToLists = $true
    $field.update($true)

    Write-Host -ForegroundColor Green "Lookup lists rewired"
}
catch [Exception]
{
Write-Host -ForegroundColor Red "Error in  Rewire_Lookup_Fields: $_"
}
finally
{  
    $RootWeb.Dispose()
$RootSite.Dispose()
}
After running your script, your lookup column should be wired up.

List Definition & Instance

In my previous post I held your hand through the tough process of declaring some site columns and content types; by tough I mean difficult if you're a nine year old with a learning disability, but hey I'm not one to judge. You can think of this post as part deux, where I will show you how to create a list definition from your content type and then instantiate a list instance from the definition. Sound like a blast? if you said yes, I sympathise with you for my life is just as empty and meaningless as yours. If you said no, well then congratulation and I'd like some fries with that...

Let's get started, create a SharePoint 2010 Project in the same solution called LookUp.Data, this is going to be where our list definitions and list instances are going to reside.

With your Lookup.Data project Created add two folders:

• List Definitions
• List Instances

Lets create some list definitions:

1. Add a new item to the List Definition folder
• In the left pane select SharePoint 2010, 
• In the middle pane select List Definition from Content Type
• Give your list definition a descriptive name, and somehow specify that it's a list definition. I added an _LD but you can do whatever you like.
• Click the Add button 

2. Next you'll see the SharePoint Customization Wizard:
• Make sure to shorten the solution name
• select the content type you want to build the definition from
• uncheck the "Add a list instance for this list definition"

now repeat the previous steps to create the Job Posting List Definition, once complete you should end up with something along the lines of:

1. Now with Both of our List Definitions complete lets go and create some List Instances to match.
• Right click on the List Instance folder and add a new item.
• in the left hand pane again select SharePoint 2010
• In the middle pane pick List Instance
• Give it a descriptive Name like "Department"
• Click Add

2. Next you'll see the SharePoint Customization Wizard:
• Give your list instance a display name
• select the List Definition you want to Instantiate
• Fill out a description
• specify the URL address for the list
• and finally decide whether you want it in the quick launch bar, that's really up to you, generally if a list is updated regularly by a user you'd set it to true.

Now with the Department List Instance ready repeat the steps above to create the Job Posting List instance.

Just as before lets clean up our feature:

• Rename Feature 1 to "Lookup.Data"
• Double Click the Lookup.Data feature to view the feature manifest.
• Change the Title to something descriptive
• Give it a description
• set the scope
• and most importantly remember to add all the List instances to the manifest.

Once Complete your Feature Manifest should resemble the following.

Now deploy the Lookup.Data project to deploy our list definitions and instances. Now you need to know that when you deploy a project that contains a list instance two shitty things happen:

• all list data is cleared
• the list is assigned a new GUID (that ruins lookup columns)

I mention these because in the next post we're going to add a lookup column and then rewire it.

Site Columns & Content Types

You want to Create Two Lists one that feeds off the other, but you're not sure where to start, well you start with site columns & Content types then you move onto List definitions and Instances.

Let’s get started, create a new SharePoint 2010 project and name it Lookup.Schema inside a solution called Lookup.Example, this is where we are going to put the site columns, and the content types for our two lists. Create two folders:

• Content Types
• Site Columns

Once that’s complete inside the SiteColumns folder create an Empty Element called SiteColumns and create the following site columns

•  DepartmentID – Text
• Department – Text
• JobPostingName – Text
• StartDate –DateTime
• EndDate –DateTime
• Department – Lookup

The XML for the Site Columns:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Field ID ="{B23D01D7-5062-4CBC-8336-2FF4E1731ABE}"
       Name="SPJ_DepartmentName"
       DisplayName ="Department"
       Type="Text"
       Required="TRUE"
       Group="SPJ Columns" />
  <Field ID ="{FE0E46B4-ECC0-4CAB-84AC-7D835B486834}"
       Name="SPJ_DepartmentID"
       DisplayName ="Department ID"
       Type="Text"
       Required="TRUE"
       Group="SPJ Columns" />
  <Field ID ="{07D51A7C-1125-412D-8999-D4A6F8F6F5BA}"
       Name="SPJ_JobPostingName"
       DisplayName ="Job Posting"
       Type="Text"
       Required="TRUE"
       Group="SPJ Columns" />
  <Field ID ="{E7A84BA3-1F9A-43AE-B5A6-07A3F1A726D8}"
       Name="SPJ_StartDate"
       DisplayName ="Start Date"
       Type="DateTime"
       Required="TRUE"
       Format="DateTime"
       Group="SPJ Columns" />
  <Field ID ="{9861E969-637A-49F3-8354-A1D0BB65B14D}"
       Name="SPJ_EndDate"
       DisplayName ="End Date"
       Type="DateTime"
       Required="TRUE"
       Format="DateTime"
       Group="SPJ Columns" />
</Elements>

Now what you might notice is that I’ve listed 6 columns that you need to make but there’s only definitions for five of them. As you will see later on, a lookup site column needs a list instance to feed from, so we’ll set that up once we have our provider list instance ready.

Now that we have our site columns ready to go we need to create Content type for our list definitions. I want you to create to Content types that inherit from Item in the Content Type folder:

• Department
• JobPosting

When you see the following screen make sure to select item from the drop down before you click Finish.

With your two content types created your solution explorer should resemble the following:

The Site column xml should be contained within the site columns empty element which is inside the Site Columns folder as for the department and JobPosting content types, they should be inside the Content Types folder. This may seem like overkill for a small demo project like this and it is, but it’s a great habit to get into. SharePoint Projects tend to grow at an exponential rate and due to the brittle nature of this beast it is very much worth wile to be organized from day one, rather than trying to come back later and organize things.

So let’s open up the Department Content type and add the site column detentions we are going to use:

• Department ID
• Department Name

The XML is as follows:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <!-- Parent ContentType: Item (0x01) -->
  <ContentType ID="0x010010495f58ea83494d8b3ece02c8ab1cf0"
               Name="Department"
               Group="SPJ Content Types"
               Description="Content type to describe a Department"
               Inherits="TRUE"
               Version="0">
    <FieldRefs>
      <FieldRef ID ="{FE0E46B4-ECC0-4CAB-84AC-7D835B486834}" Name="SPJ_DepartmentID"/>
      <FieldRef ID ="{B23D01D7-5062-4CBC-8336-2FF4E1731ABE}" Name="SPJ_DepartmentName"/>
    </FieldRefs>
  </ContentType>
</Elements>

With the Department Content Type complete let's make the Job Posting One:

• Job Posting Name
• Start Date
• End Date

The XML is as Follows

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <!-- Parent ContentType: Item (0x01) -->
  <ContentType ID="0x010068497622be804676bf83a14bbdf3dc67"
               Name="JobPosting"
               Group="SPJ Content Types"
               Description="Content Type to describe a job posting"
               Inherits="TRUE"
               Version="0">
    <FieldRefs>
      <FieldRef ID ="{07D51A7C-1125-412D-8999-D4A6F8F6F5BA}" Name="SPJ_JobPostingName" />
      <FieldRef ID ="{E7A84BA3-1F9A-43AE-B5A6-07A3F1A726D8}" Name="SPJ_StartDate" />
      <FieldRef ID ="{9861E969-637A-49F3-8354-A1D0BB65B14D}" Name="SPJ_EndDate" />
    </FieldRefs>
  </ContentType>
</Elements>

With the Content types Complete lets add the finishing touches by expanding the Features folder in the solution explorer, rename Feature1 to Lookup.Schema.

With "Feature 1" renamed to "Lookup.Schema" double click on "Lookup.Schema.feature" to display the actual feature manifest. a feature manifest is a list of things that this feature is going to deploy to the site and activate. With your feature manifest open rename the title to Lookup.Schema WebScope and give it a description, also make sure that is in fact scoped for the web.

With all that complete deploy your solution to add the site columns and content types to your site.