Another day, another challenge!  I was looking at a document library with 1000+ files where a "Document Type" column had been added as a choice.  Unfortunately, the column was supposed to be a lookup.  I created a new lookup column and pointed it to the correct list.  That's great, but editing the properties of all 1000+ files just didn't sound like good times to me.  The following script walks the document library, pulls each "Document Type" choice, matches it against the lookup list and updates the new "Document Type" column.

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

# What are we going to look at?
$myWeb = "http://some_portal"
$myList = "Some Library"

# Master document type lookup list
$myListLU = "Lookup List"

# Retrieve the lists
$web = Get-SPWeb $myWeb
$list = $web.GetList("$myWeb\$myList")
$listLU = $web.GetList("$myWeb\lists\$myListLU")

# Enumerate the list and update column
$itemCount = $list.Items.Count
"`nDocument library contains $itemCount items ...`n"
$i = 1
ForEach ($item in $list.Items) {
  "Updating item: $i of $itemCount"
  $oldData = $item["Document Type"]

  # Match choice to lookup
  ForEach ($itemLU in $listlU.Items) {
    If ($itemLU["Title"] -eq $oldData) {
      $NewLookupFieldValue = New-Object Microsoft.Sharepoint.SPFieldLookupValue $itemLU["ID"], $itemLU["Title"]
      $item["DocTypeTemp"] = $NewLookupFieldValue


I was writing a workflow in SharePoint Designer to take actions based on the values of a multiple selection choice field.  Let's say your choice field has the following choices:

Any or all of the above choices can be selected.  I used "contains" to check for the existance of particular choice selections ... if field contains "A" do this, if field contains "B" do this, etc.  However, what happens when "AB" is selected?  Workflow for "A" and "B" would fire, because "AB" contains both.  Why not use "equals" instead of "contains"?  That would be very lengthy, because the result could "equal" ... "A", "B", "AB", "A, B", "A, AB", etc.

RegEx to the rescue!  Instead of using "contains" for "A" and "B", use "matches regular expression" and use the word break expression \b.  For the above example, we can use \bA\b to match just "A" and \bB\b to match just "B".  We can still use "contains" for "AB".

Copyright 2011 - 2021 The Lazy SharePoint Admin | All Rights Reserved
menu-circlecross-circle linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram