This post is about creating a lookup column in the root site collection to a library in one of the sub sites. Many blogs on the internet describe how to do this without code, but they describe the other way around; their lookup is used on the sub site and the attached library is situated at the root site.

Our required situation

Our required situation

The situation in which the list is located in a sub web, cannot be configured from the UI. Previously a developer would be required to define the creation of the lookup column by code. But with PowerShell you have a great alternative to do it with scripting and without the help of a developer. However, you must have desktop access to the SharePoint server.

The script requires you to collect a few parameters to connect with the correct list. The most important thing to do, is to find the correct GUIDS of the sub web and the list. You can do this with PowerShell, but if you aren’t a developer I can recommend to use the SharePoint Manager tool. This tool will help you to retrieve all required GUIDS with ease.

Use SharePoint Manager to find GUIDS

After you configured the script with your parameters, paste it in a SharePoint enabled PowerShell window, and it will create the lookup column for you.

#Configuration
$sitecollectionUrl = "http://sitecollection.url/"
$lookupFieldWebGuid="{c04790db-cd8a-4ab1-b9ab-9d2964a89115}"
$lookupListGuid = "{5cbeb646-6517-4d8c-a974-dfeca6b66672}"
$fieldDisplayName="Sample lookup"
$fieldInternalName="SampleLookup"
$sitecolGroup = "Sample"
$showField = "Title"
$fieldDescription = "Field added by Powershell"
$fieldHidden="FALSE"
$fieldReadonly = "FALSE"
$fieldRequired = "FALSE"
$unlimitedLength = "FALSE"
#End of configuration

$siteCollection = Get-SPSite -Identity $sitecollectionUrl
$rootWeb = $siteCollection.RootWeb
#Define lookup site column with a XMLString
$lookupXMLString = '<Field Type="Lookup" DisplayName="' + $fieldDisplayName + '" Description="' + $fieldDescription + '" 
Required="' + $fieldRequired + '" EnforceUniqueValues="FALSE" WebId="' + $lookupFieldWebGuid + '" ShowField="' + $showField + '" UnlimitedLengthInDocumentLibrary="' + $unlimitedLength + '" 
Group="' + $sitecolGroup + '" StaticName="' + $fieldInternalName + '" List="' + $lookupListGuid + '" 
Name="' + $fieldInternalName + '" Hidden="' + $fieldHidden + '" ReadOnly="' + $fieldReadonly + '"></Field>'
#Create site column from XML string
$rootWeb.Fields.AddFieldAsXml($lookupXMLString) 
Write-Host "The sitecolumn has been added." -foregroundcolor green