Home > Development, PowerShell, SharePoint > Create lookup field using PowerShell and CSOM

Create lookup field using PowerShell and CSOM

For our projects we always try to avoid manual configurations. This is because it is a tedious and error prone process if you work with a DTAP environment. To avoid this, we also try to script as much as possible for SharePoint Online projects. Lately we worked with creating lookup fields in SharePoint online, using PowerShell and CSOM. Creating fields this way is pretty easy, but connecting lookup fields forced us to think about casting the Microsoft.SharePoint.Client.Field object to a Microsoft.SharePoint.Client.FieldLookup object.

Within CSOM this can be done by leveraging the ClientRuntimeContext.CastTo method, but… This is a generic method (object of type T). This is something which is not easily supported by PowerShell. To use this method, you can use reflection using the MakeGenericMethod method.

The full PowerShell script is provided below

#————————————————————-
# LOAD CLIENT ASSEMBLIES
#————————————————————-
$clientAssembliesFolder = “D:\ClientAssemblies”
Add-Type -Path (Join-Path -Path $clientAssembliesFolder -ChildPath “Microsoft.SharePoint.Client.dll”)
Add-Type -Path (Join-Path -Path $clientAssembliesFolder -ChildPath “Microsoft.SharePoint.Client.Runtime.dll”)

#————————————————————-
# INITIALIZE CONTEXT
#————————————————————-
[string]$siteUrl = "https://[UseYourOwn].sharepoint.com/sites/Dev"
[string]$username = “admin@[UseYourOwn].onmicrosoft.com”
[string]$password = “[UseYourOwn]”
$pwd = $password | ConvertTo-SecureString -AsPlainText -Force
$context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $pwd)
$context.Credentials = $credentials

#————————————————————-
# LOAD CASTTO FOR LOOKUPS
#————————————————————-
$castToMethodGeneric = [Microsoft.SharePoint.Client.ClientContext].GetMethod(“CastTo”)
$castToMethodLookup = $castToMethodGeneric.MakeGenericMethod([Microsoft.SharePoint.Client.FieldLookup])

#————————————————————-
# LOAD LISTS
#————————————————————-
[string] $originaListTitle = “List1”
[string] $destinationListTitle = “List2”
$listOriginal = $context.Web.Lists.GetByTitle($originaListTitle)
$context.Load($listOriginal)
$listDestination = $context.Web.Lists.GetByTitle($destinationListTitle)
$context.Load($listDestination)
$context.ExecuteQuery() # This loads the necessary list ID

#————————————————————-
# CREATE LOOKUP
#————————————————————-
[string] $internalName = “LookupWithStaticName”
[string] $displayName = “LookupTest”
[string] $displayFieldForLookup = “Title”
[string] $lookupFieldXML = “<Field DisplayName=`”$internalName`” Type=`”Lookup`” />”
$option = [Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView

$newLookupField
= $listDestination.Fields.AddFieldAsXml($lookupFieldXML, $true, $option)
$context.Load($newLookupField)
$lookupField = $castToMethodLookup.Invoke($context, $newLookupField)
$lookupField.Title = $displayName
$lookupField.LookupList = $listOriginal.Id
$lookupField.LookupField = $displayFieldForLookup
$lookupField.Update()
$context.ExecuteQuery()

Advertisements
  1. August 5, 2014 at 19:56

    how to add allow multiple property using CSOM while creating lookup column using above code

  2. August 5, 2014 at 20:09

    i have found that add multi=’TRUE’ in xml. Below is example

    “”

  3. Jacob Greeff
    July 5, 2015 at 16:59

    Hi guys,

    First time poster – this helped me SO much tonight! Building on it though:
    $lookupField.LookupWebId = $listOriginal.ParentWeb.Id

    helped me to make it a cross site lookup! you need to have the parentweb loaded with clientcontext though.

    You made a sharepoint n00b’s life MUCH easier!

  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Ben Prins

What I want to remember about SharePoint

blog.frederique.harmsze.nl

my world of work and user experiences

Bram de Jager - Coder, Speaker, Author

Office 365, SharePoint and Azure

%d bloggers like this: