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.



Implementation:

(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:
<br></br> 
    <input type="text" id="techsearch" >
    <input type="button" value="Search" onclick="LoadTechnologies($('#techsearch').val());" >      
<br></br><br></br>


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



<br></br><br></br>


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

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

div.expand-wrapper{
  white-space:nowrap; 
}

</style>



<script type="text/javascript">


 


$(document).ready(function(){

            var techsearch = null;  
            var data = null;
            var TechTable = null;
            LoadTechnologies(techsearch);
           
    

    });    //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)
 $("#dynamictable").children().remove();
//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>");





                       
               
$(function()
                  {
     
                    
                        $.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:"
                                                }, 

                                   bDestroy:true,
                                   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",
                                                   mRender:expandRenderer
                                                 },
                                                 { 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))
                                   {
                                          $(this).removeClass('open');
                                         TechTable.fnClose(nTr);
                                   }
                                   else
                                   {
                                         $(this).addClass('open');
                                        TechTable.fnOpen(nTr, fnFormatDetails(TechTable, nTr), 'details');
                                      }
                               });


                          
 

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



}

 
</script>


Additional Reference:
http://kalashnikovtechnoblogs.blogspot.in/2015/09/get-data-in-jquery-datatable-from.html

Friday, February 14, 2014

Hiding a Section based on Multi Select List Box selected value and Promoting it to a List



(1) Promoting
Create a Hidden field and calculate its value using the formula below and promote
eval(eval(MultiSelectField[. != ""], 'concat(., ", ")'), "..")













(2) Hiding section based on MSLB value
 when you set up the condition, select "Select a field or group..." from the left dropdown, select the MSLB's field, and before confirming the Select a Field or Group dialog, pick All occurrences of ___ from the dropdown(one more below).