0

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

2

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

  • but why u are specifying the Id of column ??? I usually create site columns using power shell without having to specify the Id – john Gu May 10 '17 at 17:43
  • yes no need to specify it. Its optional. You can remove that in your code. – Gautam Sheth May 10 '17 at 17:45
  • now i tried defining the xml as you mentioned ,, but the lookup list will be empty... – john Gu May 10 '17 at 22:40
  • now i tried defining the xml as you mentioned ,, now the lookup column will be created but the lookup list (Get information from) for the lookup field will be empty... – john Gu May 10 '17 at 23:28
  • 1
    yes again my bad, its $false. Yes, you can control it as $field.ShowInNewForm = $true , $field.ShowInEditForm = $true etc . Am not sure about the first approach, did you try with list guid or list title ? – Gautam Sheth May 11 '17 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.