Monday, February 17, 2014

SharePoint 2013 App - Manage List Threshold (Moving Multiple Documents in Folders) Office365/ On-premise

This is a new app project that came when was working with SharePoint Online (Office 365), 
I had a Library with folders with more then 5000 documents and wasn't able to access the View, well... nothing new, only needed to make a query and move the documents, i used the same approach in SharePoint 2010 ECMAScript and done, batch 1000 by 1000 to another folder.

Then i think to myself how a normal user or "Admin" of a site (can IT or Business) will manage this?

I get surprise how people get stuck with this type of situations, even when they are instructed to avoid that, this happen very often.

Objective:
My goal was to create a simple tool using JSOM to migrate documents from folder with more then 5000 Documents and avoid the error message.
This solution audits folders, in a Document Library with a defined number of documents (e.g >5000 Documents). 
This Folder Url will be use to get the ID of the Documents and migrate to other/New Folder in the same Document Library and avoid the following error.



No, This is not another article talking about configuring the List Thresholds in Web application and Server side code, "SharePoint Online don't allow to change this values or make Server Side code".

If you are interested in that area please read this articles about that.
Here some articles talking about configuration and working with List Thresholds: 

Manage List Threshold Files App Solution

This solution was made using the ECMAScript and JSOM to track and make bulk migration of Documents.





This Solution can be implemented in the following tested environments:
  • Office 365
  • SharePoint 2013 Standard/Enterprise
App Manifest:
  • Hosting-Type: SharePoint-Hosted
  • Scope: Web
  • Permissions: Write
Tools:
  • Visual Studio 2013
    • SharePoint App
  • Fiddler
  • Internet Explorer (Developer Tools)
  • You can use "Napa" Office 365 Development Tools to 
Who should Test:
  • Developers in their Test Environment with Know-How of SharePoint Lists and SharePoint JSOM.
PS: This solution wasn't validated with all situations and errors could appear.
This is a example made with JSOM and SharePoint Online.
I don't recommend publish in production. If you made, will be by your own risk.

Functionality of the Solution





Audit Folders:
  • This section give the option to select the Document Library and make a auditory to identify the number of items by Folder and Sub-folder.
  • It is possible filter the Search by the numbers of Document by Folder.
  • By default the value is 0, but you can change to 5000 to return folder with more than 5000 Documents.
  • The option "Copy url" will copy the serverRelativeUrl to "List Folder" field in "Get Items ID".

Get Items  ID:
  • This section gives the option to select a Folder and retrieve the ID's Files.  This array of ID's will be use to make the bulk move.
  • It is possible to filter a define number of ID's to be moved,  (max. is 1000) because of specific REST Url Query.
  • The ID's helps the bulk moving of Documents.


Move Items/Files:
  • In this section it is possible to create Sub Folders in a Document Library
    • Add a new Folder Path in the Destination Folder and select the option "Create Folder"

·         Move Document to another Folder
o   Select the folder where the documents should move. Copy paste the ID's from the Tab "Get Items ID" and paste in Tab "Move Items/Files"   
o   Select the option "Move Items/Files" and wait until the process is finish.



Boring Part: 
  • You can only move 1000 documents each time and that takes time...


Reason and Tests associated:

If you Query a List with more then 5000 items, the list view threshold of SharePoint will block and provide a error Message.

This issue was very well manage in this App using the SharePoint 2010 REST call "_vti_bin/ListData.svcto the Res(T)cue.

But made some tests using the CAML and REST (the new _api from 2013) using JSOM to get other possible solutions, but the results weren't good...

Here are the results.

CAML

CAML with Filter:

This CAML Query will return the threshold  error  because the Document Library has more then 5000 item, value defined by default in the Web Application threshold option "even when i call only 1000 items".

Example:
camlQuery.set_viewXml('<View Scope=\'RecursiveAll\'><Query><Where><And><Eq><FieldRef Name=\'FSObjType\' /><Value Type=\'int\'>0</Value></Eq><Eq><FieldRef Name=\'FileDirRef\' /><Value Type=\'Lookup\'>'/sites/Dev/Document/Example'</Value></Eq></And></Where></Query><ViewFields><FieldRef Name=\'ID\' /><FieldRef Name=\'FileDirRef\' /></ViewFields> <RowLimit>1000</RowLimit></View>');

Error message:

"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."


Output Image:


CAML noFilter:

This CAML Query return the first 5000 items but it is not possible make filters.
This is not good because if you have different folders, you aren't sure about the values that are fetched. 
If you try to make any type of filter will return the threshold error.

Example work for 5000:
camlQuery.set_viewXml('<View Scope=\'RecursiveAll\'><Query><Where></Where></Query><RowLimit>5000</RowLimit></View>');

Error:
If you try to include a folder url will be consider as filter and then returna "threshold" error.
camlQuery.set_folderServerRelativeUrl('/sites/Dev/Document/Example');


Output Image:


REST

After testing the CAML Query, the same Test's were made using REST url's and you will see the same behavior in this methods 

REST by Folder

"/sites/Dev/_api/SP.AppContextSite(@target)/web/getFolderByServerRelativeUrl('/sites/Dev/Document/Example')/Files?@target='hostweburl'

Output Image:


REST with Filter

/sites/Dev/_api/SP.AppContextSite(@target)/web/lists/getbytitle('Document')/Items?$top=5000&$filter=(FileDirRef eq '/sites/Dev/Document/Example')&@target='hostweburl'

