PowerShell and Lookup Columns

I was working on a new script that I’m calling the “Mass Install Package Form”, tied into this PSMANAGE tool I’ve been working on. The purpose of the form is to let someone assign a software package to many computers and then have them perform the installation automatically without intervention. The form itself lets you pick a package, then lets you tag the computers you want to install it on, and the it goes and creates SharePoint tasks for each computer you selected.


I ran into an issue while creating the tasks, where the Lookup columns were failing. I did a bit of research and found out that I needed to use the lookup records ID not display name.

So there were two ways to accomplish this. I could either pass the ID along with the other information, or I could use SharePoint to retrieve the ID by passing the display name.

In case I have to do this again in the near future, I will post the steps here to refresh my memory 😛

The example here is that I have two lists. One is called “Packages” which is a short list of silent install packages. The other is called “Tasks” which is the list of computers that will get a package assigned to them. So for installing Office 2016 to 50 computers, there will be 50 records all with the “Office 2016” package in the lookup column. The reason I had to do this with PowerShell was because I was generating these tasks from my PSMANAGE tool (that’s another story). Anyway, here is how to get the ID from the Lookup column display name…

# make the connection to your existing workspace and lists...
$yourURL = "https://sharepoint/sites/yoursite"
$spWeb = Get-SPWeb $yourURL
$spList = $spWeb.Lists["Tasks"]
$spLookupList = $spWeb.Lists["Products"]

# the lookup column value you want to use...
$yourValue = "Office Professional Plus 2016"

# creating a new list item
$yourListItem = $spList.AddItem()

# getting the ID for the lookup column value you had the user select in the form (assuming you aren't passing the ID along too)...
$yourLookupListItem = $spLookupList.Items | Where-Object {$_.DisplayName -eq $yourValue}
$yourLookupListItemID = $yourLookupListItem.ID

# assign the ID as the value to the lookup column in your new list item...
$yourListItem["TaskPackage"] = $yourLookupListItemID

So I went and did this and it worked pretty well. The only problem was that when you are adding a lot of records, the time it takes to do this for like 50-100 tasks is a bit ridiculous. So it turned out it was much better to just include the ID along with the Display Name when sending your chosen lookup column value. So yah, I stopped using this like an hour after I got it working. But it might come in  handy some other time 😛

Oh and another type of column, the “Person or Group” type needs a little something special also.

$spAssignTo = "domain\username"
$assignTo = $spWeb.EnsureUser("$spAssignTo")
$newItem["TaskAssignedTo"] = $assignTo