Render a table using data from recursive object arrays SharePoint Online

2008jackson

Since Sharepoint Online has a maximum threshold limit of 5000 on view, I am trying to generate one table with multiple recursive Object arrays of 5000 size.

The code below renders first list of 5000 entries and throws the error alert "DataTables warning: table id=table_id - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3".

Kindly assist to clean this code.

$(document).ready(function() {   
    GetListItemsRecursive();
});    
    
var response = response || [];  
var listURL = "SPO_Site/_api/web/lists/getbytitle('List_Name')/items?$top=5000&$select=Created,EncodedAbsUrl";    

GetListItemsRecursive(listURL);
function GetListItemsRecursive() {    
    $.ajax({    
        url: listURL,    
        type: "GET",    
        dataType: "json",    
        headers: {    
            "accept": "application/json;odata=verbose"    
        },    
        success: mySuccHandler,    
        error: myErrHandler    
    });    
}    
  
function mySuccHandler(data) {    
            response = response.concat(data.d.results);
            console.log(data);
            if(data.d.__next) {
                GetListItemsRecursive(data.d.__next);
            }    
    try {    
          
        $('#table_id').DataTable({    
            "pageLength": 100,
            "dom": 'Bfrtip',
            "buttons": [ {extend: 'searchBuilder', config: {columns: [0,1,2,3,4,5,6,7],},}, 'copy' ],
            "aaData": data.d.results,
            "aaSorting": [[2, "desc"]],
            "aoColumns": [  
            {    
                "mData": "Created"
            },             
            {    
                "mData": "EncodedAbsUrl",
                "mRender": function ( data, type, full )
                {return '<a href="'+data+'" target="_blank">View</a>';}             
            }
            ]    
        });    
    } catch (e) {    
        alert(e.message);    
    }    
}    
    
function myErrHandler(data, errMessage) {    
    alert("Error");    
}   

colin

It looks like GetListItemsRecursive is being called twice, on lines 2 and 8 - therefore the table will be initialised twice, which is the error you're seeing.

Colin

2008jackson

Hi Colin, removing line 2 gives me the same error. It loads the first array of 5000 results and throws the error again. Please assist.

kthorngren

That would indicate you are calling mySuccHandler more than once or you have $('#table_id').DataTable() somewhere else in your code.

Sounds like you want to make multiple ajax requests to populate the Datatable. With each of the requests adding more rows to the table. Is this correct? If so then initialize Datatables once in $(document).ready() removing "aaData": data.d.results, to init a blank Datatable. In the success function replace the init code in line 30 with rows.add() to add the fetched rows.

Kevin

2008jackson

Hi Kthorngren,

To the best of my knowledge, I've modified the code with your idea. I'm still in a loop. The same error comes up using the rows.add API. Could you pls have a look again? Appreciate if you could modify my code below.

$(document).ready(function() {});    
    
var response = response || [];  
var listURL = "SPO_Site/_api/web/lists/getbytitle('List_Name')/items?$top=5000&$select=Created,EncodedAbsUrl";    
GetListItemsRecursive(listURL);
function GetListItemsRecursive() {    
    $.ajax({    
        url: listURL,    
        type: "GET",    
        dataType: "json",    
        headers: {    
            "accept": "application/json;odata=verbose"    
        },    
        success: mySuccHandler,    
        error: myErrHandler    
    });    
}    
  
function mySuccHandler(data) {
            response = response.concat(data.d.results);
            console.log(data);
            if(data.d.__next) {
                GetListItemsRecursive(data.d.__next);
            }        
    try {    

       
        var table = $('#table_id').DataTable({    
            "pageLength": 100,
            "dom": 'Bfrtip',
            "buttons": [ {extend: 'searchBuilder', config: {columns: [0,1],},}, 'copy' ],
            "aoColumns": [  
            {    
                "mData": "Created",
                "render": function (data, type, row) {
                data = moment(data).format('DD-MM-YYYY HH:mm');
                return data;}               
            },             
            {    
                "mData": "EncodedAbsUrl",
                "mRender": function ( data, type, full )
                {return '<a href="'+data+'" target="_blank">View</a>';}             
            }
            ]    
        });   
        table.rows.add(response).draw();    
    } catch (e) {    
        alert(e.message);    
    }    
}    
    
function myErrHandler(data, errMessage) {    
    alert("Error");    
}

colin

At a glance, it looks fine, though I would move the code into your empty function:

$(document).ready(function() {});    

Can you create a test case so we can debug your issue, please. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

2008jackson

Hi Colin,

Since the data is in Sharepoint, I'm unable to create a fiddle or test case. I've moved the code into the empty function. I still have a loop.

I'm able to generate the desired output with code below without using Datatables. However, kindly assist to load this recursive RestAPI call in Datatables.

<script type="text/javascript" src="https://emiratesgroup.sharepoint.com/teams/EKDL/SiteAssets/js/jquery-3.5.1.js"></script>
<script>
var response = response || [];
var listURL = "SPO_Site/_api/web/lists/getbytitle('List_Name')/items?$top=5000&$select=Created,EncodedAbsUrl";
GetListItemsRecursive(listURL);
function GetListItemsRecursive(restURL) {
    $.ajax({
        url: restURL,
        method: "GET",
        contentType: "application/json;odata=verbose",
        headers: {
            "accept": "application/json;odata=verbose"
        },
        success: function (data) {
            response = response.concat(data.d.results);
            console.log(data);
            var tbl=$("<table/>").attr("id","Table");
            $("#Div").append(tbl);
            for(var i=0;i<response.length;i++)
            {
                var tr="<tr>";
                var td1="<td>" + i + ' ' +response[i]["Created"]+"</td>";
                var td2="<td>"+response[i]["EncodedAbsUrl"]+"</td>";
               $("#Table").append(tr+td1+td2); 
            }

            if(data.d.__next) {
                GetListItemsRecursive(data.d.__next);
            }
        },
        error: function (error) {
            console.error(error);
        }
    });
}
</script>
<div id="Div"> 
</div>

