Powershell - Moving lists with lookup columns inside your site collection

The Example

Alright, here’s the story. On a subsite inside our site collection, we have two lists: Colors and Themes. The Colors list is a simple one, containing only a single column – Title (ie. Color name). The themes list contains the theme Title and a Theme Color property, which is configured as a lookup column to the Title field of the Colors list. Here’s some pictures, just to get the idea:

The Problem

This is where things start to get interesting. Suppose you had to migrate those two lists to another site, like the site collection root. Shouldn’t be a problem, right? We can just use PowerShell:

1 2 3

$web = get-spweb "http://sp2010/subsite1/subsite2" export-spweb $web -ItemUrl "/List/Colors" -Path "c:\backup\colors.cmp" export-spweb $web -ItemUrl "/List/Themes" -Path "c:\backup\themes.cmp"

Once the lists have been successfully exported, we simply need to import them back to the site collection root:

1 2 3

$web = $web.Site.RootWeb import-spweb $web -Path "c:\backup\colors.cmp" import-spweb $web -Path "c:\backup\themes.cmp"

And that’s it. Now simply navigate to your site collection root and open the Themes list. Everything seems to be working fine! What is this blog post even about then??

Well, the problem starts when you navigate back to the original subsite and delete the lists! When you navigate back to the imported Themes list, you’ll notice that the lookup column no longer works:

The Solution

To fix this problem, I’ve used SharePoint Manager 2010, although PowerShell would’ve done the job nicely. The reason I’ve decide to use SharePoint Manager over PowerShell, is because I think it’s nicer to work with when manipulating XML schemes (which is what we’ll be doing shortly). Additionally, it’s really an awesome tool and I think everybody who does SharePoint should use it.

So, fire up SharePoint Manager and navigate to the imported Themes list, and examine its Theme Color property:

You see, there are three important properties which make lookup columns work:

• LookupList – the ID of the list which contains the lookup values
• LookupWebId – the ID of the site, which is the parent web for the lookup list
• LookupField – the name of the column on the LookupList, which contains the actual lookup values

The reason the lookup column doesn’t work anymore is that the export-web and import-web cmdlets preserve all the list settings. Therefore, the lookup column still points to the original Colors list, which was located on the subsite. Just to prove that the values are no good, here’s a few screenshots:

You can see that we should update the LookupList field to the value {4B22429F-7D5A-4A8C-AD9D-CA80A2BFEDDB}, and the LookupWebId to the value 5bbf7020-c0d8-4af7-ad36-62ff86e08b39 (it’s interesting to note that not all IDs contain curly braces).

The only problem left for us to solve is how to set these values, because neither SharePoint Manager nor PowerShell will let you change these values directly. Instead, we’ll have to update the SchemaXml peroperty of the lookup column. The original value was:

1

<Field Type="Lookup" DisplayName="Theme Color" Required="TRUE" EnforceUniqueValues="FALSE" List="{1def7ac1-7bc4-4acd-95fe-7be3c7128239}" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" RelationshipDeleteBehavior="None" ID="{4f757239-c8f9-43db-8ac2-f889c0071087}" SourceID="{884c0f6a-c4fb-43bd-af90-fc1db1374103}" WebId="e1188ac9-1f4e-4cdf-bcdc-85a08e849d73" StaticName="ThemeColor" Name="ThemeColor" ColName="int1" RowOrdinal="0" Group="" Version="2" />

Now simply update this XML snippet with the two IDs (ie. update the List and WebId attributes) and paste it back into the SchemaXml property text box (also, make sure that is entire snippet is on a single line). Finally, press the Save button and you’re done! The lookup column should be working fine again.