Using DataTables With Sharepoint

zgoforth · August 2020

Hello, so I have been having issues with appending a DataTable on my landing page. I am pulling from three separate lists on different subsites through SharePoint. I will attach my code below. The weird part about it is I can get it to work perfectly if the data is static (of course...) but I will link a JSFiddle with the static data that works.

<html>
<head>
<link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<title><strong>X, DAR-Q, & Z Deliverables</strong></title>
</head>
<body>
    <div class="container">
      <h4 class="text-center">X, DAR-Q, & Z Deliverables</h4>
        <table class = "display">
            <thead>
              <tr>
                <th>Program</th>
                <th>Deliverable</th>
                <th>To</th>
                <th>Date</th>
                <th>Approved</th>
                <th>Notes</th>
              </tr>
            </thead>
            <tbody>
            </tbody>
        </table>    
    </div>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
<script>
function loadData(source, url) {
  return fetch(url, { headers: { accept: "application/json; odata=verbose" } }) // make request
    .then((r) => {
      if (!r.ok) throw new Error("Failed: " + url);  // Check for errors
      return r.json();  // parse JSON
    })
    .then((data) => data.d.results) // unwrap to get results array
    .then((results) => {
      results.forEach((r) => (r.source = source)); // add source to each item
      return results;
    });
}

window.addEventListener("load", function () {
  Promise.all([
    loadData("XDeliverables", "/_api/web/lists/getbytitle('XDeliverables')/items?$select=Program,To,Date,Approved,Notes"),
    loadData("YDeliverables", "/_api/web/lists/getbytitle('YDeliverables')/items?$select=Program,To,Date,Approved,Notes"),
    loadData("ZDeliverables", "/_api/web/lists/getbytitle('ZDeliverables')/items?$select=Program,To,Date,Approved,Notes"),
  ])
    .then(([r1, r2, r3]) => {
      const objItems = r1.concat(r2,r3);
      var tableContent =
        '<table id="deliverablesTable" style="width:100%" border="1 px"><thead><tr><td><strong>Program</strong></td>' +
        "<td><strong>To</strong></td>" +
        "<td><strong>Date Submitted</strong></td>" +
        "<td><strong>Approved</strong></td>" +
        "<td><strong>Notes</strong></td>" +
        "</tr></thead><tbody>";

      for (var i = 0; i < objItems.length; i++) {
        tableContent += "<tr>";
        tableContent += "<td>" + objItems[i].Program + "</td>";
        tableContent += "<td>" + objItems[i].To + "</td>";
        tableContent += "<td>" + objItems[i].Date + "</td>";
        tableContent += "<td>" + objItems[i].Approved + "</td>";
        tableContent += "<td>" + objItems[i].Notes + "</td>";
        tableContent += "</tr>";
      }
      $(tableContent).appendTo("#deliverables").DataTable();
    })
    .catch((err) => {
      alert("Error: " + err);
      console.error(err);
    });
});
</script>
</body>

colin · August 2020

Yeah, there's a lot going on there, we'd really need to see that in operation to be able to debug it,

Colin

zgoforth · August 2020

@colin check my most recent edit

zgoforth · August 2020

This is my updated code, that uses ajax to pull from the other sharepoint list, but when I place the code in the script editor, the DataTable Loads but no Items post to the DataTable and it says "Loading..."
https://i.stack.imgur.com/BXK4u.png

<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.js"></script>
<script src="https://momentjs.com/downloads/moment.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" />
<table id="myTable" class="display" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th>Program</th>
      <th>Deliverable</th>
      <th>To</th>
      <th>Date</th>
      <th>Approved</th>
      <th>Notes</th>
    </tr>
  </thead>
</table>
<script>
  // UMD
  (function(factory) {
    "use strict";

    if (typeof define === 'function' && define.amd) {
      // AMD
      define(['jquery'], function ($) {
        return factory( $, window, document );
      });
    }
    else if (typeof exports === 'object') {
      // CommonJS
      module.exports = function (root, $) {
        if (!root) {
          root = window;
        }

        if (!$) {
          $ = typeof window !== 'undefined'
            ? require('jquery')
            : require('jquery')( root );
        }

        return factory($, root, root.document);
      };
    } else {
      // Browser
      factory(jQuery, window, document);
    }
  }
  (function($, window, document) {
    $.fn.dataTable.render.moment = function (from, to, locale) {
      // Argument shifting
      if (arguments.length === 1) {
        locale = 'en';
        to = from;
        from = 'YYYY-MM-DD';
      } else if (arguments.length === 2) {
        locale = 'en';
      }

      return function (d, type, row) {
        var m = window.moment(d, from, locale, true);

        // Order and type get a number value from Moment, everything else
          // sees the rendered value
          return m.format(type === 'sort' || type === 'type' ? 'x' : to);
        };
      };
    }));
