Create a lookup column using powershell. How i can define the related Lookup list

Question

I am working on-premise SharePoint server 2016. and i want to create a new site column of type Lookup. the column display name will be "Department". so when i try to create the column using the UI i got an error that the Department column already exists. so i want to create the column using power-shell, so i can have its Display name = Department. Now i tried this script:-

$site = Get-SPSite -Identity "http://***1/"
$web = $site.RootWeb


#Assign fieldXML variable with XML string for site column
$fieldXML = '<Field Type="Lookup"
Name="MyDepartment"
Description=""
DisplayName="Department"
StaticName="MyDepartment"
Group="custom columns"
Hidden="FALSE"
Required="TRUE"
Sealed="FALSE"
ShowInDisplayForm="TRUE"
ShowInEditForm="TRUE"
ShowInListSettings="TRUE"
ShowInNewForm="TRUE">
</Field>'

#Output XML to console
write-host $fieldXML

#Create site column from XML string
$web.Fields.AddFieldAsXml($fieldXML) 

which created a lookup column. but when i edit it using the UI i was not able to select the lookup list. so seems i need to define this using powershell .. so can anyone advice on this please? how i can define the lookup list and its related fields using powershell for my new lookup column??

Thanks

Answer 1

Try it as below xml:

Option 1

<Field Type="Lookup" 
    ID="{AD45AC76-D1E2-46C8-A047-E8C43C10205C}" 
    Name="MyDepartment" 
    StaticName="MyDepartment" 
    DisplayName="Department" 
    Required="FALSE" 
    List="Lists/TestList" 
    ShowField="Title"   
    Group="custom Columns" />

You need to specify the List and ShowField.

List == the destination list from which you want to get data

ShowField == the destination list column which will be "looked up".

In case it's a multi-valued lookup, you also need to specify Mult=TRUE.

Option 2

You can also add it as below:

$site = Get-SPSite -Identity "http://***/"
$web = $site.RootWeb
$lookupList = $web.Lists["Testlist"]  
$web.Fields.AddLookup("MyDepartment", $lookupList.ID,$false);
$field = $web.Fields["MyDepartment"]
$field.LookupField = $lookupList.Fields["Title"]
$field.Title = "Department"
$field.Update()

SPFieldCollection.AddLookup method