Filtering large list by lookup column

It happened to me to filter a list by a lookup column, but only recently I applied a filter on a large list, with over 60,000 items, which of course exceeded the limit set by the administrator. I was confused. My filter did not return more that 200 items and I received the following error: “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.“.

This is kind of moment you do not want to see. Column is indexed, but still this error. The issue actually was caused by the fact I didn’t filter the list by lookup id. Filtering large list by lookup is not a workaround and adding LooupId=’TRUE” in CAML query is solving your issue.

<View><Query><Where><Eq><FieldRef Name="InternalName" LookupId="TRUE" /><Value Type="Integer">100</Value></Eq></Where></Query></View>

Add more items to a list in the same request

Again I bring into discussion JavaScript CSOM. I am not doing this just for fun, even I recognize SharePoint development can be fun. I am doing this because is probably the recommended way to add list items using CSOM, as more and more companies are migrating to Office 365, where no server side development is allowed on SharePoint Online.

You can find how to do this on Microsoft website, but my article is about something else. Is about how to have your code organized and how you can even insert more items in the same request. So, let’s start!

/// <Reference name="MicrosoftAjax.js" />


Shp.List = function (listName, webUrl) {
    /// <summary>Create an instance of Shp.Lists class</summary>
    /// <param name="listName" type="String" optional="false" mayBeNull="false">List name</summary>
    /// <param name="webUrl" type="String" optional="true" mayBeNull="false">Web URL</summary>
    var e = Function.validateParameters(arguments, [{ name: "listName", type: String, optional: false, mayBeNull: false },
                                                    { name: "webUrl", type: String, mayBeNull: false, optional: true }], true);
    if (e) throw e;

    this.listName = listName;
    this.ctx = (typeof (webUrl) === 'undefined') ? SP.ClientContext.get_current() : new SP.ClientContext(webUrl);
    this.oWeb = this.ctx.get_web();
    this.oList = this.oWeb.get_lists().getByTitle(this.listName);
    return this;

Shp.List.prototype = {

    add_items: function (listItems) {
        /// <summary>Add list items to the list</summary>
        /// <param name="listItems" type="Array" mayBeNull="false" optional="false" elementType="Object" elementMayBeNull="false">List items</param>
        var e = Function.validateParameters(arguments, [{ name: "listItems", type: Array, mayBeNull: false, optional: false, elementType: Object, elementMayBeNull: false }], true);
        if (e) throw e;

        var instance = this;
        var defferred = jQuery.Deferred();
        var results = [];

        for (var k = 0; k < listItems.length; k++) {
            var itemCreateInfo = new SP.ListItemCreationInformation();
            var oListItem = instance.oList.addItem(itemCreateInfo);
            // Set fields values
            var listItem = listItems[k];
            for (var field in listItem) {
                oListItem.set_item(field, listItem[field]);
       [ }

        instance.ctx.executeQueryAsync(function () {
        }, function (sender, args) {

        return defferred.promise();


I have created above an Shp.List object which can be initialized with two parameters. First one is the list name, which is mandatory, and the second one is web URL, which is optional. If you need to do your operation on a list in a sub-site, for example, please specify the URL of it. If you don’t specify the URL, client context will be created for current web.

Shp.List object was a method called “add_items”, which takes an array of objects as parameter and return a jQuery Deffered object. Let’s see how it can be used.

    var incident = {};
    incident['Title'] = 'Title 1';
    incident['Priority'] ='Priority 1';
    incident['TicketType'] ='Ticket type 1';

    var incident2 = {};
    incident2['Title'] = 'Title 2';
    incident2['Priority'] ='Priority 2';
    incident2['TicketType'] ='Ticket type 2';

    var items = new Shp.List("Tickets").add_items([incident, incident2]); (err) {

    items.done(function (items) {

        // Get the id of created items
        for (var i = 0; i < items.length; i++) {


Please note the property names for objects are internal names of the fields.

The next challenge for me is to create a way to insert a very big number of items calling the method once. Now it is working with a small number of items, but, if number of items is too big, you might receive “Request uses too many resources” error.

Stop SharePoint to store dates in UTC

Using client JavaScript CSOM, you can set the date and time fields values as you want. There is just a little issue. If you used JavaScript Date object SharePoint will store the date in UTC, not your local time, which might be or not the same or UTC, but probably not.

var dt = new Date();

But don’t worry about this. There is a workaround. You just need to convert the date into an ISO string. On SharePoint, Microsoft Ajax library offers by default a format function for Date object.

var dt = new Date();

Workaround is simple. Nothing complicated in this. In general, I think SharePoint is a great platform for development, if you find the right information.

Attach files to an item with JavaScript

Several times I was requested to develop a functionality to attach files to an item. Before HTML 5, it was difficult and I was using a Silverlight applet to encode a file in a binary string. Now, when HTML 5 is everywhere and new versions of SharePoint include a special API for this, the job is an easy task.

/// <reference name="MicrosoftAjax.js" />
/// <reference path="/_layouts/15/sp.js" />
/// <reference path="/_layouts/15.SP.RequestExecutor.js"" />

Shp.Attachments = function () {
    /// <summary>Shp Attachments static class</summary>
    throw 'Cannot initiate Shp.Attachments static class';

Shp.Attachments.get_file = function (fileInput) {
    /// <summary>This method is used to get the content of the file as binary string</summmary>

    var deffered = jQuery.Deferred();
    var reader = new FileReader();
    reader.onload = function (e) {

    reader.onerror = function (e) {

    return deffered.promise();

Shp.Attachments.add_multiple = function (listName, itemId, fileInputs, webUrl, success, fail) {
    /// <summaryAdd multiple attachments at once></summary>
    /// <param name="listName" type="String" optional="false" mayBeNull="false">List name</param>
    /// <param name="itemId" type="String" optional="false" mayBeNull="false">Item id</param>
    /// <param name="fileInputs" type="Array" elementType="HTMLElement" elementMayBeNull="false" optional="false" mayBeNull="false">File inputs</param>
    /// <param name="webUrl" type="String" optional="false" mayBeNull="true">Web URL</param>
    /// <param name="overwrite" type="Boolean" optional="false" mayBeNull="false">Overwrite</param>
    /// <param name="success" type="Function" optional="false" mayBeNull="false">Success</param>
    /// <param name="fail" type="Function" optional="true" mayBeNull="false">fail</param>
    var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, optional: false, mayBeNull: false },
                                                    { name: 'itemId', type: String, optional: false, mayBeNull: false },
                                                    { name: 'fileInputs', type: Array, optional: false, mayBeNull: false, elementMayBeNull: false, elementType: HTMLElement },
                                                    { name: 'webUrl', type: String, optional: false, mayBeNull: true },
                                                    { name: 'success', type: Function, optional: false, mayBeNull: false },
                                                    { name: 'fail', type: Function, optional: true, mayBeNull: false }], true);
    if (e) throw e;

    var webUrl = webUrl || _spPageContextInfo.webAbsoluteUrl;
    var fail = fail || function (err) { alert(err); };

    // We start to read the files
    var filesToRead = fileInputs.length;
    var readedFiles = [];;
    for (var k = 0; k < fileInputs.length; k++) {
        var fileInput = fileInputs[k];
        (function (ctl) {
            Shp.Attachments.get_file(ctl).done(function (fileContent) {
                var parts = ctl.value.split("//");
                var fileName = parts[parts.length - 1];
                readedFiles.push({ content: fileContent, name: fileName });
                if (readedFiles.length === filesToRead) {
                    Shp.Attachments._add_multiple(listName, itemId, readedFiles, webUrl, success, fail);

Shp.Attachments._add_multiple = function (listName, itemId, readedFiles, webUrl, success, fail) {

    var filesToAttach = readedFiles.length;
    var fileIndex = 0;

    // Remove duplicated files
    var filesNames = [];
    for (var n = 0; n < readedFiles.length; n++) {
        var readedFile = readedFiles[n];
        if (Array.contains(fileNames, === true) {
            Array.removeAt(readedFiles, n);

    function attach() {

        var fileContent = readedFiles[fileIndex].content;
        var fileName = readedFiles[fileIndex].name;

        Shp.Attachments._add(listName, itemId, fileContent, fileName, webUrl, function () {
            if (fileIndex === filesToAttach) {
            else {
        }, function () {
            alert('Cannot attach file: ' + fileName);



Shp.Attachments.add = function (listName, itemId, fileInput, webUrl, success, fail) {
    /// <summary>Add attachments</summary>
    /// <param>List name</param>
    /// <param>Item Id</param>
    /// <param>File input controls</param>
    /// <param>Web url</param>
    var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, optional: false, mayBeNull: false },
                                                    { name: 'itemId', type: String, optional: false, mayBeNull: false },
                                                    { name: 'filesInput', type: HTMLElement, optional: false, mayBeNull: false },
                                                    { name: 'webUrl', type: String, optional: false, mayBeNull: true },
                                                    { name: 'success', type: Function, optional: false, mayBeNull: false },
                                                    { name: 'fail', type: Function, optional: true, mayBeNull: false }], true);
    if (e) throw e;

    var webUrl = webUrl || _spPageContextInfo.webAbsoluteUrl;
    var fail = fail || function (err) { alert(err); };

    Shp.Attachments.get_file(fileInput).then(function (fileContent) {
        var parts = fileInput.value.split("\\");
        var fileName = parts[parts.length - 1];
        // Attachments add internal method
        Shp.Attachments._add(listName, itemId, fileContent, fileName, webUrl, success, fail);

Shp.Attachments._add = function (listName, itemId, fileContent, fileName, webUrl, success, fail) {
    /// <summary>Add attachment internal method. Not to use directly in your code/</summary>

    var scriptBase = webUrl + "/_layouts/15/";
    jQuery.getScript(scriptBase + "SP.RequestExecutor.js", function () {

        var executor = new SP.RequestExecutor(webUrl);
            url: webUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items(" + itemId + ")/AttachmentFiles/add(FileName='" + fileName + "')",
            method: "POST",
            binaryStringRequestBody: true,
            body: fileContent,
            state: "Update",
            success: function () {
            fail: function (data) {



I have created two functions to attach the files. One is called simple “add” and the other is name “add_multiple”. First one “Shp.Attachments.add” is a simple one and accepts following parameters:

  • List name as string
  • Item id as string
  • HTML file control as HTML element
  • Web URL. It can be nul and in this case is used _spPageContextInfo.webAbsoluteUrl.
  • Success method to be executed if attachment is added
  • Fail method to be executed if attachment failed. If not specified, a simple alert with error is displayed.
Shp.Attachments.add('Tickets', '2', document.getElementById('attachment1'), null, function (itemId) {
   }, function (err) {
       alert('Error: ' + err);

Shp.Attachments.add_multiple is similar with the first one. The only difference is that the 3rd parameter is an array of HTML input file elements.

var input_files = [document.getElementById('attachment1'), document.getElementById('attachment2'), document.getElementById('attachment3')];
Shp.Attachments.add('Tickets', '2',input_files, null, function (itemId) {
   }, function (err) {
       alert('Error: ' + err);

I hope the code will help to create some custom forms. It works on SharePoint on-premise and SharePoint online and was also tested in SharePoint add-in model. I can only advice you to extend it and adapt it to your needs.