1

I have a SharePoint list that has lots of columns and a priority column.

Now it used to be a high - medium - low column.

However last year they changed the ideas behind that, and we have now 1,2,3,4 as priority.

Its all working nice, but when I now filter on items the old values still appear. They should translate high=1 and medium = 2 and low = 4

The problem there are a lot of records, I need a way to automatically update those values. And only change them if they have the old high-medium-low status.

Update some code, as related to powershell scripting this Some how this code doesnt work i dont see whats wrong with it. Its just a first step to only show something so i see the lists is traversed

Add-PSSnapin Microsoft.SharePoint.PowerShell 
$webURL = "https://infodesk.company.com"
$listName = "Tickets"
$web = Get-SPWeb -site $webURL
$list = $web.Lists[$listName]
$items = $list.items
Foreach($item in $items)
{
  Echo $item
}
4
  • Do you need c# console code or javascript?
    – Hardik
    May 30, 2016 at 10:11
  • As this is one time activity, you can use powershell for this.
    – Yayati
    May 30, 2016 at 10:27
  • Remember, when you update items all Modified dates will be set to current time as well May 30, 2016 at 10:27
  • and Modified By also will be set to current logged in user
    – Gaurravs
    May 30, 2016 at 11:44

4 Answers 4

2

Using PowerShell it would go something like this, assuming you're replacing value in same field.

If you need to update other field with the new value, just update the field name inside the IF statements:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$webURL = "https://site.company.com"
$listName = "yourlistname"
$web = Get-SPWeb $webURL
$list = $web.Lists[$listName]
$items = $list.items

Foreach($item in $items)
{
    if($item["PriorityColumn"] -eq "high")
    {
        $item["PriorityColumn"] = "1"    
    }    
    elseif($item["PriorityColumn"] -eq "medium")
    {
        $item["PriorityColumn"] = "2"
    }
    elseif($item["PriorityColumn"] -eq "low")
    {
        $item["PriorityColumn"] = "4"
    }    

    $item.SystemUpdate($false)
}

$web.Dispose()
11
  • ehm if $item.update() is used modified column will change, shouldnt i use $item.SystemUpdate($false) instead ?.
    – Peter
    May 30, 2016 at 11:09
  • That is correct, use SystemUpdate if you don't want Modified, ModifiedBy reflect this update, as well as don't want to create new versions if versioning is enabled.
    – Jussi Palo
    May 30, 2016 at 11:35
  • Is there something to disable workflows as well (just to be sure)
    – Peter
    May 30, 2016 at 11:37
  • Okay tried it all together now, but its not working,.. That $ListName, is that the friendly name or should it be an url as well ?. Somehow i dont see any error
    – Peter
    May 30, 2016 at 11:53
1

Thank to @Jussi palo, I got the basic idea for the script. I eventually ended up using the script below for some unknown reason the selection of items didn't work (maybe because it wasn't a standard SharePoint column?, well I can only guess.

if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

#disable event firing
$myAss =    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint");
$type = $myAss.GetType("Microsoft.SharePoint.SPEventManager");
$prop = $type.GetProperty([string]"EventFiringDisabled",[System.Reflection.BindingFlags] ([System.Reflection.BindingFlags]::NonPublic -bor [System.Reflection.BindingFlags]::Static));
$prop.SetValue($null, $true, $null);

$sourceWebURL = "https://infodesk.company.com/IT"
$sourceListName = "Tickets"

$spSourceWeb = Get-SPWeb $sourceWebURL
$spSourceList = $spSourceWeb.Lists[$sourceListName]

$spSourceItems = $spSourceList.Items | where {$_['Priority'] -eq 'NORMAL'}

[int]$count = 0
ForEach($item in $spSourceItems)
{
  $status = $item['Priority']
  if($status = "NORMAL")
  {
    $count++
    Write-Host $count, $item['Priority'],$item['Title']

    #Unmark below to activate this script
    #$item["Priority"] = "3"
    #$item.SystemUpdate($false)  #this updates without changing modified
    #Write-Host $item['Priority'],$item['Title']
  }
}
# enable event firing
$prop.SetValue($null, $false, $null);
4
  • It is really an answer to your question? May 31, 2016 at 4:17
  • All the same as my script.
    – Jussi Palo
    May 31, 2016 at 6:45
  • The differences are indeed minimal, so i'm not sure why this one works and the other didnt, but did work in your case, i wasnt getting the column items back; maybe cause its a custom column by a vendor (not standard sharepoint column).
    – Peter
    May 31, 2016 at 7:18
  • @Aakash Morya Yes it is, around 500 wrong entries have been corrected with it in just 10 sec. To do it safely i first did a run to list the names, then altered code (unmarked) to set values i did this per wrong priority entry. Could have done it all in once, but i just play on the safe side. Without the script doing it manually would have altered modified column what would have resulted in wrong reports.
    – Peter
    May 31, 2016 at 7:21
0

Below is the sample code to change the PriorityColumn (Your columns name) values from high to 1.

You can do same for other values like medium & low for by changing the parameters in query.

<script type="text/javascript">
    var collListItemToBeUpdated = "";
    var listItemToBeUpdated = "";
    function getItemsToBeUpdated() <--- this function called on button click to get ID's
    {
         var clientContext = new SP.ClientContext.get_current();
         var oList = clientContext.get_web().get_lists().getByTitle('rohit_list1');
         var camlQuery = new SP.CamlQuery();
         camlQuery.set_viewXml('<View><Query><Where>' +
            '<FieldRef Name=\'PriorityColumn\'/><Value Type=\'text\'>high</Value>' +
            '</Where></Query></View>');
         collListItemToBeUpdated = oList.getItems(camlQuery);
         clientContext.load(collListItemToBeUpdated);
         clientContext.executeQueryAsync(getItemsToBeUpdatedSuccess, getItemsToBeUpdatedFailed);
    }
    function getItemsToBeUpdatedSuccess()
    {
         ListItemToBeUpdated= collListItemToBeUpdated.getEnumerator();
         updateMultipleListItems();
    }
    function getItemsToBeUpdatedFailed(sender, args)
    {
        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
    }

    function updateMultipleListItems() <--- this function called on successfully getting ID's
    {   
        var itemArray = [];
        var clientContext = SP.ClientContext.get_current();
        var oList = clientContext.get_web().get_lists().getByTitle('rohit_list1');

         while(ListItemToBeUpdated.moveNext())
         {
            var oItem = ListItemToBeUpdated.get_current();
            var oListItem = oList.getItemById(oItem.get_id());
            oListItem.set_item('PriorityColumn', '1');  
            oListItem.update();
            itemArray.push(oListItem);
            clientContext.load(itemArray[itemArray.length-1]);
         }  
        clientContext.executeQueryAsync(updateMultipleListItemsSuccess, updateMultipleListItemsFailed);
    }
    function updateMultipleListItemsSuccess() 
    {    
        alert('Items Updated');
    }

    function updateMultipleListItemsFailed(sender, args) 
    {    
        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
    }
    </script>
1
  • is given solution works for you?
    – Hardik
    May 31, 2016 at 12:08
0

Why do you want to use a script? Are there hundreds or thousands record that you need to update? If the amount of items is not that big it is a lot easier, especially of you are not a developer, to do it manually using Excel. Try doing the following:

  • Create a view that only shows items with old priority values
  • Export list the Excel
  • Perform search and replace in your excel sheet
  • Copy the updated values to SharePoint using the Quick Edit mode (also using the view you used for exporting). You can copy only the Priority column so other columns are not updated.
1
  • i have 523 records..
    – Peter
    May 30, 2016 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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