How load list item with individual column filtration in office 365/ SharePoint 2013 page?

This post was most recently updated on August 2nd, 2024

Below I share code to apply filter option to SharePoint list using the jquery data table. 

Steps: Page level

• Create page and add following datatable css and js.<link rel=”stylesheet” type=”text/css” href=”https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css” />
  <script src=”https://code.jquery.com/jquery-1.12.4.js” type=”text/javascript”></script>
  <script type=”text/javascript” src=”https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js”></script>
• Create table with header according to SharePoint list. <div class=”Filters”><div><span>ID FIlter</span><span class=”idFilterOption”></span> <span>Adjustment Type FIlter</span><span class=”requestFilterOption”></span></div></div>

  <table id=”table_id” class=”display” role=”grid” width=”100%” cellspacing=”0″>
  <thead>
  <tr>
  <th>ID</th>
  <th>Title</th>
  <th>Adjustment Type</th>
  <th>Adjustment Requested</th>
  <th>Unique No</th>
  </tr>
  </thead>
  </table>

Steps: JS Level

• 
  Step 1: We need to get item count of threshold exceed list/normal SharePoint list by GetItemCount () function.
  
  //Step 1. get total item count of threshold exceed list. function GetItemCount(siteurl, ListName){ var ItemCount=''; $.ajax({ url: siteurl+"/_api/web/lists/GetByTitle('"+ListName+"')/ItemCount", method: "GET", async: false, headers: { "Accept": "application/json; odata=verbose" }, success: function (data) { ItemCount = data.d.ItemCount; }, error: function (data) { console.log(data); } }); return ItemCount; }

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  //Step 1. get total item count of threshold exceed list. function GetItemCount(siteurl, ListName){      var ItemCount='';      $.ajax({            url: siteurl+"/_api/web/lists/GetByTitle('"+ListName+"')/ItemCount",            method: "GET",            async: false,            headers: { "Accept": "application/json; odata=verbose" },            success: function (data) {                ItemCount = data.d.ItemCount;            },             error: function (data) {                console.log(data);             }      });      return ItemCount;   }

