Remove From My Forums

Have Jquery Data table Sort and Filter above table

Question

Dear All,

I had written the below code where I am able to get the list items and filter, sort the SharePoint list items in Jquery data table.
And I followed the link which is exactly matching my requirement , But in my case I am using select input (dropdown) .

I tried below following code .But I find no luck. Can any one please help me what's wrong in below code.

<body>
       <div class="display" id="tableheader">
           <table id="tblsuperstore" class="display" style="width:100%">
               <thead>
                   <tr class="bgcolorgray">
                       <th>ID</th>
                       <th>Country</th>
                       <th>City</th>
                       <th>State</th>
                       <th>Order Date</th>
                   </tr>
               </thead>
               <tfoot>
                   <tr class="bgcolorgray">
                       <th>ID</th>
                       <th>Country</th>
                       <th>City</th>
                       <th>State</th>
                       <th>Order Date</th>
                   </tr>
               </tfoot>
           </table>
       </div>
   </body>
    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
    <script language="javascript" src="https://cdn.datatables.net/fixedheader/3.1.3/js/dataTables.fixedHeader.min.js"></script>
  <script language="javascript" src="    https://cdn.datatables.net/responsive/2.2.1/js/dataTables.responsive.min.js"></script>

    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/fixedheader/3.1.3/css/fixedHeader.dataTables.min.css">
  <link rel="stylesheet" href="    https://cdn.datatables.net/responsive/2.2.1/css/responsive.dataTables.min.css">

    <script language="javascript" src="https://momentjs.com/downloads/moment.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.21.0/moment.min.js"></script>

    <script>
       //alert('start script');
       $(document).ready(function () {
           ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js");
            sortDatePlugin();
       });
  
       function retrieveListItems() {
             var clientContext = new SP.ClientContext.get_current();
           var oList = clientContext.get_web().get_lists().getByTitle('Supersotres');
            var camlQuery = new SP.CamlQuery();
           camlQuery.set_viewXml("<View><Query><OrderBy><FieldRef Name='Country'/></OrderBy></Query> </View>");
           camlQuery.RowLimit = 101;
            this.collListItem = oList.getItems(camlQuery);
           clientContext.load(collListItem);
           clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
        }

       function onQuerySucceeded(sender, args) {
           var Country = null;
           var City = null;
           var State = null;
           var count = 1;
           var orderdate=null;

           var listItemInfo = '';
           var listItemEnumerator = collListItem.getEnumerator();
           while (listItemEnumerator.moveNext()) {
               count++;
               var oListItem = listItemEnumerator.get_current();
               //alert('title ' + oListItem.get_item('Title'));
               listItemInfo += ' <strong>Title:</strong> ' + oListItem.get_item('Title') + '<br />';

               Country = oListItem.get_item('Country');
               City = oListItem.get_item('City');
               State = oListItem.get_item('State');
               orderdate=moment.utc(oListItem.get_item('OrderDate')).format('DD/MM/YYYY');

               var tr = $('<tr>');

               var td = $('<td>');
               td.append(oListItem.get_item('ID'));
               tr.append(td);

               var td = $('<td>');
               td.append(Country);
               tr.append(td);

               var td = $('<td>');
               td.append(City);
               tr.append(td);

               var td = $('<td>');
               td.append(State);
               tr.append(td);

               var td = $('<td>');
               td.append(orderdate);
               tr.append(td);

               $("#tblsuperstore").append(tr);
             }
             $('#tblsuperstore').DataTable( {
               "paging":false,fixedHeader:{header: true,footer: true},responsive: false,autoWidth: false,  aaSorting: [[3, 'asc']],
               "columnDefs":[{ "width": "3%", "targets":0 },{ "width": "9%", "targets": 1},{ "width": "8%", "targets": 2},{ "width": "6%", "targets": 3}],
                      initComplete: function () {
                          this.api().columns().every( function () {
                              var column = this;
                              if ( column.index() !== 0 ) {
                               var select = $('<select><option value=""></option></select>')
                                  .appendTo( $(column.footer()).empty() )
                                  .on( 'change', function () {
                                      var val = $.fn.dataTable.util.escapeRegex(
                                          $(this).val()
                                      );

                                      column
                                          .search( val ? '^'+val+'$' : '', true, false )
                                          .draw();
                                  } );
                               column.data().unique().sort().each( function ( d, j ) {
                                  select.append( '<option value="'+d+'">'+d+'</option>' )
                              } );
                            }
                        } );
                      }
             } );

             $('#s4-workspace').scroll(function() {
             $('#tblsuperstore').DataTable().fixedHeader.adjust();
             });

              var position = collListItem.get_listItemCollectionPosition();
              if (position != null)
              {
                query.set_listItemCollectionPosition(position);
                collListItem = targetList.getItems(query);
                clientContext.load(collListItem);
                clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
              }
       }
         function onQueryFailed(sender, args) {
           alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
       }
   </script>
 <p id="divHelloWorld"> </p>