</script>
<script>
$(document).ready(function() {
  $('#myTable').DataTable({
    'ajax': {
      'url': "_api/web/lists/getbytitle('XDeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable",
      'headers': { 'Accept': 'application/json;odata=nometadata' },
      'dataSrc': function(data) {
        return data.value.map(function(item) {
          return [
            item.Program,
            item.Deliverable,
            item.To,
            new dueDate(item.Date),
            item.Approved,
            item.Notes
          ];
          console.log(item);
        });
      }
    },
    columnDefs: [{
      targets: 4,
      render: $.fn.dataTable.render.moment('YYYY/MM/DD')
    }]
  });
});
</script>

kthorngren · August 2020

Your example doesn't replicate the way you are adding the data. Maybe you can try replicating lines 47-65, in your fiddle, to see what happens.

Please describe the problem you are having. Do you get errors?

Kevin

zgoforth · August 2020

DataTables warning: table id=myTable - Ajax error. For more information about this error, please see http://datatables.net/tn/7

zgoforth · August 2020

@kthorngren I can try replicating it in Fiddle, but sharepoint won't work so the information I am trying to pull just won't be there

zgoforth · August 2020

@kthorngren DataTables warning: table id=myTable - Ajax error. For more information about this error, please see http://datatables.net/tn/7

kthorngren · August 2020

Looks like we cross posted

Have you followed the troubleshooting steps provided in the link?
http://datatables.net/tn/7

Kevin

zgoforth · August 2020

@kthorngren I fixed the error, but so now how can I call three different URLS through the AJAX? All three lists have the same items but i need to pull all of them

kthorngren · August 2020

The ajax option doesn't have an option to list multiple URLs. You will need to use an ajax call like your first post and combine the data.

Kevin

zgoforth · August 2020

@kthorngren Ok, and look at this. The Approved Field Says (Invalid Date) When it should show a Yes or no? It is a choice option on the sharepoint list could that be why

zgoforth · August 2020

zgoforth · August 2020

Also, I tried doing what you said @kthorngren and the table doesn't appear, just the headers.

$(document).ready(function() {
  $('#myTable').DataTable({
    Promise.all([
            loadData("AMMODeliverables", "https://gemini3group.sharepoint.com/sites/Projects/USMC/AMMO/_api/web/lists/getbytitle('AMMODeliverables')/objItemss?$select=Program,To,Date,Approved,Notes,Deliverable"),
            loadData("DarQDeliverables", "https://gemini3group.sharepoint.com/sites/Projects/USMC/DARQ/_api/web/lists/getbytitle('DarQDeliverables')/objItemss?$select=Program,To,Date,Approved,Notes,Deliverable"),
            loadData("WTBnDeliverables", "https://gemini3group.sharepoint.com/sites/Projects/USMC/WTBn/_api/web/lists/getbytitle('WTBnDeliverables')/objItemss?$select=Program,To,Date,Approved,Notes,Deliverable"),
        ])
        .then(([r1, r2, r3]) => {
            const objobjItemss = r1.concat(r2, r3);
            console.log(JSON.stringify(objItems));
            console.log(objobjItemss);
        'dataSrc': function(data) {
        return data.value.map(function(objItems) {
          return [
            objItems.Program,
            objItems.Deliverable,
            objItems.To,
            objItems.Date,
            objItems.Approved,
            objItems.Notes
          ];
        });
      }
      })
    },
    columnDefs: [{
      targets: 4,
      render: $.fn.dataTable.render.moment('YYYY/MM/DD')
    }]
  });
});

kthorngren · August 2020

Promise.all

This is not a Datatables option. So you will need to use it like you have it in your first code snippet.

The Approved Field Says (Invalid Date) When it should show a Yes or no? It is a choice option on the sharepoint list could that be why

You have:

    columnDefs: [{
      targets: 4,
      render: $.fn.dataTable.render.moment('YYYY/MM/DD')
    }]

Column numbers start at 0 so column 4 is the Approved column.

Kevin

zgoforth · August 2020

@kthorngren Why wont the Promise.all work with DataTables? I am so confused on why and how to get multiple urls. Could I make a variable urls?

kthorngren · August 2020

Why wont the Promise.all work with DataTables?

When using $('#myTable').DataTable({ ... }) to initialize Datatables you can use the options listed in the docs:
https://datatables.net/reference/option/

What you tried is not a configuration option Datatables has.

Promise.all is a standard Javascript method to aggregate asynchronous operations. Teaching how to use it is outside the scope of this forum. Stack Overflow or other tutorials available are better resources.

Here is a simple example of fetching data from multiple URLs using jQuery Ajax. The example initializes a blank Datatable then uses rows.add() to add the row data from each Ajax response.
http://live.datatables.net/regatofe/33/edit

This is just to give you an idea of what you need. Your specific set of requirements will dictate how you need to approach this. It starts with understanding the JSON response data you are getting and setting up the Datatables columns using columns.data as it looks like you are getting object based data. Something like your fiddle.

Kevin

zgoforth · August 2020

@kthorngren It works pretty well for the most part, but when I fetch the data nothing loads. Could it be the urls I used?

zgoforth · August 2020

<script>
$(document).ready(function() {
  
  $('#myTable').DataTable( {
    "columns": [
      { "data": "Program" },
      { "data": "Deliverable" },
      { "data": "To" },
      { "data": "Date" },
      { "data": "Approved" },
      { "data": "Notes" }
    ]
  } );
  
  $('button').on('click', function () {
    var urls = ["/_api/web/lists/getbytitle('AMMODeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable", "/_api/web/lists/getbytitle('DarQDeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable", "/_api/web/lists/getbytitle('WTBnDeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable"];
    
    for (i=0; i < urls.length; i++) {
      $.ajax({
        url: urls[i],
        success: function (data) {
          data = JSON.parse(data);
          var table = $('#myTable').DataTable();
          table.rows.add( data.data ).draw();
        }
      });    
    }

  });
  
} );
</script>

kthorngren · August 2020

but when I fetch the data nothing loads. Could it be the urls I used?

Start by validating the Ajax requests with the browser's network inspector tool. If needed steps can be found in this technote. The table.rows.add( data.data ) statement in my example is specific to the data structure returned.

If you still need help please post a link to your page so we can take a look. If you can't do that then post a snippet from the JSON response as found in the network tools. Also post your Datatables init code. It could be the data is not returned in a structure that Datatables supports. Which means it will need to be manipulated before using rows.add().

Kevin

zgoforth · August 2020

I read the technote and followed it but in my Dev Tools, I cannot see the Ajax Request Made

kthorngren · August 2020

I used the button click event to demonstrate adding the rows via ajax and what an empty datatable looks like. Are you wanting to click a button to load the data? Maybe you are wanting to put that click event into a function that is called in the $(document).ready(function() {}); function, like this:

function loadData() {
    var urls = ["/_api/web/lists/getbytitle('AMMODeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable", "/_api/web/lists/getbytitle('DarQDeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable", "/_api/web/lists/getbytitle('WTBnDeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable"];
     
    for (i=0; i < urls.length; i++) {
      $.ajax({
        url: urls[i],
        success: function (data) {
          data = JSON.parse(data);
          var table = $('#myTable').DataTable();
          table.rows.add( data.data ).draw();
        }
      });   
}
$(document).ready(function() {
   
  $('#myTable').DataTable( {
    "columns": [
      { "data": "Program" },
      { "data": "Deliverable" },
      { "data": "To" },
      { "data": "Date" },
      { "data": "Approved" },
      { "data": "Notes" }
    ]
  } );
   
 loadData();
   
} );

Kevin

zgoforth · August 2020

When I tried that way the datatable doesn't even appear

kthorngren · August 2020

Do you get errors in your browser's console?

There may be a syntax error in the above code since I didn't test it.

Kevin

zgoforth · August 2020

Uncaught SyntaxError: Unexpected end of input

zgoforth · August 2020

@kthorngren I tried fixing the Syntax Error and it just gives me another syntax error

kthorngren · August 2020

Looks like it is missing a close } for the for loop:

function loadData() {
    var urls = ["/_api/web/lists/getbytitle('AMMODeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable", "/_api/web/lists/getbytitle('DarQDeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable", "/_api/web/lists/getbytitle('WTBnDeliverables')/items?$select=Program,To,Date,Approved,Notes,Deliverable"];
      
    for (i=0; i < urls.length; i++) {
      $.ajax({
        url: urls[i],
        success: function (data) {
          data = JSON.parse(data);
          var table = $('#myTable').DataTable();
          table.rows.add( data.data ).draw();
        }
      });  
    } // missing bracket
}

Kevin

zgoforth · August 2020

@kthorngren I fixed it, and wrapped the button around both the loadData function aswell as the $(document).ready(function() and it works better around the loadData, but still doesn't populate anything to the table and says "No data available in the Datatable"

kthorngren · August 2020

wrapped the button around both the loadData function

What button?

but still doesn't populate anything to the table and says "No data available in the Datatable"

Have you verified the loadData() function is being called?

Have you verified the data in the success function?

Do you get errors in the browser's console?

Please post a link to your page so we can take a look and help debug.

Kevin

zgoforth · August 2020

@kthorngren Also, when I try to see the data returned in the net work console under (XHR), I can't find anything returned??

kthorngren · August 2020

when I try to see the data returned in the net work console under (XHR), I can't find anything returned??

Then you will need to debug your server scripts to find out why. Are the URLs correct?

Kevin

Using DataTables With Sharepoint

Using DataTables With Sharepoint

Answers

Howdy, Stranger!

Categories

DataTables

Using DataTables With Sharepoint

Using DataTables With Sharepoint

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables