Created
December 28, 2016 21:14
-
-
Save automationhaus/9fff8a22256f33dcfd6caf3818cd6048 to your computer and use it in GitHub Desktop.
Sharepoint Dynamic Metadata Values and List View Threshold Bypass
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <# | |
| .SYNOPSIS | |
| Sets the values for the title and metadata fields for the Contoso Customer Production Docs library | |
| .DESCRIPTION | |
| For each item in the library a lookup is performed either against the ERP, the file name or the parent folder names to generate a value for the current field. Possible values are matched against the contents in the term store to confirm the value can be set. The script also picks up where it left off from the last run using the last item id and last results count. Once the script is finished if a new customer folder is found the metadata defaults script, Set-MetaDataDefaults.ps1, is kicked off to set the defaults for that folder. | |
| .NOTES | |
| Britt Thompson | |
| [email protected] | |
| #> | |
| #region ASSEMBLIES, SNAPINS, MODULES | |
| Add-PSSnapin *Sharepoint* -ErrorAction SilentlyContinue | |
| [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint") | Out-Null | |
| [System.Reflection.Assembly]::LoadwithPartialName(“Microsoft.Office.DocumentManagement”) | Out-Null | |
| $Debug = $True | |
| #endregion | |
| #region FUNCTIONS | |
| # The write feedback function is a way to show screen output and log the output to file simultaneously | |
| function Write-Tee | |
| { | |
| [cmdletbinding()] | |
| param | |
| ( | |
| [Parameter(Position=0,ValueFromPipeline=$true)] | |
| [string]$msg, | |
| [string]$ForegroundColor = "White", | |
| [string]$OutFile=$LogFile, | |
| [switch]$Overwrite, | |
| [switch]$NoNewLine, | |
| [string]$Prefix=" - ", | |
| [switch]$NoPrefix | |
| ) | |
| $Info = "i"; $Task = "="; $Errors = "!"; $Inquiry = "?"; $Debugging = "d"; $Response = "r" | |
| $DBG = $False | |
| switch -regex ($msg) | |
| { | |
| "\[$Info\]" { $ForegroundColor = "Yellow" } | |
| "\[$Task\]" { $ForegroundColor = "Cyan"; $Prefix = " " } | |
| "\[$Errors\]" { $ForegroundColor = "Red" } | |
| "\[\$Inquiry\]" { $ForegroundColor = "Magenta" } | |
| "\[$Debugging\]" { $ForegroundColor = "Magenta"; $DBG = $True } | |
| "\[$Response\]" { if($ForegroundColor -eq "White"){ $ForegroundColor = "Green" } } | |
| } | |
| if(($DBG -eq $False) -or ($DBG -and $Debug)){ $Write = $True } | |
| if(!$NoPrefix) | |
| { | |
| if($Write){ Write-Host $Prefix -NoNewline } | |
| } | |
| if($NoNewLine) | |
| { | |
| if($Write){ Write-Host -ForegroundColor $ForegroundColor $msg -NoNewline } | |
| } | |
| else | |
| { | |
| if($Write){ Write-Host -ForegroundColor $ForegroundColor $msg } | |
| } | |
| if(!$NoPrefix -and $msg -ne ""){ $msg = $Prefix + $msg } | |
| if($OutFile -ne "") | |
| { | |
| if(!$Overwrite) | |
| { | |
| if($NoNewLine) | |
| { | |
| if($Write){ [System.IO.File]::AppendAllText($OutFile, $msg, [System.Text.Encoding]::Unicode) } | |
| } | |
| else | |
| { | |
| if($Write){ $msg | Out-File $OutFile -Append } | |
| } | |
| } | |
| else | |
| { | |
| if($Write){ $msg | Out-File $OutFile -Force } | |
| } | |
| } | |
| } | |
| #Get catch is a function used in the error catching to reduce repetitive lines | |
| function Get-Catch | |
| { | |
| $ErrorMessage = $_.Exception.Message | |
| Write-Tee "[!] $ErrorMessage" | |
| $Script:Outcome = "failed" | |
| $Script:Color = "Red" | |
| $Script:E++ | |
| } | |
| #Function to set the metadata values for single and multi values fields | |
| function Set-MetadataFieldValue([Microsoft.SharePoint.SPListItem]$ListItem, [string]$FieldName, $TaxFieldItem, [switch]$Multi) | |
| { | |
| if($TaxFieldItem.Name -ne ($ListItem[$FieldName]).Label) | |
| { | |
| try | |
| { | |
| Write-Tee "[i] Setting $FieldName Value" | |
| Write-Tee "[d] Taxonomy Item Value: $($TaxFieldItem.Name)" | |
| Write-Tee "[d] Old $FieldName Value: $(($ListItem[$FieldName]).Label)" | |
| $Field = [Microsoft.SharePoint.Taxonomy.TaxonomyField]$ListItem.Fields[$FieldName] | |
| if($Multi) | |
| { | |
| $FieldCollection = New-Object Microsoft.SharePoint.Taxonomy.TaxonomyFieldValueCollection($Field) | |
| $FieldValues = $ListItem[$FieldName] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValueCollection] | |
| $FieldValues | %{ | |
| $FieldValue = New-Object Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue($Field) | |
| $FieldValue.TermGuid = $_.TermGuid | |
| $FieldValue.Label = $_.Label | |
| $FieldCollection.Add($FieldValue) | |
| } | |
| $FieldValue = New-Object Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue($Field) | |
| $FieldValue.TermGuid = $TaxFieldItem.ID | |
| $FieldValue.Label = $TaxFieldItem.Name | |
| $FieldCollection.Add($FieldValue) | |
| $Field.SetFieldValue($ListItem,$FieldCollection) | |
| } | |
| else | |
| { | |
| $Field.SetFieldValue($ListItem,$TaxFieldItem) | |
| } | |
| $ListItem.Update() | |
| Write-Tee "[r] New $FieldName Value: $(($ListItem[$FieldName]).Label -join ";")" | |
| } | |
| catch { Get-Catch } | |
| } | |
| } | |
| function Get-UrlParents([string]$Path, [string]$Replace) | |
| { | |
| $Rents = @() | |
| if($Replace){ $Path = $Path.Replace($Replace,"") } | |
| do | |
| { | |
| if($Path.StartsWith("/")){ $Path = $Path.Substring(1) } | |
| $Pos = $Path.IndexOf("/") | |
| if($Pos -ne -1){ $Val = $Path.Substring(0,$Pos) } | |
| if($Pos -ne -1){ $Path = $Path.Substring($Pos) } elseif ( $Path.Length -gt 0 -and $Path -notmatch "/" ){ $Val = $Path } | |
| $Rents += $Val | |
| } | |
| while($Path -match "/") | |
| $Rents | |
| } | |
| #endregion | |
| #region VARIABLES | |
| #Set the format for the log and CSV file date | |
| $DateFormat = Get-Date -Format yyyy-MM-dd_HHmm | |
| $ScriptFileName = "Set-SPMetaData" | |
| $LogPath = "C:\Scripts\Logs\$($ScriptFileName)" | |
| $LastIDFile = "$($LogPath)_LastID.txt" | |
| $LastResultsCountFile = "$($LogPath)_LastResultsCount.txt" | |
| #Included after this script runs to set the folder default values | |
| $MetaDataDefaultsScript = "C:\Scripts\Set-PSDefaultColumnValues.ps1" | |
| #Comment out the $CSV or $LogFile variables to prevent logging or export to CSV | |
| $LogFile = "$($LogPath)_$DateFormat.txt" | |
| #The CSV format is a ShareGate formatted CSV file | |
| $CSV = "$($LogPath)_$DateFormat.csv" | |
| #Establishing the EAP so my variable clearing loops don't erase it | |
| $ErrorActionPreference = "Continue" | |
| #Establish the error count variable so the Get-Catch function can increment the value | |
| $E = 0 | |
| #Set the list name and site where the list lives | |
| $WebName = "https://sp.contoso.com/customers" | |
| $ListName = "Production Docs" | |
| #Create the CSV results array | |
| $Result = @() | |
| #Establish a folder list so we know if we need to run the metadata defaults script | |
| $FolderList = @() | |
| <# | |
| The ListViewThreshold sets the maximum number of items to query. If the value is set to 100 the script will loop through all | |
| items in the library in chunks of 100 in order to bypass the Sharepoint list view threshold limits. The $GT variable establishes | |
| the ID of the item to start with. While the script runs it dumps the last item ID to a file so it can pick up where it left off | |
| from the last run. If the file doesn't exist it starts at 0. | |
| #> | |
| $ListViewThreshold = 100 | |
| if(Test-Path $LastIDFile){ $GT = [int](Get-Content $LastIDFile) } else { $GT = 0 } | |
| if(Test-Path $LastResultsCountFile){ $LastResultsCount = [int](Get-Content $LastResultsCountFile) } else { $LastResultsCount = 0 } | |
| #Array of excluded fields that helps when trying to reuse the script in other libraries allowing dynamic field updating | |
| $Exclude = @( | |
| "IconOverlay","Source URL","Document Created By", | |
| "Document Modified By","Item Type","Client Id", | |
| "Document Concurrency Number","Name","Check In Comment", | |
| "Copy Source","Document ID","Version","App Created By", | |
| "App Modified By","Checked Out To","Edit","File Size", | |
| "Folder Child Count","Item Child Count", | |
| "Source Name (Converted Document)", | |
| "Source Version (Converted Document)","Shared With", | |
| "Persist ID","Edit Menu Table End", | |
| "Edit Menu Table Start","Has Copy Destinations", | |
| "Is Current Version","Level","Approver Comments", | |
| "Approval Status","Shared File Index","UI Version", | |
| "ID of the User who has the item Checked Out","Merge", | |
| "Content Type ID","HTML File Type", | |
| "Encoded Absolute URL","Type","GUID","Instance ID", | |
| "Is Checked out to local","Property Bag","Order", | |
| "owshiddenversion","Document Parent Identifier", | |
| "Effective Permissions Mask","ProgId", | |
| "Scheduling End Date","Scheduling Start Date","Relink", | |
| "Restricted","ScopeId","Select","Sort Type", | |
| "Document Stream Hash","Taxonomy Catch All Column", | |
| "Taxonomy Catch All Column1","Template Link", | |
| "Unique Id","Virus Status","Workflow Instance ID", | |
| "Workflow Version","HTML File Link","Is Signed" | |
| ) | |
| #Included note field types since note fields are filtered out in the $Fields variable | |
| $Include = @("DocumentSetDescription","BaseName") | |
| #Special case fields and specific values | |
| $MetadataFields = [ordered]@{ | |
| Customer = $null | |
| "Client Code" = $null | |
| "End User" = $null | |
| "Business Unit" = "ATMS" | |
| Practice = $null | |
| "Division/Team" = "From ERP" | |
| Industry = "From ERP" | |
| "Document Type" = $null | |
| "Process Step" = "Production / Support" | |
| } | |
| #Managed metadata variables | |
| $TaxSite = Get-SPSite "https://sp.contoso.com" | |
| $TaxName = "SP Managed Metadata Service Proxy" | |
| $GroupName = "Contoso" | |
| <# | |
| Each termset is looped through based on its index in the array. There's a matching $TermParent index item that's used in the loop. | |
| $TermParent items that are arrays will traverse $y items deep where $TermParent item arrays within arrays will perform a GetAllTerms() | |
| request in order to pull all terms within the termstore under the selected parent. | |
| #> | |
| $TermSetName = @( | |
| "Customer", | |
| "Client Code", | |
| "Customer End User", | |
| "Business Unit", | |
| "Practice", | |
| "Division/Team", | |
| "CRM Industry End User", | |
| "Document Type","Process Steps" | |
| ) | |
| $TermParent = @( | |
| ".Customer", | |
| ".Client Code", | |
| ".Customer End User", | |
| "Business Unit", | |
| "Practice", | |
| @(".Division/Team",@(".ATMS Teams",".MS Managed Services",".AT Advanced Technologies",".BT Teams",".Corporate Overhead",".DS Teams")), | |
| ".CRM Industry End User", | |
| @(".Document Type",".Customer Centric DocType",".3-Production / Support"), | |
| ".Process Steps" | |
| ) | |
| $RemoveChars = @(" ","/") | |
| #Define the parent path to all the files | |
| $ParentPath = "Documents/" | |
| #Connect to the Metadata Service | |
| $TaxonomySession = Get-SPTaxonomySession -Site $TaxSite | |
| $TermStore = $taxonomySession.TermStores[$TaxName] | |
| #Bind to the Term Group you want to perform configurations against | |
| $Group = $TermStore.Groups | ?{$_.Name -eq $GroupName} | |
| #This loop creates variables based on the name of the term set and populates each variable with the terms | |
| for ($x=0; $x -lt $TermSetName.Count; $x++) | |
| { | |
| #Bind to Term Set you want to perform configurations against | |
| $TermSet = $Group.TermSets[$TermSetName[$x]] | |
| #Get the chosen term to enter the child terms | |
| if($TermParent[$x].Count -gt 1) | |
| { | |
| for ($y=0; $y -lt $TermParent[$x].Count; $y++) | |
| { | |
| if($TermParent[$x][$y].Count -gt 1) | |
| { | |
| $TermSet = $Group.TermSets[$TermSetName[$x]] | |
| $TermSet = $TermSet.GetAllTerms() | |
| } | |
| else | |
| { | |
| $TermSet = $TermSet.Terms | ?{$_.Name -eq $TermParent[$x][$y]} | |
| } | |
| } | |
| } | |
| else | |
| { | |
| $TermSet = $TermSet.Terms | ?{$_.Name -eq $TermParent[$x]} | |
| } | |
| #Bind to Terms within the Term Set | |
| $Terms = $TermSet.Terms | |
| $VarName = "Tax" | |
| $VarName += $(($MetadataFields.GetEnumerator().Name[$x]).ToString()) | |
| $RemoveChars | %{ $VarName = $VarName.Replace("$_","") } | |
| New-Variable -Name $VarName -Value $Terms -Force | |
| } | |
| <# | |
| Create additional taxonomy term variables with values that remove or replace the following characters. This logic allows | |
| an easier match between taxonomy and the ERP customer data values. This is used to match the client folder name with a | |
| taxonomy item or ERP customer item name when a customer value for the current item may not exist. Establishing the customer | |
| value allows the propagation of a numerous additional values. | |
| #> | |
| $RemoveCharacters = ".",",","&","＆" | |
| $ReplaceCharacters = " "," and " | |
| $TaxCustomerMatchable = @() | |
| $TaxClientCodeMatchable = @() | |
| $TaxEndUserMatchable = @() | |
| #Loop through the dynamically created $Tax* variables and search/replace/remove the characters in the above defined arrays | |
| $TaxCustomer | %{ | |
| $Match = New-Object PSObject | |
| $Match | Add-Member -MemberType NoteProperty -Name Name -Value $( | |
| $Name = [string]$_.Name | |
| foreach($CR in $RemoveCharacters){ $Name = $Name.Replace($CR,"") } | |
| foreach($CR in $ReplaceCharacters){ $Name = $Name.Replace($CR,"") } | |
| $Name | |
| ) | |
| $Match | Add-Member -MemberType NoteProperty -Name ID -Value $_.ID | |
| $TaxCustomerMatchable += $Match | |
| } | |
| $TaxClientCode | %{ | |
| $Match = New-Object PSObject | |
| $Match | Add-Member -MemberType NoteProperty -Name Name -Value $( | |
| $Name = [string]$_.Labels.Value[1] | |
| foreach($CR in $RemoveCharacters){ $Name = $Name.Replace($CR,"") } | |
| foreach($CR in $ReplaceCharacters){ $Name = $Name.Replace($CR,"") } | |
| $Name | |
| ) | |
| $Match | Add-Member -MemberType NoteProperty -Name ID -Value $_.ID | |
| $TaxClientCodeMatchable += $Match | |
| } | |
| $TaxEndUser | %{ | |
| $Match = New-Object PSObject | |
| $Match | Add-Member -MemberType NoteProperty -Name Name -Value $( | |
| $Name = [string]$_.Name | |
| foreach($CR in $RemoveCharacters){ $Name = $Name.Replace($CR,"") } | |
| foreach($CR in $ReplaceCharacters){ $Name = $Name.Replace($CR,"") } | |
| $Name | |
| ) | |
| $Match | Add-Member -MemberType NoteProperty -Name ID -Value $_.ID | |
| $TaxEndUserMatchable += $Match | |
| } | |
| <# | |
| Variables to exclude from the variable clearing loops. Variables generated during the for loop and metadata item value section | |
| are cleared after the loop finishes to prevent false positives or value overlaps. | |
| #> | |
| $ExcludedVars = @("PrevCustomerFolder","CustomerFolder","Obj","Value","MetaFieldVariables","StartVariables","Customer") | |
| #Variable clearing script blocks that are executed below | |
| $MetaFieldVarsCleared = { | |
| $MVC = 0 | |
| Get-Variable | ?{ $MetaFieldVariables -notcontains $_.Name -and $ExcludedVars -notcontains $_.Name } | | |
| %{ Clear-Variable -Name $_.Name -Force -ErrorAction SilentlyContinue; $MVC++ } | |
| Write-Tee "[d] Cleared Meta Field Vars: $MVC" | |
| } | |
| $StartVarsCleared = { | |
| $SVC = 0 | |
| Get-Variable | ?{ $StartVariables -notcontains $_.Name -and $ExcludedVars -notcontains $_.Name } | | |
| %{ Clear-Variable -Name $_.Name -Force -ErrorAction SilentlyContinue; $SVC++ } | |
| Write-Tee "[d] Cleared Start Vars: $SVC" | |
| } | |
| $ClearExcludedVars = { | |
| Get-Variable | ?{ $ExcludedVars -contains $_.Name } | | |
| %{ Clear-Variable -Name $_.Name -Force -ErrorAction SilentlyContinue } | |
| } | |
| #region SQL QUERY | |
| #Establish the SQL connection to the ERP system | |
| #Database server IP or hostname | |
| $DS = "ERPSQL1" | |
| $User = "ERPRead" #read-host -prompt " - Enter the SQL user" | |
| #This is a file where an encrypted password is stored and then decrypted dynamically so the script can be scheduled | |
| $Pwd = read-host -prompt " - Enter SQL password" -AsSecureString | |
| $DB = "ERP" | |
| $CS = "Server=$DS;uid=$User;pwd=$Pwd;Database=$DB;Integrated Security=False;" | |
| $CONN = New-Object System.Data.SqlClient.Sqlconnection | |
| $CONN.connectionString = $CS | |
| $CONN.Open() | |
| $SQLQuery = @" | |
| SELECT | |
| MRC.GPID AS 'ID', | |
| MRC.CustomerName AS 'Name', | |
| MRC.CustomerType AS 'Type', | |
| MRC.CustomerCode_ChannelPartner AS 'CPCode', | |
| MRC.CustomerName_ChannelPartner AS 'CPName', | |
| MRC.GPID_ChannelPartner AS 'CPGPID', | |
| MRC.Owner_BusinessUnitID_Abbreviation AS 'BU', | |
| CUS.ClientKey AS 'Key', | |
| CUS.TeamName AS 'Division/Team', | |
| CUS.TeamLeader AS 'TeamLead', | |
| CUS.Industry AS 'Industry', | |
| CUS.AccountManager AS 'AccountManager' | |
| FROM | |
| ( SELECT * FROM V_SharePoint WHERE CustomerCode <> '' ) AS MRC LEFT OUTER JOIN | |
| ( SELECT | |
| ClientKey, | |
| CompanyName, | |
| ClientID, | |
| IsActive, | |
| TeamName, | |
| TeamLeader, | |
| Industry, | |
| AccountManager | |
| FROM V_CUST_List | |
| ) AS CUS ON MRC.CustomerCode = CUS.ClientID | |
| "@ | |
| $CMD = $CONN.CreateCommand() | |
| $CMD.CommandText = $SQLQuery | |
| $Load = $CMD.ExecuteReader() | |
| $ERPCustomers = New-Object System.Data.DataTable | |
| $ERPCustomers.Load($Load) | |
| $ERPCustomers = @($ERPCustomers | ?{($_.Name).Replace(" "," ")}) | |
| $TC = $ERPCustomers.Count | |
| #endregion | |
| #Establish the connection to Sharepoint and and collect initial list data. | |
| $WebScope = Start-SPAssignment | |
| $Web = $WebScope | Get-SPWeb $WebName | |
| $List = $Web.Lists[$ListName] | |
| #Loop through the fields to generate an array of fields to include | |
| $Fields = $List.Fields | ?{$Exclude -notcontains $_.Title -and $_.TypeAsString -ne "Note"} | |
| if($Include){ $Fields += $List.Fields | ?{ $Include -contains $_.StaticName } } | |
| #In case there's duplicates select unique fields | |
| $Fields = $Fields | Select -Unique | |
| #Get the total file and checked out files count to establish the total number of visible files | |
| $CheckedOutFilesCount = $List.CheckedOutFiles.Count | |
| $ListItemCount = $List.ItemCount | |
| $ListTotalItemCount = $ListItemCount-$CheckedOutFilesCount | |
| #endregion | |
| Write-Tee @" | |
| ============================================================================================== | |
| Contoso Dynamic Metadata Field Values Script $(Get-Date) | |
| ------------------------------------------------------------------------------------------- | |
| List = $ListName | |
| Items = $ListTotalItemCount | |
| LVT = $ListViewThreshold | |
| Last Result# = $LastResultsCount | |
| StartID = $GT | |
| ============================================================================================== | |
| "@ -NoPrefix -Overwrite | |
| <# | |
| When running in the ISE multiple times if you stop the script before variables are cleared you run into major issues. | |
| This makes sure the variables are cleared so you don't have to manually run the script blocks. | |
| #> | |
| if($StartVariables){ $K = &$StartVarsCleared | Out-Null } | |
| if($MetaFieldVariables){ $K = &$MetaFieldVarsCleared | Out-Null } | |
| &$ClearExcludedVars | |
| #region LOOP | |
| do | |
| { | |
| #CAML query string that sets the ID greater than field and orders by ID. The GT variable set to the last item ID at the end of the loop | |
| $QueryString = @" | |
| <Where> | |
| <Gt> | |
| <FieldRef Name="ID" /> | |
| <Value Type="Counter">$GT</Value> | |
| </Gt> | |
| </Where> | |
| <OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy> | |
| "@ | |
| #Create the CAML query to find the item recursively with the ListViewThreshold | |
| $Query = New-Object Microsoft.SharePoint.SPQuery | |
| $Query.ViewAttributes = 'Scope="RecursiveAll"' | |
| $Query.RowLimit = "$ListViewThreshold" | |
| $Query.Query = $QueryString | |
| $Items = $List.GetItems($Query) | |
| #Count the total in the query for the for loop | |
| $ItemCount = $Items.Count | |
| #Establish a list of variables available prior to the for loop start so new variables can be removed | |
| New-Variable -Name StartVariables -Value ( Get-Variable | %{ $_.Name } ) -Force | |
| for ($i=0; $i -lt $ItemCount; $i++) | |
| { | |
| #Simplify the $Items[$i] variable and mark it as a SPListItem object | |
| $SPItem = [Microsoft.SharePoint.SPListItem]$Items[$i] | |
| Write-Tee "[=] Current Item: $($SPItem.URL)" | |
| #Collect the content type so we can get a folder count for the metadata defaults script | |
| $ItemContentType = $SPItem.ContentType.Name | |
| Write-Tee "[d] Content Type: $ItemContentType" | |
| #Get an array of all items in the url and remove the last item so it's only parents | |
| $ParentFolders = New-Object System.Collections.ArrayList | |
| Get-UrlParents -Path $SPItem.URL -Replace $ParentPath | %{ $ParentFolders.Add($_) } | Out-Null | |
| #If this is not the top level folder create the parentfolder variable | |
| #If this is the top level folder add it to the metadata defaults folder list array | |
| if($ParentFolders.Count -gt 1) | |
| { | |
| $ParentFolders.Remove($ParentFolders[-1]) | |
| $ParentFolder = $ParentFolders[-1] | |
| } elseif($ItemContentType -match "Folder"){ $FolderList += $ParentFolders[0] } | |
| #Set the previous customer folder so we know when to clear the customer variable | |
| $PrevCustomerFolder = $CustomerFolder | |
| Write-Tee "[d] Previous Customer Folder: $PrevCustomerFolder" | |
| #Establish the current customer folder | |
| $CustomerFolder = $ParentFolders[0] | |
| #Create a matchable customer folder name | |
| $CustomerFolderMatchable = $CustomerFolder | |
| if($CustomerFolderMatchable) | |
| { | |
| foreach($RC in $RemoveCharacters){ $CustomerFolderMatchable = $CustomerFolderMatchable.Replace($RC,"") } | |
| foreach($RC in $ReplaceCharacters){ $CustomerFolderMatchable = $CustomerFolderMatchable.Replace($RC,"") } | |
| } | |
| Write-Tee "[d] Matchable Customer Folder Name: $CustomerFolderMatchable" | |
| if($PrevCustomerFolder -ne $CustomerFolder){ Write-Tee "[d] Customer Nulled"; $Customer = $null } else { Write-Tee "[d] Item Customer: $($Customer.Name)" } | |
| Write-Tee "[d] Current Customer Folder: $CustomerFolder" | |
| Write-Tee "[d] Parent Folder: $ParentFolder" | |
| #Custom column names for the ShareGate CSV | |
| $Hm = @{ | |
| SourcePath = "Server Relative Url" | |
| DestinationPath = "Server Relative Url" | |
| ContentType = "Content Type" | |
| Parent = "Path" | |
| } | |
| #Create an object to store the current item's values | |
| $Obj = New-Object PSObject | |
| foreach($F in $Fields) | |
| { | |
| Write-Tee "[d] Current Field: $($F.Title)" | |
| #Create a reusable title item | |
| $Title = [string]$($SPItem["Name"]) -replace '\.[^\.]*$','' | |
| #region SET VALUES | |
| #Set the title value using the name of the item if it doesn't exist | |
| if($F.Title -eq "Title") | |
| { | |
| if(-not $SPItem["Title"]) | |
| { | |
| $Value = $Title | |
| Write-Tee "[d] Setting Title: $Value" | |
| try | |
| { | |
| $SPItem[$F.Title] = $Value | |
| $SPItem.Update() | |
| Write-Tee "[r] New Title Value: $($SPItem[$F.Title])" | |
| } | |
| catch { Get-Catch } | |
| } | |
| } | |
| #Establish a list of variables prior to the metadata fields set section so we know what vars to clear | |
| New-Variable -Name MetaFieldVariables -Value ( Get-Variable | %{ $_.Name } ) -Force | |
| #If the item field is in the list of metadata fields process this | |
| if($MetadataFields.GetEnumerator().Name -contains $F.Title) | |
| { | |
| $TaxValue = $null | |
| if($F.Title -eq "Customer") | |
| { | |
| $CustomerValue = ($SPItem["Customer"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue]).Label | |
| if(-not $CustomerValue) | |
| { | |
| #Run through a series of matches to generate a customer metadata value and select value from the ERP | |
| if($PrevCustomerFolder -eq $CustomerFolder -and $Customer) | |
| { | |
| $TaxValue = $TaxCustomer | ?{ $_.Name -eq $Customer.Name -or $_.Name -eq ($Customer.Name).Replace("&","＆") } | |
| } | |
| elseif($TaxCustomerMatchable.Name -contains $CustomerFolderMatchable) | |
| { | |
| $TaxCustomerGUID = $TaxCustomerMatchable | ?{$_.Name -eq $CustomerFolderMatchable} | |
| $TaxValue = $TaxCustomer | ?{ $_.ID -eq $TaxCustomerGUID.ID } | |
| } | |
| elseif($TaxEndUserMatchable.Name -contains $CustomerFolderMatchable) | |
| { | |
| $TaxEndUserGUID = $TaxEndUserMatchable | ?{$_.Name -eq $CustomerFolderMatchable} | |
| $TaxValue = $TaxEndUser | ?{ $_.ID -eq $TaxEndUserGUID.ID } | |
| $EndUser = $ERPCustomers | ?{ $_.Name -eq $TaxValue.Name -or $_.Name -eq ($TaxValue.Name).Replace("＆","&") } | |
| } | |
| #Set the value with the generated taxonomy value from above if it exists | |
| if($TaxValue) | |
| { | |
| Write-Tee "[d] Metdata Item Value: $($TaxValue.Name)" | |
| #If there's an end user/channel partner for the customer change around the customer selection process | |
| $EndUserValue = ($SPItem["End User"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue]).Label | |
| if($EndUserValue){ Write-Tee "[d] Current End User Value: $EndUserValue" } | |
| if($EndUser -and -not $EndUserValue) | |
| { | |
| Write-Tee "[d] ERP End User: $($EndUser.Name)" | |
| $TaxValueEndUser = $TaxValue | |
| $Customer = $ERPCustomers | ?{ $_.Name -eq $EndUser.CPName } | |
| Write-Tee "[d] ERP Customer: $($Customer.Name)" | |
| #The amphersands are converted in the termset and cannot be matched with a default & so it's replaced with the correct version | |
| $TaxValue = $TaxCustomer | ?{ $_.Name -eq $Customer.Name -or $_.Name -eq ($Customer.Name).Replace("&","＆") } | |
| Write-Tee "[d] Metdata Item Value (Previous was End User): $($TaxValue.Name)" | |
| Set-MetadataFieldValue -ListItem $SPItem -FieldName "End User" -TaxFieldItem $TaxValueEndUser | |
| } | |
| else | |
| { | |
| Set-MetadataFieldValue -ListItem $SPItem -FieldName $($F.Title) -TaxFieldItem $TaxValue | |
| $Customer = $ERPCustomers | ?{$_.Name -eq $TaxValue.Name -or $_.Name -eq ($TaxValue.Name).Replace("＆","&")} | |
| } | |
| } | |
| } | |
| else | |
| { | |
| Write-Tee "[d] Current Customer Value: $CustomerValue" | |
| $Customer = $ERPCustomers | ?{ $_.Name -eq $CustomerValue } | |
| } | |
| #Select the industry item value if one is found in the ERP customer | |
| $IndustryValue = ($SPItem["Industry"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue]).Label | |
| if($IndustryValue){ Write-Tee "[d] Current Industry Value: $IndustryValue" } | |
| if($Customer.Industry -and -not $IndustryValue) | |
| { | |
| Write-Tee "[d] Current Inustry Value: $IndustryValue" | |
| Write-Tee "[d] ERP Industry: $($Customer.Industry)" | |
| #Confirm the item exists in the termset | |
| $TaxValueIndustry = $TaxIndustry | ?{$_.Name -eq $Customer.Industry} | |
| if($TaxValueIndustry){ Set-MetadataFieldValue -ListItem $SPItem -FieldName "Industry" -TaxFieldItem $TaxValueIndustry } | |
| } | |
| #Select the Team item value if one is found in the ERP customer | |
| $TeamValue = ($SPItem["Division/Team"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue]).Label | |
| if($TeamValue){ Write-Tee "[d] Current Division/Team Value: $TeamValue" } | |
| if($Customer."Division/Team" -and -not $TeamValue) | |
| { | |
| Write-Tee "[d] ERP Industry: $($Customer."Division/Team")" | |
| #Confirm the item exists in the termset | |
| $TaxValueTeam = $TaxDivisionTeam | ?{$_.Name -eq $Customer."Division/Team"} | |
| if($TaxValueTeam){ Set-MetadataFieldValue -ListItem $SPItem -FieldName "Division/Team" -TaxFieldItem $TaxValueTeam } | |
| } | |
| if($Customer){ Write-Tee "[d] Found ERP Customer: $($Customer.Name)" } | |
| } | |
| if($F.Title -eq "Client Code") | |
| { | |
| #Run through a series of matches to generate a client code metadata value and select value from the ERP | |
| $ClienCodeValue = ($SPItem["Client Code"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue]).Label | |
| if(-not $ClienCodeValue) | |
| { | |
| if($PrevCustomerFolder -eq $CustomerFolder -and $Customer) | |
| { | |
| $TaxValue = $TaxClientCode | ?{ $_.Name -eq $Customer.ID } | |
| } | |
| elseif($TaxClientCodeMatchable.Name -contains $CustomerFolderMatchable) | |
| { | |
| $TaxClientCodeGUID = $TaxClientCodeMatchable | ?{$_.Name -eq $CustomerFolderMatchable} | |
| $TaxValue = $TaxClientCode | ?{ $_.ID -eq $TaxClientCodeGUID.ID } | |
| } | |
| if($TaxValue) | |
| { | |
| $Customer = $ERPCustomers | ?{$_.ID -eq $TaxValue.Name } | |
| Set-MetadataFieldValue -ListItem $SPItem -FieldName $($F.Title) -TaxFieldItem $TaxValue | |
| } | |
| } | |
| else | |
| { | |
| Write-Tee "[d] Current Client Code Value: $ClienCodeValue" | |
| $Customer = $ERPCustomers | ?{ $_.Name -eq $ClienCodeValue } | |
| } | |
| if($Customer){ Write-Tee "[d] Found ERP Client Code: $($Customer.ID)" } | |
| } | |
| #Select the business unit item value if one is found in the ERP customer. If not found try to create one for the customer. | |
| $BUValues = ($SPItem["Business Unit"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValueCollection]).Label | |
| if($Customer -and -not $Customer.BU -and $BUValues) | |
| { | |
| if($BUValues.Count -gt 1) | |
| { | |
| if($BUValues[0] -eq "ATMS"){ $BV = "MS" } else { $BV = $BUValues[0] } | |
| Write-Tee "[d] Set Customer.BU to $BV" | |
| $Customer.BU = $BV | |
| } | |
| else | |
| { | |
| if($BUValues -eq "ATMS"){ $BV = "MS" } else { $BV = $BUValues } | |
| Write-Tee "[d] Set Customer.BU to $BV" | |
| $Customer.BU = $BV | |
| } | |
| #Since the ERP doesn't always contain a BU add it to our object if we found one | |
| if($BV){ $ERPCustomers | ?{$_.ID -eq $Customer.ID -and -not $_.BU; $_.BU = $BV } } | |
| } | |
| #Set the Business Unit value for the current item | |
| if($F.Title -eq "Business Unit" -and $BUValues){ Write-Tee "[d] Current BU Value(s): $BUValues" } | |
| if($F.Title -eq "Business Unit" -and $BUValues -notcontains $Customer.BU) | |
| { | |
| switch ($Customer.BU) | |
| { | |
| "BT" { $BU = "BT" } | |
| "DS" { $BU = "DS" } | |
| "MS" { $BU = "ATMS" } | |
| default { if($MetadataFields["Business Unit"] -ne $null){ $BU = $MetadataFields["Business Unit"] } } | |
| } | |
| $TaxValueBU = $TaxBusinessUnit | ?{ $_.Name -eq $BU } | |
| if($BU -and $TaxValueBU){ Set-MetadataFieldValue -ListItem $SPItem -FieldName "Business Unit" -TaxFieldItem $TaxValueBU -Multi } | |
| } | |
| #Set the Practive value for the current item | |
| $PracticeValue = ($SPItem["Practice"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValueCollection]).Label | |
| if($F.Title -eq "Practice") | |
| { | |
| if($PracticeValue){ Write-Tee "[d] Current Practice Value: $PracticeValue" } | |
| Write-Tee "[d] Practice Value from Name: $Title" | |
| #Try to match a Practice based on the names of the parent folders | |
| $PracticeMatch = @($TaxPractice.Labels.Value | ?{$ParentFolders -contains $_} | Select -First 1) | |
| if(($TaxPractice.Labels.Value -contains $Title -and $PracticeValue -notcontains $Title) -or | |
| ($PracticeMatch -and $PracticeValue -notcontains $PracticeMatch)) | |
| { | |
| if($TaxPractice.Labels.Value -contains $Title){ $PV = $Title } elseif ($PracticeMatch){ $PV = $PracticeMatch } | |
| Write-Tee "[d] Practice Match: $PV" | |
| $TaxValuePractice = $TaxPractice | ?{ $_.Labels.Value -eq $PV } | |
| if($TaxValuePractice){ Set-MetadataFieldValue -ListItem $SPItem -FieldName "Practice" -TaxFieldItem $TaxValuePractice -Multi } | |
| } | |
| } | |
| #Set the Document Type value for the current item | |
| $DocTypeValue = ($SPItem["Document Type"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue]).Label | |
| if($F.Title -eq "Document Type" -and $DocTypeValue){ Write-Tee "[d] Current Document Type Value: $DocTypeValue" } | |
| if($F.Title -eq "Document Type" -and -not $DocTypeValue) | |
| { | |
| Write-Tee "[d] Document Type Value from Name: $Title" | |
| #Try to match a Document Type based on the names of the parent folders | |
| $DocTypeMatch = @($TaxDocumentType.Labels.Value | ?{ $ParentFolders -contains $_ } | Select -First 1) | |
| #If a document type isn't matched from the parent folders then try from the file name/title | |
| if(-not $DocTypeMatch){ $DocTypeMatch = @($TaxDocumentType.Labels.Value | ?{ $Title -match $_ } | Select -First 1) } | |
| if($TaxDocumentType.Labels.Value -contains $Title -or $DocTypeMatch) | |
| { | |
| if($TaxDocumentType.Labels.Value -contains $Title){ $DV = $Title } elseif ($DocTypeMatch){ $DV = $DocTypeMatch } | |
| $TaxValueDocumentType = $TaxDocumentType | ?{ $_.Labels.Value -eq $DV } | |
| if($TaxValueDocumentType){ Set-MetadataFieldValue -ListItem $SPItem -FieldName "Document Type" -TaxFieldItem $TaxValueDocumentType } | |
| } | |
| } | |
| #Set the Process Step value | |
| $ProcessStepValue = ($SPItem["Process Step"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue]).Label | |
| if($F.Title -eq "Process Step" -and $ProcessStepValue){ Write-Tee "[d] Current Process Step Value: $ProcessStepValue" } | |
| if($F.Title -eq "Process Step" -and -not $ProcessStepValue) | |
| { | |
| $TaxValueProcessStep = $TaxProcessStep | ?{ $_.Name -eq $MetadataFields["Process Step"] } | |
| if($TaxValueProcessStep){ Set-MetadataFieldValue -ListItem $SPItem -FieldName "Process Step" -TaxFieldItem $TaxValueProcessStep } | |
| } | |
| } #if($MetadataFields.GetEnumerator().Name -contains $F.Title) | |
| #Clear the meta field variables created during this if section | |
| &$MetaFieldVarsCleared | |
| #endregion | |
| #Set the CSV values for the current field in the current item | |
| #Match the field type to the correct command to enumerate the value from the field | |
| if($F.TypeAsString -notmatch "Taxonomy") | |
| { | |
| $Value = $SPItem["$($F.Title)"] | |
| } | |
| elseif($F.TypeAsString -eq "TaxonomyFieldTypeMulti") | |
| { | |
| $Object = $SPItem["$($F.Title)"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValueCollection] | |
| $Value = ($Object.Label) -join ";" | |
| } | |
| elseif($F.TypeAsString -eq "TaxonomyFieldType") | |
| { | |
| $Object = $SPItem["$($F.Title)"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue] | |
| $Value = $Object.Label | |
| } | |
| if($Value -and $Value -match "＆"){ $Value = $Value.Replace("＆","&") } | |
| #Match the special headers and create the columns with the required values (ShareGate columns) | |
| if($Hm.Values -contains $F.Title) | |
| { | |
| $Names = ($HM.GetEnumerator() | ?{$_.Value -eq $F.Title}).Name | |
| foreach($N in $Names) | |
| { | |
| $Name = $N | |
| $HM.Remove($N) | |
| $Obj | Add-Member -MemberType NoteProperty -Name $Name -Value $Value -Force | |
| } | |
| } | |
| else | |
| { | |
| $Name = $F.Title | |
| $Obj | Add-Member -MemberType NoteProperty -Name $Name -Value $Value -Force | |
| } | |
| } #foreach($F in $Fields) | |
| #Add the item to the results object | |
| $Result += ($Obj | Sort-Object) | |
| #Clear the start variables | |
| &$StartVarsCleared | |
| #Output the last result count so we can pickup where we left off | |
| $LastResultsCount + $Result.Count | Out-File $LastResultsCountFile -Force | |
| } | |
| #Get the last item id and overwrite the greater than variable | |
| if($Result){ $LastID = ($Result.ID | Measure -Maximum).Maximum; $GT = $LastID } else { $LastID = $GT } | |
| #Output the last item id to file so we can pick up where we left off | |
| $LastID | Out-File $LastIDFile -Force | |
| #Generate a result count so the while loop will end correctly when we've started somewhere in the middle | |
| $ResultsCount = $LastResultsCount + $Result.Count | |
| Write-Tee @" | |
| ============================================================================================== | |
| LastID = $LastID | |
| GT = $GT | |
| Items = $ItemCount | |
| Results = $ResultsCount of $ListTotalItemCount | |
| Errors = $E | |
| ============================================================================================== | |
| "@ -NoPrefix | |
| if($ItemCount -eq 0){ break } | |
| } | |
| while($ResultsCount -lt $ListTotalItemCount) | |
| #Dispose of the Web object | |
| $Web.Dispose() | |
| Stop-SPAssignment $WebScope | |
| #endregion | |
| #Export a CSV of the results for review or ShareGate bulk metadata | |
| if($CSV -and $ItemCount -gt 0){ $Result | Sort SourcePath | Export-CSV -NoTypeInformation -Path $CSV -Force } | |
| #Include the folder meta data defaults script | |
| if($FolderList.Count -gt 0){ . $MetaDataDefaultsScript -Specific:$True -FolderList $FolderList } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment