How to Create a Lookup List for a Site Column Using Powershell

2008-01-04T01:01:00Z

I had a task awhile back to create a list that would be used for a lookup on a site column. The site column I needed to create was State, with the lookup providing me a list of all 50 states. I could have created this list by hand by entering in all 50 states, but I figured there had to be a better approach. I instead created a PowerShell script to help me with this task. Take a look:

[System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.Sharepoint”)

# function to add each item in array to list

function AddItem

{

write-host Adding Item: $args[0]

$varItem = $varList.Items.Add()

$varItem["Title"] = $args[0]

$varItem.Update()

}

# set site path

$varSitePath="http://somesite"

# create new SPSite object

$varSite=new-object Microsoft.SharePoint.SPSite($varSitePath)

$varWeb = $varSite.OpenWeb()

# get list template

$varListTemplate = $varWeb.ListTemplates["Custom List"]

# set List title

$varResName="State"

# create array of items that you want to populate list with

$varTitles= @("Alabama", "Alaska", "Arizona", "Arkansas", "California", "Colorado", "Connecticut", "Delaware", "Florida", "Georgia", "Hawaii", "Idaho", "Illinois", "Indiana", "Iowa", "Kansas", "Kentucky", "Louisiana", "Maine", "Maryland", "Massachusetts", "Michigan", "Minnesota", "Mississippi", "Missouri", "Montana", "Nebraska", "Nevada", "New Hampshire", "New Jersey", "New Mexico", "New York", "North Carolina", "North Dakota", "Ohio", "Oklahoma", "Oregon", "Pennsylvania", "Rhode Island", "South Carolina", "South Dakota", "Tennessee", "Texas", "Utah", "Vermont", "Virginia", "Washington", "West Virginia", "Wisconsin", "Wyoming")

write-host Creating list: $varResName

# add list to web

$varWeb.Lists.Add($varResName, $varResName, $varListTemplate)

$varList = $varWeb.Lists[$varResName]

# loop through array and call function to add items to list

$varTitles | % { AddItem $_ }

$varWeb.Close()

$varSite.Close()

After script was run, you get the following list:

So with this list created, I can now create my State site column and use this list for the lookup.

Make sure that for the “In this column” that you select Title, otherwise your list will be empty.

Corey Erkes

How to Create a Lookup List for a Site Column Using Powershell