kthorngren

To make sure you initialize Datatables only once move lines 28-45 outside of the function. Insert that code between lines 1 and 3.

Kevin

2008jackson

Kevin, appreciate the assistance. Following your suggestion, i was able to get rid of the error alerts. However, the data being rendered is incorrect. It seems to be in a loop where the rendered data count goes increments. I totally have 8900 rows in my source but the rendered data table shows 100/140000 and increases in a loop. Please have a look.

$(document).ready(function() {

    var table = $('#table_id').DataTable({
        "pageLength": 100,
        "dom": 'Bfrtip',
        "buttons": [ searchBuilder, copy],
        "aaSorting": [[2, "desc"]],
        "aoColumns": [{
                "mData": "Created"
                }
            },
            {
                "mData": "EncodedAbsUrl",
                "mRender": function(data, type, full) {
                    return '<a href="' + data + '" target="_blank">View</a>';
                }
            }
        ]
    });

    var response = response || [];
    var listURL = "SPO_Site/_api/web/lists/getbytitle('List_Name')/items?$top=5000&$select=Created,EncodedAbsUrl";
    GetListItemsRecursive(listURL);

    function GetListItemsRecursive() {
        $.ajax({
            url: listURL,
            type: "GET",
            dataType: "json",
            headers: {
                "accept": "application/json;odata=verbose"
            },
            success: mySuccHandler,
            error: myErrHandler
        });
    }

    function mySuccHandler(data) {
        response = response.concat(data.d.results);
        console.log(data);
        if (data.d.__next) {
            GetListItemsRecursive(data.d.__next);
        }
        try {
            table.rows.add(response).draw();
        } catch (e) {
            alert(e.message);
        }
    }

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

kthorngren

However, the data being rendered is incorrect. It seems to be in a loop where the rendered data count goes increments.

I would start by debugging this code:

        if (data.d.__next) {
            GetListItemsRecursive(data.d.__next);
        }

Seems like it is fetching more data when you don't expect it to. We would need to see a link to your page or a test case replicating the issue to help debug. But it doesn't seem like Databalbes is the issue as its not involved with fetching the data.

Kevin

2008jackson

As mentioned, the json comes from sharepoint and im unable to duplicate the data in jsfiddle Appreciate if you could point me to the direction on how to make successful recursive calls to the same json source

2008jackson

Looking further, I've decided to take another path by specifying skip parameters in my RestAPI call. I'm unable to retrieve any data on the table. Please have a look.

var results;
var allResults = [];

$(document).ready(function () {
    load();
});


function load() {

    var call1 = $.ajax({
      url: "SPO_Site/_api/web/lists/getbytitle('List_Name')/items?$select=Created&$top=5000&$skip=0&$orderby=Date desc",
            method: "GET",
            headers: { "Accept": "application/json; odata=verbose" },
            success: successHandler,
            error: errorHandler
   });

    var call2 = $.ajax({
            url: "SPO_Site/_api/web/lists/getbytitle('List_Name')/items?$select=Created&$top=5000&$skip=5000&$orderby=Date desc",
            method: "GET",
            headers: { "Accept": "application/json; odata=verbose" },
            success: successHandler,
            error: errorHandler

   });

    function successHandler(a) {
        var jsonObject = JSON.parse(a.body);
        results = jsonObject.d.results;

        if (allResults.length > 0)
            allResults = allResults.concat(results);

        else
            allResults = results;        
    }

    function errorHandler(data, errorCode, errorMessage) {
        console.log("Could not complete call: " + errorMessage);        
    };


    // When both Ajax requests were successful
   $.when(call1, call2).done(function(a1, a2){
      console.log(allResults);
    $('#table_id').DataTable({
        data:allResults ,
        columns: [
                    { data: "Created" }
        ]
    });
   });  
};

kthorngren

I'm unable to retrieve any data on the table. Please have a look.

Does that mean there is no data in the XHR response when looking at the browser's network inspector? Or that the data is there but its not populating the table?

This is an issue that we will need to look at to help debug. Without seeing what is returned its hard to say what the problem might be.. Listing the JS code is not enough to offer suggestions.

if you could point me to the direction on how to make successful recursive calls to the same json source

Did you debug the section of code I mentioned? It looks for data.d.__next to see when to stop fetching the data. Seems this is not working as expected. Again without seeing the data its hard to say what the problem might be.

Kevin

2008jackson

XHR returns JSON data with status 200. Debug data available at https://debug.datatables.net/irulum

I now have the following error on console. "Uncaught SyntaxError: Unexpected token u in JSON at position 0 at JSON.parse (<anonymous>)"

2008jackson

Issue sorted by modifying success handler below. Thank you.

    function mySuccHandler(a) {
        results = a.d.results;
        if (allResults.length > 0)
            allResults = allResults.concat(results);
        else
            allResults = results;        
    }