1

Edit: final code:

var requestIOBInfo1 = siteUrl + "/_api/web/Lists/getByTitle('IOBoard')/Items?$\
filter=((Division eq '" + iDivision + "') and (AccountStatus eq 'Active'))&$\
select=Id, Title, InOut, TodaySchedule&$\
orderby=Title desc";

var ajaxIOB1 = $.ajax({
    url: requestIOBInfo1,
    type: "GET", 
    dataType: "json", 
    headers: { "accept": "application/json;odata=verbose" },
    success: successAI, 
    error: myErrHandler
});

function successAI(data) {
try {

    var tables = ['table1', 'table2', 'table3'];  //current table names in separate html file
    var nItems = data.d.results.length;
    var maxItemsPerTable = nItems / tables.length;

    for(var i = 0; i < tables.length; i++) {
        var table = tables[i];
        var minIndex = i * maxItemsPerTable;
        var maxIndex = minIndex + maxItemsPerTable;
        var tableItems = data.d.results.slice(minIndex, maxIndex);
        var dataTableExample = $('#' + table).DataTable();

        if (dataTableExample != 'undefined') {
            dataTableExample.destroy();
        }

        dataTableExample = $('#' + table).DataTable({
            srollY: 300,
            "aaData": tableItems,
            "aoColumns": [
                { "mData": "Title" },
                { "mData": "InOut" }, 
                { "mData": "TodaySchedule" }
            ],
        });
    }
} catch (e) {
    alert(e.message); }
}

function myErrHandler(data, errCode, errMessage) {
alert("Error: " + errMessage);
}

and below is the HTML - in a content editor:

    <!DOCTYPE html>  
<html>  
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<meta http-equiv="X-UA-Compatible" content="IE=11">
    <title>In/Out Board A - I</title>  
    <script type="text/javascript" src="/SiteAssets/js/jquery.min.js"></script>
    <script type="text/javascript" src="/SiteAssets/js/AMSIOBoardByDiv.js"></script>  
    <link rel="stylesheet" type="text/css" href="/SiteAssets/js/jquery.dataTables.min.css">  
    <link rel="stylesheet" type="text/css" href="/SiteAssets/js/dataTables.jqueryui.min.css"> 
    <script type="text/javascript" src="/SiteAssets/js/jquery.dataTables.min.js"></script>  

</head>  
<body>  
    <p class="alertBlue" id="AtoI"></p>
    <table width="100%">
        <tr>
            <td width="30%" valign="top">
                <table id="table1" class="display" cellspacing="0" width="100%">
                    <thead>  
                        <tr>  
                            <th width="40%">Name</th> 
                            <th width="10%">In/Out</th> 
                            <th width="50%">Today Schedule</th>
                        </tr>  
                    </thead>  
                </table>
            </td>
            <td width="5%" valign="top">&nbsp;</td>
            <td width="30%" valign="top">
                <table id="table2" class="display" cellspacing="0" width="100%">
                    <thead>  
                        <tr>  
                            <th width="40%">Name</th> 
                            <th width="10%">In/Out</th> 
                            <th width="50%">Today Schedule</th>
                        </tr>  
                    </thead>  
                </table>
            </td>
            <td width="5%" valign="top">&nbsp;</td>
            <td width="30%" valign="top">
                <table id="table3" class="display" cellspacing="0" width="100%">
                    <thead>  
                        <tr>  
                            <th width="40%">Name</th> 
                            <th width="10%">In/Out</th> 
                            <th width="50%">Today Schedule</th>
                        </tr>  
                    </thead> 
                </table>
            </td>

        </tr>
    </table>
</body>  
</html> 

Is there a way to equally split the data between multiple data tables? (i.e.: if there are 92 records returned, first datatable should contain records 1-46, next datatable from 47-92)

I can get the number of records with something like countIOB = data.d.results.length;, but I can't figure out how to equally distribute the data with some order (i.e. by lastName or firstName).

2

You could make a single REST call (applying the ordering Michael mentioned) and fraction results between the number of tables:

var tables = ['tableAI1', 'tableAI2', 'tableAI3'];
var nItems = data.d.results.length;
var maxItemsPerTable = nItems / tables.length;
for (var i = 0; i < tables.length; i++) {
    var table = tables[i];
    var minIndex = i * maxItemsPerTable;
    var maxIndex = minIndex + maxItemsPerTable; 
    var tableItems = data.d.results.slice(minIndex, maxIndex);
    var dataTableExample = $('#' + table).DataTable();
    if (dataTableExample != 'undefined') {
        dataTableExample.destroy();
    }
    dataTableExample = $('#' + table).DataTable({
        srollY: 300,
        "aaData": tableItems,
        "aoColumns": [
            { "mData": "Title" },
            { "mData": "InOut" },
            { "mData": "TodaySchedule" }
        ],
    });
}
  • I edited the code (see initial post) but it returned blank data tables. Did I insert the code incorrectly? – Tamras Merin Jan 16 '18 at 20:49
  • Could you post the relative html and a sample json response? – Steve T Jan 16 '18 at 21:56
  • I posted the html not sure how to get the json response from SharePoint... – Tamras Merin Jan 16 '18 at 22:11
  • I think I got it to work, I found an extra character left over from when I cleared the previous code. Your code works wonderfully. Thank you! – Tamras Merin Jan 16 '18 at 22:47
  • Not sure this is exactly what you need as your tables seem to be divided by the first letter of the Title field A-I, J-Q and R-Z, but it would be easy to adapt the code to assign the items to their corresponding table instead of distributing them by number. – Steve T Jan 16 '18 at 22:53
0

Add an order by to the REST call and get the first third, second third, and final third parts, e.g.:

var requestIOBInfo1 = siteUrl + "/_api/web/Lists/getByTitle('IOB')/Items?$\
     $filter=(...)&\
     $select=Id, Title, InOut, TodaySchedule&\
     &$orderby=InOut desc&\
     $top=32

Replace the last part with $top='32'&amp;$skip='64' and $top='64'&amp;$skip='92' to get the second and third parts, for example.

If you don't know how many records you'll have use /_api/web/lists/getbytitle('listName')/ItemCount to create a variable and set the offset dynamically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.