SP Assest

Edited by SP Assest Sunday, April 29, 2018 6:46 PM

Sunday, April 29, 2018 6:46 PM

Answers

Hi,

Here is the solution to fix the case.

Download fixedHeader 3.1.1 from //cdn.datatables.net/fixedheader/3.1.1/js/dataTables.fixedHeader.js

Add below red part code to the js library( you could check the link below).

https://datatables.net/forums/discussion/32623/fixedheader-with-sharepoint-2013-online

Upload the updated js library to SharePoint or layouts folder.

Add another tr in thead as filter row(dropdown filter header and header order will conflict).

Here is my tested code for your reference(a web part page with script editor web part).

<script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
    <script src="https://cdn.datatables.net/responsive/2.2.1/js/dataTables.responsive.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.20.1/moment.min.js"></script>
    <script src="/_layouts/15/js/dataTables.fixedHeader3.11.js"></script>   
    <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />     
    <link href="https://cdn.datatables.net/responsive/2.2.1/css/responsive.dataTables.min.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/fixedheader/3.1.3/css/fixedHeader.dataTables.min.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css" />
    
    <script>
        //alert('start script');
        $(document).ready(function () {
            ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js");
            sortDatePlugin();           
        });
        // Function is to Sort the Date Column based on DD/MM/YYYY
        function sortDatePlugin() {
            $.fn.dataTable.moment = function (format, locale) {
                var types = $.fn.dataTable.ext.type;
                // Add type detection
                types.detect.unshift(function (d) {
                    return moment(d, format, locale, true).isValid() ? 'moment-' + format : null;
                });
                // Add sorting method - use an integer for the sorting
                types.order['moment-' + format + '-pre'] = function (d) {
                    return moment(d, format, locale, true).unix();
                };
            };
            $.fn.dataTable.moment('DD/MM/YYYY');
        }

        function retrieveListItems() {
            var clientContext = new SP.ClientContext.get_current();
            var oList = clientContext.get_web().get_lists().getByTitle('Supersotres');
            var camlQuery = new SP.CamlQuery();
            camlQuery.set_viewXml("<View><Query><OrderBy><FieldRef Name='Country'/></OrderBy></Query></View>");
            camlQuery.RowLimit = 101;
            this.collListItem = oList.getItems(camlQuery);
            clientContext.load(collListItem);
            clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
        }

        function onQuerySucceeded(sender, args) {
            var Country = null;
            var City = null;
            var State = null;
            var count = 1;
            var orderdate = null;

            var listItemInfo = '';
            var listItemEnumerator = collListItem.getEnumerator();
            while (listItemEnumerator.moveNext()) {
                count++;
                var oListItem = listItemEnumerator.get_current();
                //alert('title ' + oListItem.get_item('Title'));
                listItemInfo += ' <strong>Title:</strong> ' + oListItem.get_item('Title') + '<br />';

                Country = oListItem.get_item('Country');
                City = oListItem.get_item('City');
                State = oListItem.get_item('State');
                orderdate = moment.utc(oListItem.get_item('OrderDate')).format('DD/MM/YYYY');

                var tr = $('<tr>');

                var td = $('<td>');
                td.append(Country);
                tr.append(td);

                var td = $('<td>');
                td.append(City);
                tr.append(td);

                var td = $('<td>');
                td.append(State);
                tr.append(td);

                var td = $('<td>');
                td.append(orderdate);
                tr.append(td);

                $("#tblsuperstore tbody").append(tr);
            }
            var table=$('#tblsuperstore').DataTable({
                stateSave: true,
                cache: true,
                responsive: true,
                fixedHeader: {
                    header: true,
                    footer: true
                }
                ,
                initComplete: function () {                  
                    //this.api().columns().every(function () {                    
                    //    var column = this;
                    //    if (column.index() !== 4 && column.index() !== 5 && column.index() !== 6 && column.index() !== 7 && column.index() !== 10) {
                    //        $(column.header()).append("<br>");
                    //        var select = $('<select><option value=""></option></select>')
                    //          .appendTo($(column.header()))
                    //          .on('change', function () {
                    //              var val = $.fn.dataTable.util.escapeRegex(
                    //                $(this).val()
                    //              );
                    //              column
                    //                .search(val ? '^' + val + '$' : '', true, false)
                    //                .draw();
                    //          });
                    //        column.data().unique().sort().each(function (d, j) {
                    //            select.append('<option value="' + d + '">' + d + '</option>');
                    //        });
                    //    }
                    //});
                    //responsive: {
                    //        details: {
                    //            type: 'column'
                    //        }
                    //}
                }

            });
            $(".filterhead").each(function (i) {
                var select = $('<select><option value=""></option></select>')
                    .appendTo($(this).empty())
                    .on('change', function () {
                        var term = $(this).val();
                        table.column(i).search(term, false, false).draw();
                    });
                table.column(i).data().unique().sort().each(function (d, j) {
                    select.append('<option value="' + d + '">' + d + '</option>')
                });
            });
            var position = collListItem.get_listItemCollectionPosition();
            if (position != null) {
                query.set_listItemCollectionPosition(position);
                collListItem = targetList.getItems(query);
                clientContext.load(collListItem);
                clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
            }
        }
        function onQueryFailed(sender, args) {
            alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
        }
    </script>

    <table id="tblsuperstore" class="display" border="1" width="100%" style="overflow-x:auto;">
        <thead>
            <tr>
                <th class="filterhead">Country</th>
                <th class="filterhead">City</th>
                <th class="filterhead">State</th>
                <th class="filterhead">Order Date</th>               
            </tr>
            <tr> <!--class="fh-fixedHeader"-->
                <th>Country</th>
                <th>City</th>
                <th>State</th>
                <th>Order Date</th>
            </tr>
        </thead>
        <tbody></tbody>
        <tfoot>
            <tr > <!--class="fh-fixedFooter"-->
                <th>Country</th>
                <th>City</th>
                <th>State</th>
                <th>Order Date</th>
            </tr>
        </tfoot>
    </table>

