Provisioning a Lookup Site Column using PowerShell

[Lance Alcabasa]

I am having problems provisioning a look-up column. I am using the following code:

$list = $context.Site.RootWeb.lists.GetByTitle("Ticket Categories")
$rootWeb = $context.Site.RootWeb
$context.Load($list)
$fields = $context.Site.RootWeb.fields
$context.Load($fields)
$context.Load($rootWeb)
    
#send the request containing all operations to the server
$context.executeQuery()

$listId = $list.ID
$LookupWebID=$rootWeb.Id

$fieldAsXML = "<Field Type='Lookup' DisplayName='Ticket Category' Required='TRUE' EnforceUniqueValues='FALSE' List='{e3b055fc-e4b0-43d9-b8da-ef4ae0165c56}' WebId='d5d274fa-2a1f-4da5-86a4-155431b126af' ShowField='Title' UnlimitedLengthInDocumentLibrary='FALSE' Group='Custom Columns' ID='{2CD129D4-6BD8-4029-928D-0D1551F6DC15}' SourceID='{d5d274fa-2a1f-4da5-86a4-155431b126af}' StaticName='TicketCategory' Name='TicketCategory' Version='1' />"

$fieldOption = [Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint
$field = $fields.AddFieldAsXML($fieldAsXML, $true, $fieldOption)
$context.load($field)
$context.executeQuery()

The problem now is that the column gets created but doesn't work properly. The screenshot below shows 2 lookup columns, the Lookup Category column was created using Browser GUI. The Ticket Category column was created using PowerShell. As can be seen in the screenshot, there are no choices available for the Ticket Category column, which seems like it was not provisioned properly.

Looking at the Schema XML of both columns, I can't see any issue though, apart from having different IDs.

<Field Type="Lookup" DisplayName="Ticket Category" Required="TRUE" EnforceUniqueValues="FALSE" List="{e3b055fc-e4b0-43d9-b8da-ef4ae0165c56}" WebId="d5d274fa-2a1f-4da5-86a4-155431b126af" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" Group="Custom Columns" ID="{2cd129d4-6bd8-4029-928d-0d1551f6dc15}" SourceID="{d5d274fa-2a1f-4da5-86a4-155431b126af}" StaticName="TicketCategory" Name="TicketCategory" Version="1" />

<Field Type="Lookup" DisplayName="Lookup Category" Required="TRUE" EnforceUniqueValues="FALSE" List="{e3b055fc-e4b0-43d9-b8da-ef4ae0165c56}" WebId="d5d274fa-2a1f-4da5-86a4-155431b126af" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" Group="Custom Columns" ID="{705eab0d-a97e-4acf-a95f-315cc55cf197}" SourceID="{d5d274fa-2a1f-4da5-86a4-155431b126af}" StaticName="Lookup_x0020_Category" Name="Lookup_x0020_Category" Version="1" />

What seems to be wrong? Is there a correct way or better way to provision a Lookup Site Column?

[#2]

I tried to reproduce the issue but I wasn't able to. Could you try what happens when you remove the "Version" attribute from your Field XML? When I include this attribute in my tests I received the error messsage that the field was modified by another user. I do get a working lookup field however...

The attribute "SourceID" could also be omitted...

A little side note: are you familiar with PnP-PowerShell? It includes a lot of usefull cmdlets, for example Add-PnPFieldFromXml

https://docs.microsoft.com/en-us/powershell/module/sharepoint-pnp/add-pnpfieldfromxml?view=sharepoin...

[#3]

Hello,

It seems the problem is because of the static ID property. If that is omitted, the error doesn't happen anymore.

Thanks!