Tuesday, February 18, 2014

Filter List items by Data Table - JQuery, REST API, AJAX and Datatables

Make a REST API call to retrieve SharePoint list items.
Returns JSON Objects array
Call DataTable to display in Tabular Format.
Add additional logic to show/hide additional details for a particular row.


(1) Create a list with Four columns and some test data Co11, Col2, Col3, Col4.
(2) Create a new page and add Script Editor WebPart.

<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>
<link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>

//Create User Interface to filter/search through results

Search for List of Technologies here:
    <input type="text" id="techsearch" >
    <input type="button" value="Search" onclick="LoadTechnologies($('#techsearch').val());" >      

<div id="dynamictable"></div>


tr span.expand {
  width: 20px;
  height: 20px;
  background-image: url('http://www.datatables.net/release-datatables/examples/examples_support/details_open.png');
  vertical-align: middle;
  margin-right: 5px; 

tr span.open {
  background-image: url('http://www.datatables.net/release-datatables/examples/examples_support/details_close.png'); 



<script type="text/javascript">



            var techsearch = null;  
            var data = null;
            var TechTable = null;

    });    //jquery function

    function LoadTechnologies(techsearch) {
      var data2 =  $.ajax(
 //Build URL with select columns to display and can add additional filter by conditions
         url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('ListName')/items?$select=Title,Vendor,Domain1,SubDomain1,ID,Description", 
         type: "GET",
        dataType: "json",
        headers: { Accept: "application/json;odata=verbose" }
);// end ajax

     data2.done(function (data,textStatus, jqXHR)
    var test2 = jQuery.parseJSON(  jqXHR.responseText.replace("]}}","]").replace("{\"d\":{\"results\":","")  );
                       // alert("First row title is: " + test2[1].Title);

// Cleanup - Remove any children under Dynamic Table (To remove results after first load)
//Create a Table interface depending on how you want to display returned list results            
                       $('#dynamictable').append('<table id="table1" cellspacing="0" cellpadding="4" width="100%"></table>');
var table = $('#dynamictable').children();   
table.append("<thead><tr><td><b>Name of Technology </b></td> <td><b>Vendor</b></td>   <td><b>Domain</b></td>  <td><b>SubDomain</b></td>  <td></td>  </th></tr></thead><tbody></tbody>");

                        $.fn.dataTableExt.sErrMode = 'throw' ;

                        //Function formatting to show what additional details to show when you click to Expand
                        function fnFormatDetails(oTable, nTr) {
                              var aData = oTable.fnGetData(nTr);
                              var sOut = '<table cellpadding="8" bgcolor="rgb(255,160,0)" border="0" style="padding-left:50px;">';
                                  sOut += '<tr><td>Description: </td><td>' + aData.Description + '</td></tr>';
                 return sOut;

                     //Binding to DataTable to show results in a tabular format
                        var TechTable = $('#table1').dataTable({
                                   sDom: '<"top"if>rt<"bottom"lp><"clear">',
                                  oLanguage:      {   sInfoEmpty: " ",
                                                       sZeroRecords: " No Technologies registered with the keyword you searched for",             
                                                       sSearch: "Filter Results:"

                                   bJQueryUI: true,
                                   bProcessing: true,
                                   bFilter: true,
                                   bPaginate: true,   //pagination
                                   aaData: test2,      //test2 is parsed JSON data                                                      
                                   //aaSorting: [[0, 'desc']],
                                   aoColumns: [
                                                { mData: 'Title', bSearchable: true,  bSortable: true,
                                                   sContentPadding: "aaaaaaaaaaaaaa",
                                                 { mData: 'Vendor'},
                                                 { mData: 'Domain1'},
                                                 { mData: 'SubDomain1'},
                                                 { mData: function (source, type, val)
                                                                {//Create Hyperlinks on returned data
                           return "<a href='Site/Subsite/TRTesting.aspx?BusinessUnit=" + source.ID + "' target='_blank'>Register</a>";
                                               ] //end aoColumns
                                   });   // Techtable

                function expandRenderer(data, type, full)
                             // console.log(arguments);
                            switch(type) {
                   case 'display':
      return '<div class="expand-wrapper"><span class="expand"></span><span class="data">'+data+'</span></div>';
                 case 'type':
                 case 'filter':
                 case 'sort':
                               return data;
                       }  // end switch
                          }  // end expandRenderer

//For Show/Hide additional row details       
                 $('#table1 tbody').on('click', 'td span.expand', function() {
                                 var nTr = $(this).parents('tr')[0];
                                  if (TechTable.fnIsOpen(nTr))
                                        TechTable.fnOpen(nTr, fnFormatDetails(TechTable, nTr), 'details');


});  // end function
                   }); //end data2 done function



Additional Reference:


Madhu Sudhan said...

Can you describe it Ramesh?

Tyler said...

That would be helpful yes. I did find this which might help(?) http://datatables.net/blog/Drill-down_rows