Create additional lookup columns using powershell in SharePoint

In SharePoint, we can create additional lookup columns through user interface but the internal name of these fields are not appropriate. Also, there is no provision to create additional fields for “User” or “UserMulti” type fields.
The below powershell can create additional columns by taking certain parameters

  1. $web : SharePoint web object
  2. $parentListName : List name in which additional lookup columns need to be created
  3. $parentCol : Lookup column internal name
  4. $childColName : Additional column name which we want to create
  5. $lookupColType : Type of additional lookup column (e.g. “Lookup”, “LookupMulti”, “User”, “UserMulti”)
  6. $showField : Field to be bind with additional column (e.g. “ID”)
function CreateAdditionalLookupColumns([Microsoft.SharePoint.SPWeb] $web, [string] $parentListName, [string] $parentCol, [string] $childColName, [string] $lookupColType, [string] $showField)
{
$parentList = $web.Lists.item($parentListName)

$fieldLookup = $parentList.Fields.GetFieldByInternalName($parentCol)
#write-host $fieldLookup.SchemaXml `r`n

if($lookupColType -eq "Lookup" -or $lookupColType -eq "User")
{
$schemaXML = ''
$schemaXML = [string]::Format($schemaXML,$childColName, $fieldLookup.Id, [guid]::NewGuid(), $childColName, $lookupColType, $fieldLookup.LookupList, $showField, $childColName, $fieldLookup.SourceID);
}
elseif($lookupColType -eq "UserMulti")
{
$schemaXML = ''
$schemaXML = [string]::Format($schemaXML,$childColName, $fieldLookup.Id, [guid]::NewGuid(), $childColName, $lookupColType, $fieldLookup.LookupList, $showField, $childColName, $fieldLookup.SourceID);
}
elseif($lookupColType -eq "LookupMulti")
{
$schemaXML = ''
$schemaXML = [string]::Format($schemaXML,$childColName, $fieldLookup.Id, [guid]::NewGuid(), $childColName, $lookupColType, $fieldLookup.LookupList, $showField, $childColName, $fieldLookup.SourceID);
}

#write-host $schemaXML `r`n

write-host "---------" $parentListName "----------------------" `r`n

$fieldExist = $parentList.Fields.GetFieldByInternalName($childColName)
if($fieldExist -ne $null)
{
write-host "field deleted : " $fieldExist `r`n
$parentList.Fields.Delete($childColName)
}

$fieldNew = $parentList.Fields.AddFieldAsXml($schemaXML);
write-host "field created : " $fieldNew `r`n
write-host "-----------------------------------------------------" `r`n
}

$web = Get-SPWeb "http://siteURL"    # Your site URL

CreateAdditionalLookupColumns $web "ParentList" "ParentLookupColumnName" "AdditionalLookupColumnName" "Lookup" "ID"

CreateAdditionalLookupColumns $web "ParentList" "ParentLookupColumnName" "AdditionalLookupColumnName" "User" "Name"
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s