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
}
  • Do you need c# console code or javascript? – Hardik May 30 '16 at 10:11
  • As this is one time activity, you can use powershell for this. – Yayati May 30 '16 at 10:27
  • Remember, when you update items all Modified dates will be set to current time as well – Danny '365CSI' Engelman May 30 '16 at 10:27
  • and Modified By also will be set to current logged in user – Gaurravs May 30 '16 at 11:44
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()
| improve this answer | |
  • ehm if $item.update() is used modified column will change, shouldnt i use $item.SystemUpdate($false) instead ?. – user3800527 May 30 '16 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 '16 at 11:35
  • Is there something to disable workflows as well (just to be sure) – user3800527 May 30 '16 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 – user3800527 May 30 '16 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);
| improve this answer | |
  • It is really an answer to your question? – Aakash Maurya May 31 '16 at 4:17
  • All the same as my script. – Jussi Palo May 31 '16 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). – user3800527 May 31 '16 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. – user3800527 May 31 '16 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>
| improve this answer | |
  • is given solution works for you? – Hardik May 31 '16 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.
| improve this answer | |
  • i have 523 records.. – user3800527 May 30 '16 at 10:55

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.