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.