none
Managing Large Sharepoint list with continuos in-flow of data (many list items created per day) RRS feed

  • Question

  • Hello All,

    I have a situation where I need to develop a pretty simple List form which records all the call logs that the help desk are receiveing. So there is a scope for many items to be created per day. What I am planning to do is...create one main list, which after save, redirects these list items to another seperate list(say one new list per year). So for example, it sends all the current year records to "ListName_2017" and starting from January 1st 2018, it has to create a new list(ListName_2018) automatically and redirect all the list items to this newly created list("ListName_2018"). And so on....

    NOTE: There shouldn't be any items saved in the MainList..all those shoudl be redirecte to their respective lists

    Please let me know if his type of approach is possible. If not what would be the best way to deal this situation(Completely OOB).

    Thanks in Advance!!


    Praneeth


    Tuesday, January 31, 2017 2:09 AM

Answers

  • Hi,

    We can move the list items using PowerShell to achieve it.

    Remove-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
    Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue 
    try
    {    
    	$srcListSiteUrl = "http://SharePoint/sites/Site1/"    
    	$SourceListName = "Source List"     
    	$dstListSiteUrl = "http://SharePoint/sites/Site2/"    
    	$DestinationListName = "Destination List"     
    	$keyColumnInternalName = "Title"     
    	$sourceListWeb = Get-SPWeb -identity $srcListSiteUrl    
    	$sourceListUrl = $sourceListWeb.ServerRelativeUrl + "/lists/" + $SourceListName;     
    	$dstListWeb = Get-SPWeb -identity $dstListSiteUrl    
    	$destinationListUrl = $dstListWeb.ServerRelativeUrl + "/lists/" + $DestinationListName;
    	$SourceList = $sourceListWeb.GetList($sourceListUrl);   
    	$DestinationList = $dstListWeb.GetList($destinationListUrl); 
    
    	#Filtering Items based on the Date as we do not want to archive all items in the list
    
    	$filterQuery = '<Where><And>				
    	<Geq>
    	<FieldRef Name = "Created" /> <Value IncludeTimeValue="TRUE" Type="DateTime">2016-01-01T00:00:00Z</Value>
    	</Geq>
    
    	<Leq>
    	<FieldRef Name = "Created" /> <Value IncludeTimeValue="TRUE" Type="DateTime">2016-12-31T00:00:00Z</Value>
    	</Leq>
    	</And></Where>'     
    
    	$CategoryQuery = new-object Microsoft.SharePoint.SPQuery
    	$CategoryQuery.Query = $filterQuery
    
    	$sourceSPListItemCollection = $SourceList.GetItems($CategoryQuery);
    	Write-Host Total Items to Copy : $sourceSPListItemCollection.Count  #Always better to know the number of items before we start copying to destination list
    
    	foreach($srcListItem in $sourceSPListItemCollection) 
    	{           
    		#CAML query of the common column (Title)
    		$keyValue = $srcListItem[$keyColumnInternalName]
    		Write-Host $keyValue
    		$camlQuery ='<Where><Eq><FieldRef Name='+$keyColumnInternalName+' /><Value Type="Text">'+$keyValue+'</Value> </Eq> </Where>'
    		$spQuery = new-object Microsoft.SharePoint.SPQuery
    		$spQuery.Query = $camlQuery
    		$spQuery.RowLimit = 1
    
    		#check if the item is already present in destination list
    
    		$destItemCollection = $DestinationList.GetItems($spQuery)
    
    		if($destItemCollection.Count -gt 0)
    		{
    			write-host "list item already exists, updating "
    			foreach($dstListItem in $destItemCollection) 
    			{  
    				foreach($spField in $dstListItem.Fields)
    				{
    					if ($spField.ReadOnlyField -ne $True -and  $spField.InternalName -ne "Attachments")
    					{
    						$dstListItem[$spField.InternalName] = $srcListItem[$spField.InternalName];
    					}
    				}
    				# Handle Attachments
    				foreach($leafName in $srcListItem.Attachments) 
    				{               
    					$spFile = $SourceList.ParentWeb.GetFile($srcListItem.Attachments.UrlPrefix + $leafName)
    					$dstListItem.Attachments.Add($leafName, $spFile.OpenBinary());
    				}              
    				$dstListItem.Update() 
    			} 
    		}        
    		else 
    		{            
    			write-host "adding new item" 
    
    			$newSPListItem = $DestinationList.AddItem(); 
    			foreach($spField in $srcListItem.Fields)
    			{
    				if ($spField.ReadOnlyField -ne $True -and  $spField.InternalName -ne "Attachments")
    				{ 
    					newSPListItem[$spField.InternalName] = $srcListItem[$spField.InternalName]; 
    				}
    			}
    			# Handle Attachments
    			foreach($leafName in $srcListItem.Attachments)
    			{                
    				$spFile = $SourceList.ParentWeb.GetFile($srcListItem.Attachments.UrlPrefix + $leafName)
    				$newSPListItem.Attachments.Add($leafName, $spFile.OpenBinary());
    			}            
    			$newSPListItem.Update()
    		} 
    	} 
    }
    catch 
    { 
    	write-host $_.exception 
    } 
    finally 
    {        
    	if($sourceListWeb -ne $null){$sourceListWeb.Dispose()}
    	if($dstListWeb -ne $null){$dstListWeb.Dispose()}
    }

    PowerShell Script to Copy/Replace items from one list to another list

    http://www.sptechlearn.com/2014/11/powershell-script-to-copyreplace-items-from-one-list-to-another-list.html

    Or create a timer job with some Server code to achieve it.

    http://www.c-sharpcorner.com/UploadFile/b8e86c/how-to-create-timer-job-in-sharepoint-2013/

    http://spcodes.blogspot.sg/2013/01/move-list-items-from-one-list-to-other.html

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by LoveSharePoint Thursday, February 2, 2017 6:18 PM
    Wednesday, February 1, 2017 7:42 AM
    Moderator

