Display SharePoint List or Library in Data table

Introduction:
Today we are going to learn an interesting article of how to use DataTables.js JQuery 🔗 in SharePoint(2013, 2016, Online - O365).
Since SharePoint 2013 has released, the way how the data is created, updated & displayed has changed a lot - I mean Client Side scripting has become more powerful. Server Side Coding in SharePoint (JSOM - Java Script Object Model) has the following uses or advantages over server side coding which are:
1. Faster data rendering
2. Easy to debug from browser
3. Easy & faster validation response to end users
4. Just add a content editor web part and add the code 😁
5. No IIS reset required
6. Just use client desktop and no SharePoint installation required
7. No Visual Studio (why spent extra money on license?😎)
8. Just use a Editor to write and format the code.
💡 I have recently started using Microsoft Visual Studio Code. Give a try, you will never use other editors. What a powerful editor! Download from this link and it is FREE 🔗
Scenario:

What if you were given a task to display a list or a library in a SharePoint page which has 1000's of records in it. Also, it has to load faster, need a free text search, paging features, etc.? But you have no server side access for coding or server deployments allowed 😜

Solution: 
Obviously, any SharePoint developer will go for the option SharePoint Java Script Object Model (JSOM). But how to create a table, searchable content, etc. DataTables.JS is a Table plug-in for JQuery. It is used for display the HTML content as Table.
Let's not wait and create a custom SharePoint list(here named it as Finance Sample Data). Download the data used for this example here 🔗
This is how the logic/solution for this scenario is implemented:
1. Upload the JQuery, DataTable.JS, CSS, Images required in Site Assets Library(best practice in SharePoint)
2. Create a simple text file in which add the reference to JS, CSS, Images, etc. Then add the DIV tags for creating a section and then add a TABLE tag for displaying the content, then document.ready() function and finally custom JS functions. You can also have HTML file and JS files separately.