Best Regards,

Lee

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

Proposed as answer by Dennis Guo Tuesday, May 1, 2018 1:53 AM
Marked as answer by SP Assest Wednesday, May 2, 2018 4:46 PM

Monday, April 30, 2018 5:58 AM

All replies

Hi,

Here is the solution to fix the case.

Download fixedHeader 3.1.1 from //cdn.datatables.net/fixedheader/3.1.1/js/dataTables.fixedHeader.js

Add below red part code to the js library( you could check the link below).

https://datatables.net/forums/discussion/32623/fixedheader-with-sharepoint-2013-online

Upload the updated js library to SharePoint or layouts folder.

Add another tr in thead as filter row(dropdown filter header and header order will conflict).

Here is my tested code for your reference(a web part page with script editor web part).

<script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
    <script src="https://cdn.datatables.net/responsive/2.2.1/js/dataTables.responsive.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.20.1/moment.min.js"></script>
    <script src="/_layouts/15/js/dataTables.fixedHeader3.11.js"></script>   
    <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />     
    <link href="https://cdn.datatables.net/responsive/2.2.1/css/responsive.dataTables.min.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/fixedheader/3.1.3/css/fixedHeader.dataTables.min.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css" />
    
    <script>
        //alert('start script');
        $(document).ready(function () {
            ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js");
            sortDatePlugin();           
        });
        // Function is to Sort the Date Column based on DD/MM/YYYY
        function sortDatePlugin() {
            $.fn.dataTable.moment = function (format, locale) {
                var types = $.fn.dataTable.ext.type;
                // Add type detection
                types.detect.unshift(function (d) {
                    return moment(d, format, locale, true).isValid() ? 'moment-' + format : null;
                });
                // Add sorting method - use an integer for the sorting
                types.order['moment-' + format + '-pre'] = function (d) {
                    return moment(d, format, locale, true).unix();
                };
            };
            $.fn.dataTable.moment('DD/MM/YYYY');
        }

        function retrieveListItems() {
            var clientContext = new SP.ClientContext.get_current();
            var oList = clientContext.get_web().get_lists().getByTitle('Supersotres');
            var camlQuery = new SP.CamlQuery();
            camlQuery.set_viewXml("<View><Query><OrderBy><FieldRef Name='Country'/></OrderBy></Query></View>");
            camlQuery.RowLimit = 101;
            this.collListItem = oList.getItems(camlQuery);
            clientContext.load(collListItem);
            clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
        }

        function onQuerySucceeded(sender, args) {
            var Country = null;
            var City = null;
            var State = null;
            var count = 1;
            var orderdate = null;

            var listItemInfo = '';
            var listItemEnumerator = collListItem.getEnumerator();
            while (listItemEnumerator.moveNext()) {
                count++;
                var oListItem = listItemEnumerator.get_current();
                //alert('title ' + oListItem.get_item('Title'));
                listItemInfo += ' <strong>Title:</strong> ' + oListItem.get_item('Title') + '<br />';

                Country = oListItem.get_item('Country');
                City = oListItem.get_item('City');
                State = oListItem.get_item('State');
                orderdate = moment.utc(oListItem.get_item('OrderDate')).format('DD/MM/YYYY');

                var tr = $('<tr>');

                var td = $('<td>');
                td.append(Country);
                tr.append(td);

                var td = $('<td>');
                td.append(City);
                tr.append(td);

                var td = $('<td>');
                td.append(State);
                tr.append(td);

                var td = $('<td>');
                td.append(orderdate);
                tr.append(td);

                $("#tblsuperstore tbody").append(tr);
            }
            var table=$('#tblsuperstore').DataTable({
                stateSave: true,
                cache: true,
                responsive: true,
                fixedHeader: {
                    header: true,
                    footer: true
                }
                ,
                initComplete: function () {                  
                    //this.api().columns().every(function () {                    
                    //    var column = this;
                    //    if (column.index() !== 4 && column.index() !== 5 && column.index() !== 6 && column.index() !== 7 && column.index() !== 10) {
                    //        $(column.header()).append("<br>");
                    //        var select = $('<select><option value=""></option></select>')
                    //          .appendTo($(column.header()))
                    //          .on('change', function () {
                    //              var val = $.fn.dataTable.util.escapeRegex(
                    //                $(this).val()
                    //              );
                    //              column
                    //                .search(val ? '^' + val + '$' : '', true, false)
                    //                .draw();
                    //          });
                    //        column.data().unique().sort().each(function (d, j) {
                    //            select.append('<option value="' + d + '">' + d + '</option>');
                    //        });
                    //    }
                    //});
                    //responsive: {
                    //        details: {
                    //            type: 'column'
                    //        }
                    //}
                }

            });
            $(".filterhead").each(function (i) {
                var select = $('<select><option value=""></option></select>')
                    .appendTo($(this).empty())
                    .on('change', function () {
                        var term = $(this).val();
                        table.column(i).search(term, false, false).draw();
                    });
                table.column(i).data().unique().sort().each(function (d, j) {
                    select.append('<option value="' + d + '">' + d + '</option>')
                });
            });
            var position = collListItem.get_listItemCollectionPosition();
            if (position != null) {
                query.set_listItemCollectionPosition(position);
                collListItem = targetList.getItems(query);
                clientContext.load(collListItem);
                clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
            }
        }
        function onQueryFailed(sender, args) {
            alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
        }
    </script>

    <table id="tblsuperstore" class="display" border="1" width="100%" style="overflow-x:auto;">
        <thead>
            <tr>
                <th class="filterhead">Country</th>
                <th class="filterhead">City</th>
                <th class="filterhead">State</th>
                <th class="filterhead">Order Date</th>               
            </tr>
            <tr> <!--class="fh-fixedHeader"-->
                <th>Country</th>
                <th>City</th>
                <th>State</th>
                <th>Order Date</th>
            </tr>
        </thead>
        <tbody></tbody>
        <tfoot>
            <tr > <!--class="fh-fixedFooter"-->
                <th>Country</th>
                <th>City</th>
                <th>State</th>
                <th>Order Date</th>
            </tr>
        </tfoot>
    </table>