All replies

  • Hi,

    Why don't you put the logic on the service request status , Example, Pending,Progressing,Assigned and Resolved. Create the lists based on the Statuses. 

    On your main list, capture this status in workflow , and route (Copy ListItem) to respective list. This option not available in SPD 2013, you can switch to SP2010 workflow.

    Or CreateList item in SP2013 and keep track the changes in Main List and update the respective list.

    Hope it will help you.


    Murugesa Pandian | MCPD | MCTS | SharePoint 2010 |

    Tuesday, January 31, 2017 12:01 PM
  • Yeah, thanks for you response. But in that case wont the list containing all the resolved items be oversized over a period of time? (becausae mostly all the statuses will be marked as Resolved at some point and all these will be accumulated in a single list)

    Please advice.

    Thank you


    Praneeth

    Tuesday, January 31, 2017 5:23 PM
  • I would create a a simple task scheduler with PowerShell script to move the resolved/closed items to the list_{currentYear}. Make this job run daily/weekly/monthly per need. If the list is not available (Jan 1st) it will create a new list for the current year.

    ---
    Rajesh
    rjesh.com| @rjesh
    You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

    Tuesday, January 31, 2017 5:52 PM
  • @ Rajesh, Thank you.

    Can you please provide little insight on powershell script  examples!!. I donot have much exposure with powershell commands. I would really appreciate if you could post sample script for me to get started with.

    Thanks,



    Praneeth

    Tuesday, January 31, 2017 10:44 PM
  • Hi,

    We can move the list items using PowerShell to achieve it.

    Remove-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
    Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue 
    try
    {    
    	$srcListSiteUrl = "http://SharePoint/sites/Site1/"    
    	$SourceListName = "Source List"     
    	$dstListSiteUrl = "http://SharePoint/sites/Site2/"    
    	$DestinationListName = "Destination List"     
    	$keyColumnInternalName = "Title"     
    	$sourceListWeb = Get-SPWeb -identity $srcListSiteUrl    
    	$sourceListUrl = $sourceListWeb.ServerRelativeUrl + "/lists/" + $SourceListName;     
    	$dstListWeb = Get-SPWeb -identity $dstListSiteUrl    
    	$destinationListUrl = $dstListWeb.ServerRelativeUrl + "/lists/" + $DestinationListName;
    	$SourceList = $sourceListWeb.GetList($sourceListUrl);   
    	$DestinationList = $dstListWeb.GetList($destinationListUrl); 
    
    	#Filtering Items based on the Date as we do not want to archive all items in the list
    
    	$filterQuery = '<Where><And>				
    	<Geq>
    	<FieldRef Name = "Created" /> <Value IncludeTimeValue="TRUE" Type="DateTime">2016-01-01T00:00:00Z</Value>
    	</Geq>
    
    	<Leq>
    	<FieldRef Name = "Created" /> <Value IncludeTimeValue="TRUE" Type="DateTime">2016-12-31T00:00:00Z</Value>
    	</Leq>
    	</And></Where>'     
    
    	$CategoryQuery = new-object Microsoft.SharePoint.SPQuery
    	$CategoryQuery.Query = $filterQuery
    
    	$sourceSPListItemCollection = $SourceList.GetItems($CategoryQuery);
    	Write-Host Total Items to Copy : $sourceSPListItemCollection.Count  #Always better to know the number of items before we start copying to destination list
    
    	foreach($srcListItem in $sourceSPListItemCollection) 
    	{           
    		#CAML query of the common column (Title)
    		$keyValue = $srcListItem[$keyColumnInternalName]
    		Write-Host $keyValue
    		$camlQuery ='<Where><Eq><FieldRef Name='+$keyColumnInternalName+' /><Value Type="Text">'+$keyValue+'</Value> </Eq> </Where>'
    		$spQuery = new-object Microsoft.SharePoint.SPQuery
    		$spQuery.Query = $camlQuery
    		$spQuery.RowLimit = 1
    
    		#check if the item is already present in destination list
    
    		$destItemCollection = $DestinationList.GetItems($spQuery)
    
    		if($destItemCollection.Count -gt 0)
    		{
    			write-host "list item already exists, updating "
    			foreach($dstListItem in $destItemCollection) 
    			{  
    				foreach($spField in $dstListItem.Fields)
    				{
    					if ($spField.ReadOnlyField -ne $True -and  $spField.InternalName -ne "Attachments")
    					{
    						$dstListItem[$spField.InternalName] = $srcListItem[$spField.InternalName];
    					}
    				}
    				# Handle Attachments
    				foreach($leafName in $srcListItem.Attachments) 
    				{               
    					$spFile = $SourceList.ParentWeb.GetFile($srcListItem.Attachments.UrlPrefix + $leafName)
    					$dstListItem.Attachments.Add($leafName, $spFile.OpenBinary());
    				}              
    				$dstListItem.Update() 
    			} 
    		}        
    		else 
    		{            
    			write-host "adding new item" 
    
    			$newSPListItem = $DestinationList.AddItem(); 
    			foreach($spField in $srcListItem.Fields)
    			{
    				if ($spField.ReadOnlyField -ne $True -and  $spField.InternalName -ne "Attachments")
    				{ 
    					newSPListItem[$spField.InternalName] = $srcListItem[$spField.InternalName]; 
    				}
    			}
    			# Handle Attachments
    			foreach($leafName in $srcListItem.Attachments)
    			{                
    				$spFile = $SourceList.ParentWeb.GetFile($srcListItem.Attachments.UrlPrefix + $leafName)
    				$newSPListItem.Attachments.Add($leafName, $spFile.OpenBinary());
    			}            
    			$newSPListItem.Update()
    		} 
    	} 
    }
    catch 
    { 
    	write-host $_.exception 
    } 
    finally 
    {        
    	if($sourceListWeb -ne $null){$sourceListWeb.Dispose()}
    	if($dstListWeb -ne $null){$dstListWeb.Dispose()}
    }

    PowerShell Script to Copy/Replace items from one list to another list

    http://www.sptechlearn.com/2014/11/powershell-script-to-copyreplace-items-from-one-list-to-another-list.html

    Or create a timer job with some Server code to achieve it.

    http://www.c-sharpcorner.com/UploadFile/b8e86c/how-to-create-timer-job-in-sharepoint-2013/

    http://spcodes.blogspot.sg/2013/01/move-list-items-from-one-list-to-other.html

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by LoveSharePoint Thursday, February 2, 2017 6:18 PM
    Wednesday, February 1, 2017 7:42 AM
    Moderator
  • @Dennis, 

    Thanks a lot!!

    This would really help me get started with and achieve what I wanted to do. Thanks for providing those references as well.


    Praneeth

    Wednesday, February 1, 2017 7:08 PM
  • Hi Praneeth,

    If my reply help you, you can mark the reply as answer, it will make others who stuck with the similar issue easier to search for valid solutions in this forum.

    Best regards,
    Dennis


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, February 2, 2017 1:04 AM
    Moderator