3. In document.ready(), call the custom function which will query the data using CAML query (I haven't tried using REST API since there will be an issue when you query 1000 items at a time or when paging is enabled, if you have any thoughts share it in the command), load the JSON data as HTML tags and draw a data table using the data objects.
💡 Learn the basics of CAML query in SharePoint by reading this article: CAML Query tutorial for SharePoint 🔗
Code for reference:

<!-- JS References -->
<script src="../../SiteAssets/Scripts/jquery-1.12.0.min.js"></script>
<script src="../../SiteAssets/Scripts/jquery.dataTables.min.js"></script>

<!--CSS References-->
<link href="../../SiteAssets/CSS/jquery.dataTables.min.css" rel="stylesheet">

<!--HTML TAGS-->
<div id="divFinanceReportTable">
    <h3>
        <div>
            <p id="FinanceReportTitle"><b>Finance Sample Data</b></p>
        </div>
    </h3>
    <table style="width: 100%;" id="idFinanceReportList" cellspacing="0">
    </table>
    <div></div>
</div>

<!--Document.ready()-->
<script>
    var txtHTMLFinanceReport = "";
    var tableFinanceReport;
    var allFinanceReportData;

    $(document).ready(function () {
        //Load Finance Report List on page load
        SP.SOD.executeFunc('sp.js', 'SP.ClientContext', loadFinanceReportList);
    });

    //Load Tools from Tools List
    function loadFinanceReportList() {
        var currentClientContext = new SP.ClientContext.get_current();
        var currentWeb = currentClientContext.get_web();
        var financeReportDataList = currentWeb.get_lists().getByTitle('Finance Sample Data');
        var camlQuery = new SP.CamlQuery();
        var loadFinanceReportQuery = new SP.CamlQuery.createAllItemsQuery();
        var query = '<View><Query><OrderBy><FieldRef Name="ID" Ascending="TRUE"/></OrderBy></Query></View>';
        loadFinanceReportQuery.set_viewXml(query);
        allFinanceReportData = financeReportDataList.getItems(loadFinanceReportQuery);
        currentClientContext.load(allFinanceReportData);
        currentClientContext.executeQueryAsync(Function.createDelegate(this, this.success), Function.createDelegate(this, this.failed));
    }
    function success() {
        var segment = null;
        var country = null;
        var product = null;
        var unitsSold = null;
        var manufacturingPrice = null;
        var salesPrice = null;
        var grossSale = null;
        var sales = null;
        var profit = null;

        var USCurrency = { style: "currency", currency: "USD" };

        txtHTMLFinanceReport = "";

        var Header = "<thead>" +
            "<tr>" +
            "<th>Segment</th>" +
            "<th>Country</th>" +
            "<th>Product</th>" +
            "<th>Units Sold</th>" +
            "<th>Manufacturing Price</th>" +
            "<th>Sales Price</th>" +
            "<th>Gross Sale</th>" +
            "<th>Sales</th>" +
            "<th>Profit</th>" +
            "</tr>" +
            "</thead>";

        txtHTMLFinanceReport += Header;
        txtHTMLFinanceReport += "<tbody class='row-border hover order-column dataTable' role='grid'>";

        var listEnumerator = allFinanceReportData.getEnumerator();

        while (listEnumerator.moveNext()) {
            var currentItem = listEnumerator.get_current();

            if (currentItem.get_item('Title') != null) {
                segment = currentItem.get_item('Title');
            } else {
                segment = "";
            }

            if (currentItem.get_item('Country') != null) {
                country = currentItem.get_item('Country');
            } else {
                country = "";
            }

            if (currentItem.get_item('Product') != null) {
                product = currentItem.get_item('Product');
            } else {
                product = "";
            }

            if (currentItem.get_item('Units_x0020_Sold') != null) {
                unitsSold = currentItem.get_item('Units_x0020_Sold');
            } else {
                unitsSold = "";
            }

            if (currentItem.get_item('Manufacturing_x0020_Price') != null) {
                manufacturingPrice = currentItem.get_item('Manufacturing_x0020_Price');
                manufacturingPrice = manufacturingPrice.toLocaleString('en-US', USCurrency);
            }
            else {
                manufacturingPrice = "";
            }

            if (currentItem.get_item('Sale_x0020_Price') != null) {
                salesPrice = currentItem.get_item('Sale_x0020_Price');
                salesPrice = salesPrice.toLocaleString('en-US', USCurrency);
            } else {
                salesPrice = "";
            }

            if (currentItem.get_item('Gross_x0020_Sales') != null) {
                grossSale = currentItem.get_item('Gross_x0020_Sales');
                grossSale = grossSale.toLocaleString('en-US', USCurrency);
            } else {
                grossSale = "";
            }

            if (currentItem.get_item('_x0020_Sales') != null) {
                sales = currentItem.get_item('_x0020_Sales');
                sales = sales.toLocaleString('en-US', USCurrency);
            } else {
                sales = "";
            }

            if (currentItem.get_item('Profit') != null) {
                profit = currentItem.get_item('Profit');
                profit = profit.toLocaleString('en-US', USCurrency);
            } else {
                profit = "";
            }

            txtHTMLFinanceReport += "<tr>" +
                "<TD>" + segment + "</TD>" +
                "<TD>" + country + "</TD>" +
                "<TD>" + product + "</TD>" +
                "<TD>" + unitsSold + "</TD>" +
                "<TD>" + manufacturingPrice + "</TD>" +
                "<TD>" + salesPrice.toLocaleString() + "</TD>" +
                "<TD>" + grossSale + "</TD>" +
                "<TD>" + sales + "</TD>" +
                "<TD>" + profit + "</TD>";
            txtHTMLFinanceReport += "</tr>";
        }
        txtHTMLFinanceReport += "</tbody>";

        //Bind the HTML data to the Table
        $("#idFinanceReportList").append(txtHTMLFinanceReport);

        tableFinanceReport = $('#idFinanceReportList').DataTable(
            {
                "columnDefs": [
                    { "targets": [0], "visible": true, "width": "15%" },
                    { "targets": [1], "visible": true, "width": "8%" },
                    { "targets": [2], "visible": true, "width": "8%" },
                    { "targets": [3], "visible": true, "width": "8%" },
                    { "targets": [4], "visible": true, "width": "8%" },
                    { "targets": [5], "visible": true, "width": "8%" },
                    { "targets": [6], "visible": true, "width": "8%" },
                    { "targets": [7], "visible": true, "width": "8%" },
                    { "targets": [8], "visible": true, "width": "8%" }
                ]
            }
        );

        tableFinanceReport.draw();
    }
    function failed(sender, args) {
        alert("Data Reterival Failed: " + args.get_message());
    }

</script>

4. Now upload the text file either in a Document Library or preferably in Site Assets. Create a SharePoint page and then add a Content Editor web part. Refer the text file in the Content Editor web part and save it. That's it, we will see a very nice Table rendering the data as shown below:

5. The DataTables.JS has inbuilt functionalities which are listed below:

• Free text search option (very fast)
• Abililty of change the number of records display (25, 50, 100..)
• Paging with Previous and Next options
• Displays the number of items displayed and also changes the number according to paging
• Ability to sort the columns both ascending & descending
• Ability to query a column but hide it from view - if that specific column is required for internal data rendering logic or comparison or etc.

Cool, isn't it? 

Download the Custom List Template with contents (.STP file), JS files, images, text file which has the entire code from this link.🔗

Please free to comment. Always. your comments help me to write more.😃 Share this post to others if this helps you!😇

Don't forget to subscribe to the posts to get notified about new tutorials by clicking the Subscribe button on the top right corner and follow us in social buttons as well.

Happy Shar(Point)ing!

Update 25/07/2017

After reading our blog post, one of our readers shared Angular JS code for implementing the same concept in our Facebook page. Here is the code for reference as a bonus to the readers 😊

////....ANGULAR JS CODE....../////
angular.module('SPapp',['datatables'])
.controller("Splist",function($scope,$http,DTOptionsBuilder){
$http(
{
method:"GET",
url: GetsiteUrl() +"/_api/web/lists/getbytitle('Test')/items?$Select=Title,Job_x0020_Number,Approvl,Username/FirstName&$expand=Username/Id",
headers: { 
"Accept": "application/json;odata=verbose"}
}
).success(function(data,status,headers,config){
$scope.listdata = data.d.results;
}).error(function(data,status,headers,config){
});
$scope.dtOptions = DTOptionsBuilder.newOptions()
.withDisplayLength(10)
.withOption('bLengthChange', false);
});
function GetsiteUrl(){
var urlparts = document.location.href.split("/");
return urlparts[1] + "//" + urlparts[2] + "/" + urlparts[3];
}
////....END of ANGULAR JS CODE - SAVED as spapp.js......///// 

////....SharePoint Code saved as Text File....../////
<!-- JS References -->
<script src="../../SiteAssets/js/jquery-1.12.0.js"></script>
<script src="../../SiteAssets/js/jquery.dataTables.min.js"></script>
<script src="../../SiteAssets/js/angular.min.js"></script>
<script src="../../SiteAssets/js/angular-datatables.min.js"></script>
<link href="../../SiteAssets/js/jquery.dataTables.min.css" rel="stylesheet">
<script src="../../SiteAssets/js/spapp.js"></script>
<!--HTML TAGS-->
<div ng-app="SPapp" ng-controller="Splist">
<table datatable="ng" dt-options="dtOptions" id="myTable" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>
<tr>
<th>Title</th>
<th>Username</th>
<th>Approvl</th>
<th>Job Number</th>

</tr>
</thead>
<tfoot>
<tr>
<th>Title</th>
<th>Username</th>
<th>Approvl</th>
<th>Job Number</th>
</tr>
</tfoot>
<tbody>
<tr ng-repeat="listdatas in listdata">
<td>{{listdatas.Title}}</td>
<td>{{listdatas.Username.FirstName}}</td> 
<td>{{listdatas.Approvl}}</td>
<td>{{listdatas.Job_x0020_Number}}</td>
</tr>
</tbody>
</table> 

</div> 
////....SharePoint Code....../////

Read More

Multi value Lookup Columns in SharePoint

I couldn't find anything obvious for accessing SharePoint Multi-value lookup columns and filtering the values so chucked this together, hopefully useful for someone😃

In continuation to my previous article on Lookup Columns, we are going to learn about Multi-value lookup columns in SharePoint.

At the end of this article, I am going to give a bonus to the readers of this article 😊

Previous articles references
CAML Query tutorial for SharePoint
Cascading dropdown (or) Filtered Values in Lookup Columns in SharePoint
Cascading drop down in SharePoint using REST API

Let's go ahead and create two source lists for this demo:

1. States 

2. Cities (State is a lookup column from States list)

3. The third list will be used to implement the Multi-value lookup column and also we are going to implement filtering multi-value lookup columns in this article. I have created a list named "Multi value lookup demo" which has State and City as lookup value from the first two lists created. Make sure, you select Allow multiple values options as shown below:

The new item form without filtering looks as shown below:

Following are the functionalities to be implemented & issues/challenges needs to be considered while implementing filtering multi-value lookup columns in SharePoint:

1. An option can be double clicked to select or clicked Add button to select a value
2. An option can be double clicked to deselect or clicked Remove button to deselect a value
3. More than one option can be selected and can be bulk added to the selected values
4. The JQuery implemented should satisfy the above conditions while filtering
5. When State is selected, the values of Cities should get filtered. In the same way, when a value from the deselected then the values from Cities should be removed

I have added a Script Editor web part in New item form and added JQuery as reference. Also, added the JS code which will implement the filtering functionality.
If you want to implement the same functionality in Edit item form, following the same steps just mentioned above.
I have used CAML query to do the filtering functionality which is shown below:

$(document).ready(function () {
    //Call multi-value lookup function on selecting State
    $("select[title='State possible values']").dblclick(function () {
        multiValueLookup();
    });
    //Call multi-value lookup function on removing State
    $("select[title='State selected values']").dblclick(function () {
        multiValueLookup();
    });
    //Call multi-value lookup function on clicking Add - State
    $("input[value='Add >'][id^='State_']").click(function (){
        multiValueLookup();
    });
    //Call multi-value lookup function on clicking Remove - State
    $("input[value='< Remove'][id^='State_']").click(function () {
        multiValueLookup();
    });
});
function multiValueLookup() {
    var items = "";
    var citiesListName = "Cities";
    $("select[title='City possible values'] option").remove();
    $("select[title='State selected values'] option").each(function (i) {
        var clientContext = new SP.ClientContext.get_current();
        var oList = clientContext.get_web().get_lists().getByTitle(citiesListName);
        var camlQuery = new SP.CamlQuery();
        camlQuery.set_viewXml("<View><Query><OrderBy><FieldRef Name='Title' /></OrderBy><Where><Eq><FieldRef Name='State' LookupId='TRUE'/><Value Type='Lookup'>" + $(this).val() + "</Value></Eq></Where></Query></View>");
        var items = oList.getItems(camlQuery);
        clientContext.load(items);
        clientContext.executeQueryAsync(success, failure);
        function success() {
            var pn2 = "";
            var pn1 = "";
            var ListEnumerator = items.getEnumerator();
            while (ListEnumerator.moveNext()) {
                var currentItem = ListEnumerator.get_current();
                if (currentItem.get_item('Title') != null) {
                    var pn1 = currentItem.get_item('Title');
                    if (pn2 != pn1) {
                        items = "<option value='" + currentItem.get_item('ID') + "' title='" + pn1 + "'>" + pn1 + "</option>";
                        $("select[title='City possible values']").append(items);
                        pn2 = pn1;
                    }
                }
            }
        }
        function failure(sender, args) {
            // alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
        }
    });
}

The above code is self explanatory and I have added few screenshots below for reference:
When an value is selected

When an value is deselected

The added value in the multi-value lookup demo list

Cool isn't it? 😊 Very simple as well.
If you have read my previous article on Cascading Lookup Columns in SharePoint, you would have noticed that I have implemented the functionality using REST API.
You may think why I didn't implement multi-value lookup column using REST API? so the bonus is here. I have implemented the above functionality using REST API as well. I will add the code in download link and you can follow either of the way to implement this functionality 😎
This is tested in SharePoint Online and I am sure it should work for SharePoint 2013, 2016 & 2010 as well.
To download, lists templates, JS code using CAML, JS code using REST API, click this link. 🔗
Please free to comment. Always, your comments help me to write more.😃 Share this post to others if this helps you!😇

Read More

CAML Query to Get Data from SharePoint List

To know about the Basics of the CAML query read this article.

To know about using CAML query builder to create CAML queries, read this article.

In this article, we will learn how to use CAML query in Microsoft Visual Studio

Scenario:

I have a custom list named “Product List” with the columns named Title, Price, Quantity and Is Available.

I want to retrieve the products with price greater than 1000 and lesser than 10000. Then I have to order the results depending upon the quantity in descending order.

Using the CAML query builder, I have created a CAML query for the above scenario and the query is as follows:

<Query><Where><And><Gt><FieldRef Name="Price" /><Value Type="Number">1000</Value></Gt><Leq><FieldRef Name="Price" /><Value Type="Number">10000</Value></Leq></And></Where><OrderBy><FieldRef Name="Price" Ascending="False" /></OrderBy></Query>

Implementing CAML Query in Microsoft Visual Studio

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace UsingCAMLQuery
{
    class Program
    {
        static void Main(string[] args)
        {
            const string camlQuery = @"100010000";
            try
            {
                using (SPSite objSite = new SPSite("sitename"))
                {
                    using (SPWeb objWeb = objSite.OpenWeb())
                    {
                        // Building a query
                        SPQuery query=new SPQuery();
                        query.Query=camlQuery;
                     
                        // Retrieving the data from the List
                        SPList objList=objWeb.Lists["Product List"];
                        SPListItemCollection items= objList.GetItems(query);

                        // Prinitng the header
                        Console.WriteLine("{0,-25} {1,-20} {2,-15} {3}", "Title", "Price", "Qunatity", "Is Available");

                        foreach (SPListItem objListItem in items)
                        {
                            Console.WriteLine("{0,-25} {1,-20} {2,-25} {3}", objListItem["Title"], objListItem["Price"], 
                                                                             objListItem["Quantity"], objListItem["Is Available"]);
                        }

                    }
                    Console.ReadLine(); 
                }
 
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error Occured:", ex.Message);
            }
            Console.ReadLine();
        }
    }
}

The full solution can be downloaded from the following link. 
Please free to comment and share this post if it helps you!

Read More

Using CAML Query Builder for SharePoint

To know about the Basics of the CAML query read this article.

Download Link

Use the following link to download the CAML Query builder.

http://www.u2u.be/res/Tools/files/CamlBuilderSetupv4.0.0.0.zip
Update: CAML query builder for 2007, 2010, 2013 and SP Online link

How to use CAML Query Builder to create a query

Following are the steps for using the CAML query builder to create a CAML query

1. Open the CAML query builder. Type the URL to connect to the SharePoint site and check Credentials of the current user to login

2. Once clicked connect, you will see the following window SharePoint site with all the lists and libraries in the site in a tree view structure which is shown below: 

3. Now we will create a new query on a list named “Product List” as shown below:

4. We will start creating a new query which is as follows:

5. You will find the query being created depending upon your selection from the list

6. Clicking on Execute, you will see the query being executed and the result is being displayed in the result tab as shown below:

The Query created is:

<Query>

           <Where>

                       <Gt>

                              <FieldRef Name="Quantity" />

                              <Value Type="Number">0</Value>

                      </Gt>

          </Where>

          <OrderBy>

                       <FieldRef Name="Price" Ascending="True" />

          </OrderBy>

</Query>

Hence, CAML Query Builder allows us to create the queries very easily so that it can be used in the SharePoint programming.

Now, we will have a question, how to use this query in the Visual Studio to retrieve the data from a list.

To know how to use CAML query in Visual Studio programming, read this article.

Read More

CAML Query tutorial for SharePoint

In this article, we will understand the basics of CAML query in SharePoint.

What is CAML?

  Ø  CAML - Collaborative Application Markup Language

  Ø  XML- Extensible Markup Language based query language

  Ø  Used to perform a query operation against SharePoint Lists

How SharePoint List Items are retrieved?

SharePoint List data can be retrieved in any one of the following ways:

1. Using the SharePoint object model – used when code runs on the server (Example: Developing a web part or an application page)

2. Using the SharePoint Lists web service – used when your code doesn’t run on the server where the SharePoint is installed (Example: Developing a windows application)

3. Using Power shell –used mostly by the ADMIN of the SharePoint when they quickly want to retrieve some information from the SharePoint site

How does CAML query looks like?

As I already mentioned, it is XML based query language and it contains tags in it. The root element of the CAML query root element is Query. But it is not necessary to use Query element in the query you form.

Within the Query element you have two elements possible:

1. Where   – to filter the data

2. OrderBy – to categorize the data

A simple structure of the CAML query is as follows:
<Query>

          <Where>

                   <Eq>

                             <FieldRef Name=”FieldName” />

                             <Value Type=”DataType”>Value</Value>

                   </Eq>

          </Where>

          <OrderBy>

                             <FieldRef Name=”FieldName” />

                             <FieldRef Name=”FieldName” />

          </OrderBy>

</Query>

Operators in CAML Query

From the above structure, we came to know that it uses Where and OrderBy elements to retrieve the data from the list.

Let us know about the operators present in the CAML query and its usage:

Inside the Where element

1. Logical Operators - AND, OR

2. Comparison Operators - Listed Below in the table

AND – Which takes two conditions are satisfied

OR – Which takes when either of the conditions is satisfied

Comparison Operators

Inside the OrderBy/GroupBy element

OrderBy – Which orders or sort the data depends upon the field (FieldRef element) given.

GroupBy – Which groups the data depends upon the group by field (FieldRef element) given.

Examples

Logical & Comparison Operators

Use of AND, Gt, Leq

<Query>

<Where>

<And>

<Gt>

<FieldRef Name="Quantity" />

<Value Type="Number">0</Value>

</Gt>

<Leq>

<FieldRef Name="Price" />

<Value Type="Number">2000</Value>

</Leq>

</And>

</Where>

</Query>

Use of OR, Gt, Leq

<Query>

<Where>

<Or>

    <Gt>

<FieldRef Name="Quantity" />

<Value Type="Number">0</Value>

    </Gt>

                     <Leq>

  <FieldRef Name="Price" />

<Value Type="Number">2000</Value>

    </Leq>

               </Or>

       </Where>

</Query>

Use of BeginsWith, Leq

<Query>

<Where>

<And>

     <BeginsWith>

 <FieldRef Name="Title" />

 <Value Type="Text">M</Value>

     </BeginsWith>

     <Leq>

    <FieldRef Name="Quantity" />

<Value Type="Number">1000</Value>

     </Leq>

</And>

</Where>

<OrderBy>

<FieldRef Name="Price" Ascending="False" />

</OrderBy>

</Query>

OrderBy Operator
<Query>

<Where>

<Or>

   <Gt>

<FieldRef Name="Quantity" />

<Value Type="Number">0</Value>

  </Gt>

    <Leq>

<FieldRef Name="Price" />

<Value Type="Number">2000</Value>

  </Leq>

</Or>

</Where>

<OrderBy>

<FieldRef Name="Price" Ascending="True" />

</OrderBy>

</Query>

Is it possible to write this queries without any errors manually?

Yes. But we will know the errors only after executing the program. Hence there is a free CAML query builder which will generate the query easily.

To know about where to download and how to use the CAML Query builder read this article.

To know how to use the CAML query in the Microsoft Visual Studio read this article.

Read More