Best Regards,

Lee

Proposed as answer by Dennis Guo Tuesday, May 1, 2018 1:53 AM
Marked as answer by SP Assest Wednesday, May 2, 2018 4:46 PM

Monday, April 30, 2018 5:58 AM

Sir,

I tried to hide input filter dropdown for "City" Column. Because I had updated the code shown below ,But I find no luck.

Can any one please do let me know what's wrong in it

  
$(".filterhead").each(function (i) {
   if ( column.index() !== 0 ) {
     var select = $('<select><option value=""></option></select>')
        .appendTo($(this).empty())
               .on('change', function () {
                   var term = $(this).val();
                   table.column(i).search(term, false, false).draw();
               });
           table.column(i).data().unique().sort().each(function (d, j) {
               select.append('<option value="' + d + '">' + d + '</option>')
           });
}
       });

SP Assest

Edited by SP Assest Wednesday, May 2, 2018 6:13 AM

Wednesday, May 2, 2018 6:00 AM

Hi,

Sample code(no change for other part):

$(".filterhead").each(function (i) {
                if (i != 1) {
                    var select = $('<select><option value=""></option></select>')
                        .appendTo($(this).empty())
                        .on('change', function () {
                            var term = $(this).val();
                            table.column(i).search(term, false, false).draw();
                        });
                    table.column(i).data().unique().sort().each(function (d, j) {
                        select.append('<option value="' + d + '">' + d + '</option>')
                    });
                } else {
                   $(this).empty();
                }
            });

Best Regards,

Lee

Wednesday, May 2, 2018 6:41 AM

Answered by:

Have Jquery Data table Sort and Filter above table

Question

Answers

All replies