• Step 2: Next step create rest service url by splitting rest call according to item count of GetItemCount () function. Check item count if item count is above 5000 then we split calling with 1000 item per call top=1000 for better performance. Otherwise we call as usual i.e. top=5000.//Step 2. create rest service url if item count is above 5000 then we split calling with 1000 item per call top=1000 for better performance. Otherwise we call as usual 5000 items top=5000
  
  function createRestUrl(siteurl, ItemCount, ListName) { if(ItemCount<=5000) { //Item count less than 5000 so we limit it as usual 5000 var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Title,ID&$top=5000"; } else { //Item count more than 5000 so we split it in 1000 item per call var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Title,ID&$top=1000"; } //Step 3: Rest call to procerss each items of list $.when(processList(listServiceUrl,ItemCount)).done(function () { }); }

  1 2 3 4 5 6 7 8 9 10 11 12 13

  function createRestUrl(siteurl, ItemCount, ListName) {           if(ItemCount<=5000) { //Item count less than 5000 so we limit it as usual 5000          var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Title,ID&$top=5000"; } else { //Item count more than 5000 so we split it in 1000 item per call var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Title,ID&$top=1000";         }          //Step 3: Rest call to procerss each items of list        $.when(processList(listServiceUrl,ItemCount)).done(function () { }); }

• Step 3: Process each item of threshold list by repeatedly calling getJSONDataFromUrl () function. GetJSONDataFromUrl () function gives Ajax json object. //Step 3: Rest call to procerss each items of list
  
  function processList(nextUrl,ItemCount) { var dfd = new $.Deferred(); if (nextUrl == undefined) { dfd.resolve(); return; } //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object. getJSONDataFromUrl(nextUrl).done(function (listItems) { TotalItemCount = TotalItemCount+listItems.d.results.length; var items = listItems.d.results; var next = listItems.d.__next; $.when(getLargeList(next,ItemCount)).done(function (){ dfd.resolve(); }); var tableTdHTML=''; //Process each column data into tableTDHTML $.each(items, function(index, obj){ if ($.inArray(obj.ID, idArray)== -1) //push unique id in idArray idArray.push(obj.ID); if ($.inArray(obj.AdjustmentType, AdjustmentTypeArray)== -1) // push unique Adjustment Type into AdjustmentTypeArray AdjustmentTypeArray.push(obj.AdjustmentType); tableTdHTML +='<tr><td>'+obj.ID+'</td><td>'+obj.Title+'</td><td>'+obj.AdjustmentType+'</td><td>'+obj.AdjustmentRequested+'</td><td>'+obj.Age+'</td></tr>'; }); //Create datatable object var table = $('#table_id').DataTable({ //Process each filter array data and append to respective filter html initComplete: function () { this.api().columns([0,2]).every( function (index) { //[0,2] indicate column header position var column = this; if(index === 0){ var column = this; var select = $('<select><option value="">Show all</option></select>') .appendTo( $('.idFilterOption').empty() ) .on( 'change', function () { var val = $.fn.dataTable.util.escapeRegex( $(this).val() ); column.search( val ? '^'+val+'$' : '', true, false ).draw(); }); $.each(idArray, function (index, obj) { select.append( '<option value="'+obj+'">'+obj+'</option>' ); }); } if(index === 2){ var select = $('<select><option value="">Show all</option></select>') .appendTo( $('.requestFilterOption').empty() ) .on( 'change', function () { var val = $.fn.dataTable.util.escapeRegex( $(this).val() ); column.search( val ? '^'+val+'$' : '', true, false ).draw(); }); $.each(AdjustmentTypeArray, function ( d, j ) { select.append( '<option value="'+j+'">'+j+'</option>' ); }); } }); } }); table.rows.add($(tableTdHTML)).draw(); //add row dynamically into datatables object }); return dfd.promise(); } //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object. function getJSONDataFromUrl(endpoint) { return jQuery.ajax({ url: endpoint, method: "GET", headers: { "Accept": "application/json; odata=verbose", "Content-Type": "application/json; odata=verbose" } }); }

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

  function processList(nextUrl,ItemCount) {            var dfd = new $.Deferred();                  if (nextUrl == undefined) {             dfd.resolve();             return;         }         //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.         getJSONDataFromUrl(nextUrl).done(function (listItems) {                   TotalItemCount = TotalItemCount+listItems.d.results.length;                     var items = listItems.d.results;                 var next = listItems.d.__next;                 $.when(getLargeList(next,ItemCount)).done(function (){                                 dfd.resolve();                                });                              var tableTdHTML='';                   //Process each column data into tableTDHTML                 $.each(items, function(index, obj){                       if ($.inArray(obj.ID, idArray)== -1)  //push unique id in idArray                         idArray.push(obj.ID);                     if ($.inArray(obj.AdjustmentType, AdjustmentTypeArray)== -1) // push unique Adjustment Type into AdjustmentTypeArray                           AdjustmentTypeArray.push(obj.AdjustmentType);                       tableTdHTML +='<tr><td>'+obj.ID+'</td><td>'+obj.Title+'</td><td>'+obj.AdjustmentType+'</td><td>'+obj.AdjustmentRequested+'</td><td>'+obj.Age+'</td></tr>';                    });                 //Create datatable object         var table = $('#table_id').DataTable({              //Process each filter array data and append to respective filter html    initComplete: function () {                          this.api().columns([0,2]).every( function (index) { //[0,2] indicate column header position                            var column = this;                            if(index === 0){                              var column = this;                              var select = $('<select><option value="">Show all</option></select>')                              .appendTo( $('.idFilterOption').empty() )                              .on( 'change', function () {                                var val = $.fn.dataTable.util.escapeRegex(                                   $(this).val()                              );                               column.search( val ? '^'+val+'$' : '', true, false ).draw();                             });                             $.each(idArray, function (index, obj) {                                 select.append( '<option value="'+obj+'">'+obj+'</option>' );                             });                           }                           if(index === 2){                              var select = $('<select><option value="">Show all</option></select>')                              .appendTo( $('.requestFilterOption').empty() )                              .on( 'change', function () {                                 var val = $.fn.dataTable.util.escapeRegex(                                 $(this).val()                              );                             column.search( val ? '^'+val+'$' : '', true, false ).draw();                             });                             $.each(AdjustmentTypeArray, function ( d, j ) {                               select.append( '<option value="'+j+'">'+j+'</option>' );                             });                           }                     });                   }      });                table.rows.add($(tableTdHTML)).draw(); //add row dynamically into datatables object       });       return dfd.promise(); }   //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object. function getJSONDataFromUrl(endpoint) {         return jQuery.ajax({             url: endpoint,             method: "GET",             headers: {                 "Accept": "application/json; odata=verbose",                 "Content-Type": "application/json; odata=verbose"             }         }); }

Final Screen shot without filtration

Screen Shot with filter

Reference:

https://sharepoint.stackexchange.com/questions/217254/rest-to-read-more-than-5000-items-from-document-library/217451#217451