2

I am working with SP 2010 Sharepoint Lists where the list items is having unique column " Document No" and each items are having "version". Each time the list items are getting updated/New items are created with the same document no and with new version no(incremental) through workflow. I would like to keep one list item for any Document number and the Version no should be latest.

Below is my sample data and how it looks

enter image description here

I am looking for some trick/Suggestion to get this done. Below is the code which will fetch the list items where all the column values are same. But i would like to compare only two columns "Document Number" and " Document version" and delete duplicate values (it doesnt matter if other columns are having different values)

Add-PSSnapin Microsoft.Sharepoint.Powershell
$w = get-spweb "www.contosa.com"            
$l = $w.Lists.TryGetList("Test");            
$items = $l.Items;            
$f = $l.Fields["Check In Comment"];            
$listType = $l.GetType().Name;            
foreach($item in $items)            
{            
    $itemTitle = $item.Title;            
    if($listType -eq "SPDocumentLibrary")            
    {            
        if($itemTitle -eq ""){$itemTitle = $item["Name"];}            
    }              
    if($item.Versions.Count -gt 0){            
        $vtr = $item.Versions.Count;            
        Write-Host "$itemTitle, has $vtr versions" -foregroundcolor Green;            
    }            
}
  • Hi guys, I have similar requirement, I appreciate your help – raju Aug 9 '17 at 7:02
  • Hi guys, I need to check 3 column similar like above requirement, I appreciate your help. – Akshay Jul 31 '18 at 7:01
0

This code should do the work - please adjust variables to match Your enviroment. And You can switch between READ and DELETE mode by changing variable $selectTRUE_or_deleteFALSE - I suggest You to execute READ first to be sure, that code works as expected and verify items that will be deleted. Only after that, execute in DELETE mode.

# VARIABLES
$columnName1 = "ColumnInternalName_DocumentNumber"
$columnName2 = "ColumnInternalName_VersionNumber"
$siteUrl = "https://www.contoso.com"  
$libraryName = "TargetLibraryTitle"
$selectTRUE_or_deleteFALSE = $true

$web = $null
$web = Get-SPWeb $siteUrl
$web.title

$list = $null
$list = $web.lists[$libraryName]
$list.title

# 1 - get all items and collect "Document Number" values
$documentNumbers = @()
$query = New-Object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'"
$query.RowLimit = 100
do
{
    $items = $list.GetItems($query)
    foreach($item in $items)
    {
        $documentNumbers += $item[$columnName1]     
    }   

    $query.ListItemCollectionPosition = $items.ListItemCollectionPosition
} 
while ($query.ListItemCollectionPosition -ne $null) 

# 2 - remove duplicates and get only unique values
$documentNumbers = $documentNumbers | select -Unique

# 3 - get all items again, but this time - query by document number and remove items based on lower version number
foreach($docNumb in $documentNumbers)
{
    $query = New-Object Microsoft.SharePoint.SPQuery
    $query.ViewAttributes = "Scope='Recursive'"
    $query.RowLimit = 100
    $query.Query = "<Where><Eq><FieldRef Name='$columnName1' /><Value Type='Text'>" + $docNumb + "</Value></Eq></Where>"
    do
    {
        # get all items with same document number
        $items = $null
        $items = $list.GetItems($query)

        # order items by version number, so highest number will be the first one
        $items = $items | Sort-Object -Property $columnName2 –Descending

        # get IDs
        $allIds = @();
        foreach($item in $items)
        {
            $allIds += $item.ID;
        }

        # $first will store item with highest version number and $idsToDelete will store all other ids
        $firstId, $idsToDelete = $allIds

        #delete all other items
        foreach($id in $idsToDelete)
        {
            $itemToDelete = $list.GetItemById($id)
            if($selectTRUE_or_deleteFALSE -eq $false)
            {
                $message = "-- " + $itemToDelete.Id + " // " + $itemToDelete.Url
                $itemToDelete.Delete();
                Write-Host " Item deleted: "$message
            }
            else
            {
                Write-Host " -- " $itemToDelete.Id " // " $itemToDelete.Url
            }
        }

        $query.ListItemCollectionPosition = $items.ListItemCollectionPosition
    } 
    while ($query.ListItemCollectionPosition -ne $null) 
}

$web.Dispose()

Write-Host "End" -b DarkGreen -f Green
Write-Host " "
Write-Host " "

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.