Adding Lookup Field to a SharePoint list via PowerShell (SharePoint/PNP)

Question

I am trying to create a lookup field via Sharpoint PNP PowerShell module.

And here is my script:

$URL = "https://testing.sharepoint.com/sites/sitename"

Connect-PnPOnline -url $URL -Credential credentialname

$targetlistname = "users" ## Name of the target list
$sourcelistname = "testlist" ## Name of the list which contains the lookup field
$sourcefieldname = "Title" ## Name of the lookup column
$lookupfielddisplayname = "JustTesting" ## Name of the new column
$lookupfieldinternalname = "justtesting" ## Name of the internal name of the new column


Add-PnPField -List $targetlistname -DisplayName $lookupfielddisplayname -InternalName $lookupfieldinternalname -Type Lookup -LookUpList $sourcelistname -LookUpValue $sourcefieldname

The shown error:

I would like to achieve this:

Can someone help me with the correct syntax?

Answer 1

Currently, Add-PnPField command still not support "LookupList" parameter, please refer the opening issue here:

Question: Adding a lookup field with Add-PnPField #1518

A workaround is to add the field with schema like this:

$xml = '<Field Type="Lookup" DisplayName="testlookup" Required="FALSE" EnforceUniqueValues="FALSE" List="{2cefea32-3396-4a9f-9117-753d110a6d4e}" ShowField="Title" UnlimitedLengthInDocumentLibrary="FA
LSE" RelationshipDeleteBehavior="None" ID="{c7da3702-965c-4cdf-b6ca-48801af6860a}" SourceID="{ffb78df3-46ea-4b1a-b596-b01adfaa2692}" StaticName="testlookup" Name="testlookup"/>'
Add-PnPFieldFromXml -List "MyList11" -FieldXml $xml

In the schema above, List is the target list Guid, showField is the target field name, SourceID is the Source List Guid.

Answer 2

Currently Add-PnPField doesn not support parameterized creation of Lookup columns. You can use the below code (tried and tested).

$URL = "https://testing.sharepoint.com/sites/sitename"

Connect-PnPOnline -url $URL -Credential credentialname

$targetlistname = "users" ## Name of the target list
$sourcelistID = "List ID here" ## ID of the source list (click on list settings and in url you get it's ID)
$sourcefieldname = "Title" ## Name of the lookup column
$lookupfielddisplayname = "JustTesting" ## Name of the new column
$lookupfieldinternalname = "justtesting" ## Name of the internal name of the new column

$ctx = Get-PnPContext
$lookupField = Add-PnPField -List $targetlistname -DisplayName $lookupfielddisplayname -InternalName $lookupfieldinternalname -Type Lookup -AddToDefaultView
$lookupField = $lookupField.TypedObject
$lookupField.LookupList = $sourcelistID
$lookupField.LookupField = $sourcefieldname
$lookupField.update()
$ctx.ExecuteQuery()

Thanks

Answer 3

Meanwhile you can shorten up your code by setting:

$DisplayName = "Email"
$PNPField = Add-PnPField -List $List -DisplayName $DisplayName -InternalName $DisplayName -Type Lookup  -AddToDefaultView
Set-PnPField -List $List -Identity $PNPField.Id -Values @{LookupList= "a66ff40f-ceca-4f6b-a523-7fe32a97ea11"; LookupField="Email"}

Source: https://sposcripts.com/add-sharepoint-columns-with-powershell/#Add_Lookup_Column