Output Image:



REST All Items 


This REST URL Query return 5000 Items from a Document Library but with no filter associated.

/sites/Dev/_api/SP.AppContextSite(@target)/web/lists/getbytitle('Document')/Items?$top=5000&@target='hostweburl'

Output Image:




REST ListData.svc

After this Tests the only one that was able to make query in Folder with more then 5000 items was the _vti_bin/ListData.svc/(documentLibrary) and (query associated).
After this the app was able to call the Document Library Data in a specific Filter.
For this case was use the following filter:
 $filter=((Path eq '[Url to Folder]') and (ContentType ne 'Folder'))





ECMAScript Methods:

Get the ID's of a Folder with more then 5000 Documents 
The Max REST call is 1000 items)

Get ID of Documents in Document Library Method
executor.executeAsync(
      {
          url:
              appweburl + "/../_vti_bin/ListData.svc/" + Document + "?$top="+item+"&$select=Id&$filter=((Path eq '" + FolderFilter + "') and (ContentType ne 'Folder'))"
              ,
              method: "GET",
          headers: { "Accept": "application/json; odata=verbose" },
          success: function (data) {     
              var jsonObject = JSON.parse(data.body);
              var results = jsonObject.d;
              if (results.length > 0) {
                  onDataReturned(results);
              }
              else {
                  CloseWaitForm();
                  alert("No result!!");
              }
          },
          error: function (xhr) {
              CloseWaitForm();
              alert(xhr.status + ': ' + xhr.statusText);
          }
      }


Open Folder Tree view Method

function LaunchTargetPicker(TextBoxId) {

    var callback = function (dest) {
        if (dest != null && dest != undefined && dest[3] != null) {
 //return the Folder URL
            $("#" + TextBoxId).val($("#IdLists option:selected").val().split(";")[0] + dest[3]);
        }
    };

    var iconUrl = "/_layouts/15/images/smt_icon.gif?rev=32";
    SP.SOD.executeFunc('pickertreedialog.js', 'LaunchPickerTreeDialogSelectUrl', function () {
//Open the Treview in the Document Library URL
        LaunchPickerTreeDialogSelectUrl('CbqPickerSelectListTitle', 'CbqPickerSelectFolderText', 'websListsFolders', $("#IdLists option:selected").val().split(";")[1], appweburl + "/../", $("#FolderFilter").val(), '', '', iconUrl, '', callback, 'true', '');
    });

}



Move Documents Method
function MoveItems() {
    Items = $('#CopyItemID').val().split(";");
    CountMov = 0;

    context = new SP.ClientContext(appweburl);
    var factory = new SP.ProxyWebRequestExecutorFactory(appweburl);
    context.set_webRequestExecutorFactory(factory);
    app = new SP.AppContextSite(context, hostweburl);
    var site = app.get_web();
    var list = site.get_lists().getByTitle($("#IdLists option:selected").text());
    currentItem = list.getItemById(Items[CountMov]);
    file = currentItem.get_file();
    context.load(currentItem, 'File.Name');

    context.executeQueryAsync(MoveFileHandler, onQueryFailed);
}

function MoveFileHandler() {
    try {
        if (file != null) {
            var _destinationlibUrl;
            if ($('#DestinationFolder').val().slice(-1) == "/")
            { _destinationlibUrl = $('#DestinationFolder').val() + file.get_name(); }
            else {
                _destinationlibUrl = $('#DestinationFolder').val() +"/"+ file.get_name();
            }
            file.moveTo(_destinationlibUrl, SP.MoveOperations.allowBrokenThickets);
            context.executeQueryAsync(MoveSuccess, onQueryFailed);
        }
    } catch (e) {
        alert(e.message);
    }

}


Get Folders Method

function GetFolders() {
    $('#Audit').html('');
   
    context = new SP.ClientContext(appweburl);
    var factory = new SP.ProxyWebRequestExecutorFactory(appweburl);
    context.set_webRequestExecutorFactory(factory);
    app = new SP.AppContextSite(context, hostweburl);
   
    var site = app.get_web();
    var list = site.get_lists().getByTitle($("#IdLists option:selected").text());
    folderCollection = list.get_rootFolder();
    context.load(folderCollection);
    context.executeQueryAsync(successHandler, onQueryFailed);

}
function successHandler(sender, args) {
        recursiveMethod(folderCollection);
}

The call the folder in a Recursive way. (back to school...)
I recommend to call the Folders in a recursive way, this avoid possible issues with the limit of the List View.
The recursive Method for folders with more than 5000 Documents was the only one that worked for me, even with this, it is not possible to get sub Folders from a Folder with more then 5000 Document using ECMAScript of Client Object Model.



Here is the Link for the Solution.


To resolve the threshold issue you can index the Columns in the List and use the 
http://blogs.msdn.com/b/sowmyancs/archive/2013/07/31/sharepoint-list-throttling-amp-indexed-columns.aspx
In the CAML Query you can use the normal Query but take in consideration the Orderby option with the parameter "UseIndexForOrderBy"
OrderBy Element
https://msdn.microsoft.com/en-us/library/office/ms467378.aspx

I hope you like this new article.

Support Links:
SharePoint 2013 Boundaries
Working with List View Thresholds in SharePoint 2013

SharePoint 2013 changing the list view limit

Post a Comment