Updating SharePoint Managed Metadata Columns with PowerShell
This post shows how to update a field with a managed metadata term.
Background
As with all software projects, requirements change as soon as you deliver. We created a huge list based on a nested folder structure.
Of course, our customer thought navigating this was too cumbersome, requiring them to click through many folders. They would like to be able to use faceted search to filter the items. To satisfy this, we will use SharePoint 2010’s new metadata navigation feature to filter based on managed metadata.
We add a managed metadata column named “Projects” and update the managed metadata column with a term from the list. To make things easy, I added a managed metadata column to the list using the web user interface. I picked a term set from my Managed Metadata Service Application to use for the column.
But now we have a problem… we have 50,000 items, how do we assign that many values? Let’s assume that all items with a Title beginning with “Item 1” will use the term “Newsfeed” in my new column, named “Projects”.
Obtaining a Reference to a Managed Metadata Term
The first task is to obtain a reference to the term “Newsfeed”.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Taxonomy")
$site = Get-SPSite https://portal.sharepoint.com
$session = New-Object Microsoft.SharePoint.Taxonomy.TaxonomySession($site)
$termStore = $session.TermStores["Connection to: Managed Metadata Service"]
$group = $termStore.Groups["MyTerms"]
$termSet = $group.TermSets["Features"]
$terms = $termSet.GetTerms(100)
$term = $terms | ?{$_.Name -eq "Newsfeed"}
Now we have a reference to the term in the “$term” variable.
Setting a Managed Metadata Column’s Value
The next step is to set the value for the new “Project” column using the term. Remember that we have 50,000 items in this list, so we iterate in batches to perform the update.
$web = Get-SPWeb https://portal.sharepoint.com
$list = $web.Lists["LargeList"]
$query = New-Object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'";
$query.RowLimit = 2000
$caml = '<Where><BeginsWith><FieldRef Name="Title" /><Value Type="Text">Item 1</Value></BeginsWith></Where>' +
'<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>'
$query.Query = $caml
do
{
$listItems = $list.GetItems($query)
$spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
foreach($item in $listItems)
{
#Cast to SPListItem to avoid ambiguous overload error
$spItem = [Microsoft.SharePoint.SPListItem]$item;
#Get the taxonomy field for the list item
$taxField = [Microsoft.SharePoint.Taxonomy.TaxonomyField]$spItem.Fields["Project"]
#Set the field's value using the term
$taxField.SetFieldValue($spItem,$term)
$spItem.Update()
}
}
while ($spQuery.ListItemCollectionPosition -ne $null)
We repeat the process for other items, allowing us to now start using metadata navigation for our huge list. We add the metadata field as a key field, and we can now easily navigate using Metadata instead of having our users click on folders.
A Note On Performance
This code is slow and inefficient, and that’s on purpose. My goal here was to show you how to set a taxonomy field’s value using PowerShell. The example I contrived updates approximately 10,000 rows. That’s 10,000 individual calls to the database. A much better way to perform a batch update is to use the ProcessBatchData method of the SPWeb object.
For More Information
Metadata Navigation and Filtering in SharePoint Server 2010 (ECM)
Changing Managed Metadata Column's Value in a List through Code