Datatables - equally split data between multiple datatables

Question

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).

Steve T · Accepted Answer · 2018-01-16 20:04:55Z

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" }
        ],
    });
}

answered Jan 16, 2018 at 20:04

Steve T

1495 bronze badges

I edited the code (see initial post) but it returned blank data tables. Did I insert the code incorrectly?

Tamras Merin
– Tamras Merin

2018-01-16 20:49:29 +00:00
Commented Jan 16, 2018 at 20:49
Could you post the relative html and a sample json response?

Steve T
– Steve T

2018-01-16 21:56:42 +00:00
Commented Jan 16, 2018 at 21:56
I posted the html not sure how to get the json response from SharePoint...

Tamras Merin
– Tamras Merin

2018-01-16 22:11:29 +00:00
Commented Jan 16, 2018 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
– Tamras Merin

2018-01-16 22:47:18 +00:00
Commented Jan 16, 2018 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
– Steve T

2018-01-16 22:53:44 +00:00
Commented Jan 16, 2018 at 22:53

| Show 1 more comment

dikuw · Accepted Answer · 2018-01-16 19:43:34Z

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'&$skip='64' and $top='64'&$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.

Stack Exchange Network

Datatables - equally split data between multiple datatables

2 Answers 2

Your Answer

Linked

Hot Network Questions

Datatables - equally split data between multiple datatables

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions