I needed to change a choice column to a lookup column and then update the new lookup column with the values of the old choice column. Lookup columns cannot be updated in the same manner as text and choice columns. The lookup column is a different variable type than plain text. Therefore, to set its contents, a variable of the same type must be created explicitly or implicitly, populated, and then pushed into the lookup column.
In this particular example, I needed to make this change for a service ticket tracking list. The old status column was of type Choice, and I needed to change it to type Lookup, so that I could implement basic metrics using the undocumented
count related method. The Choice column had four possible values: Initiated, Engaged, Resolved and Closed. These needed to be pushed appropriately into the corresponding new column. There are several approaches presented
here. The approach I used involved explicitly creating the variable type needed.
Script
$SourceWebURL = "http://[yourwebsite/yourlist]"
$SourceWeb = Get-SPWeb $SourceWebURL
$TicketList = $SourceWeb.Lists["[ticketlistname]"]
$StatusList = $SourceWeb.Lists["[lookuplistname]"]
$StatusListItemInitiated = $StatusList.GetItemById(1);
$StatusListItemInitiatedValue = New-Object Microsoft.SharePoint.SPFieldLookupValue($StatusListItemInitiated.ID,$StatusListItemInitiated.ID.ToString());
$StatusListItemEngaged = $StatusList.GetItemById(2);
$StatusListItemEngagedValue = New-Object Microsoft.SharePoint.SPFieldLookupValue($StatusListItemEngaged.ID,$StatusListItemEngaged.ID.ToString());
$StatusListItemResolved = $StatusList.GetItemById(3);
$StatusListItemResolvedValue = New-Object Microsoft.SharePoint.SPFieldLookupValue($StatusListItemResolved.ID,$StatusListItemResolved.ID.ToString());
$StatusListItemClosed = $StatusList.GetItemById(4);
$StatusListItemClosedValue = New-Object Microsoft.SharePoint.SPFieldLookupValue($StatusListItemClosed.ID,$StatusListItemClosed.ID.ToString());
$total=$TicketList.Items.count;
$Count=1;
ForEach ($Item in $TicketList.Items)
{
$percent = [math]::Round(($count/$total)*100,2);
Write-Progress -Activity 'Updating Ticket listing ' -Status "$percent percent" -PercentComplete $percent;
$SDStatus = $Item["SD Status"];
IF ($SDStatus -eq "Initiated")
{
$Item["SDStatus2"] = $StatusListItemInitiatedValue;
$Item.Update();
$count = $count+1;
}
ELSEIF ($SDStatus -eq "Engaged")
{
$Item["SDStatus2"] = $StatusListItemEngagedValue;
$Item.Update();
$count = $count+1;
}
ELSEIF ($SDStatus -eq "Resolved")
{
$Item["SDStatus2"] = $StatusListItemResolvedValue;
$Item.Update();
$count = $count+1;
}
ELSEIF ($SDStatus -eq "Closed")
{
$Item["SDStatus2"] = $StatusListItemClosedValue;
$Item.Update();
$count = $count+1;
}
};
$SourceWeb.Close()
References
