SharePoint list as data source for jQuery DataTables plugin

Question

I trying to create a table using a SharePoint list as source. I followed this tutorial and it worked perfectly. But this method would not work if you need to reload the data using, let's say a drop down list. This is what I have tried so far:

JS:

theYear = $('#dropdown').val(); // Selected Year
theRO = $('#dropdownRO').val(); // Selected RO
oTable = $('#example').dataTable({
"sAjaxSource": "https:xxx-xxxx/_api/web/lists/getByTitle('Consolidated%20LC%20Report')/items()?$filter=Fiscal_x0020_Year%20eq%20'" + theYear + "' and Regional_x0020_Office eq '" + theRO + "'&$orderby=Id&$select=Id,Title,Fiscal_x0020_Year,Notices_x0020_Received,Declined_x0020_Participation,Selected_x0020_Field_x0020_Revie,Selected_x0020_File_x0020_Review,Pending,Pending_x0020_Previous_x0020_Yea,Controversial,GFP_x0020_Reviews,NAD_x0020_Appeals,Mediation_x0020_Cases,Monthly_x0020_Cost_x0020_Savings,Monthly_x0020_Expenditure,Regional_x0020_Office,Month_Number",
    "sAjaxDataProp": "data",
    "aoColumns": [{
    "mData": "Id",
    "bVisible": false
}, //Invisible column
{
    "mData": "Title"
}, {
    "mData": "Notices_x0020_Received"
}, {
    "mData": "Declined_x0020_Participation"
}, {
    "mData": "Selected_x0020_Field_x0020_Revie"
}, {
    "mData": "Selected_x0020_File_x0020_Review"
}, {
    "mData": "Pending"
}, {
    "mData": "Pending_x0020_Previous_x0020_Yea"
}, {
    "mData": "Controversial"
}, {
    "mData": "GFP_x0020_Reviews"
}, {
    "mData": "NAD_x0020_Appeals"
}, {
    "mData": "Mediation_x0020_Cases"
}, {
    "mData": "Monthly_x0020_Cost_x0020_Savings",
    "fnRender": function (obj, val) {
        return accounting.formatMoney(val);
    }
}, {
    "mData": "Monthly_x0020_Expenditure",
    "fnRender": function (obj, val) {
        return accounting.formatMoney(val);
    }
}],
    "aLengthMenu": [
    [25, 50, 100, 200, -1],
    [25, 50, 100, 200, "All"]
],
    "iDisplayLength": -1, //Number of rows by default. -1 means All Records
"sPaginationType": "full_numbers",
    "aaData": data.d.results,
    "bJQueryUI": false,
    "bProcessing": true,
"bServerSide": true,
    "bDeferRender": true,
"bRetrieve": true,
"bInfo": true,
"bAutoWidth": true,
"bDestroy": true,
"bFilter": true,
    "drawCallback": function (settings) {
    alert('DataTables has redrawn the table');
},
    "sDom": 'T&;"clear"&;frtip',
    "oTableTools": {
    "aButtons": ["xls"],
        "sSwfPath": "../../Style Library/js/datatables/TableTools/media/swf/copy_csv_xls_pdf.swf",
},
    "sSearch": "Filter",
    "fnDrawCallback": function () {
    //Add totals row
    var Columns = $("#example > tbody").find("> tr:first > td").length;
    $('#example tr:last').after('<tr><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td><td  class="total"></td></tr>');
    //Formating the Total row number to no decimals
    $("#example tr:last td:not(:first,:last)").text(function (i) {
        var t = 0;
        $(this).parent().prevAll().find("td:nth-child(" + (i + 2) + ")").each(function () {
            t += parseFloat($(this).text().replace(/[\$,]/g, '') * 1);
        });
        return parseInt(t * 100, 10) / 100;
    });
    //Format the monthly expenditure and savings to currency formatFormating the currency
    var cell = new Array();
    cell[0] = $('#example tr:last td:nth-child(12)').text();
    cell[1] = $('#example tr:last td:nth-child(13)').text();
    $('#example tr:last').find('td:nth-child(12)').html(accounting.formatMoney(cell[0]));
    $('#example tr:last').find('td:nth-child(13)').html(accounting.formatMoney(cell[1]));
    $('#example tr:last').find('td:last').hide();
} //hides extra td that was showing
}); //End of Datatable()

Any ideas? Thanks!

Sorry to be a grammar nazi, but you say that it "would not work". Does this mean that you have not tried it? If you have attempted to reload the table, can you show us what you've tried for that? — wjervis, Jun 23 at 18:00
Using the tutorial I mentioned in my question, once you load the data using ajax, and try to change the parameter in the CAMLquery it would not fire any event because jQuery ajax sees that the data has been loaded already. So, but using DataTables, from what I've read, you can use "sAjaxSource" or just "ajax" options, depending who you ask, and then adding later the DataTables draw() or drawCallback() function, it should refresh the ajax call and re-load the table with the new data. — cubanGuy, Jun 23 at 18:13
This is what I tried already Link. That's another question I made related for a different issue, but it is a working code. Thanks! — cubanGuy, Jun 23 at 18:17
So adding your code to something like this: $("#idForDropDown").change(function(){ //Your code here }); will not work? — wjervis, Jun 23 at 18:32
No, because once the Ajax call has been made already, it won't load again.link. This link leads to another question I made in stackoverflow regarding this same issue. There someone recommended the ajax call builtin with DataTables plugin, but no luck so far. Thansk! — cubanGuy, Jun 23 at 20:17

wjervis · Answer 1 · 2014-06-24 13:22:21Z

I was able to get something working with the following (you will need to adapt to your situation). The key thing was the .destroy(), which allows you to reinitialize the DataTable.

HTML:

<select id='ddlStatus'>
    <option value='Inserted'>Inserted</option>
    <option value='Approved'>Approved</option>
    <option value='Shipped'>Shipped</option>
    <option value='Completed'>Completed</option>
</select>
<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Order</th>
            <th>Order ID</th>
            <th>Order Status</th>
            <th>Customer ID</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>Order</th>
            <th>Order ID</th>
            <th>Order Status</th>
            <th>Customer ID</th>
        </tr>
    </tfoot>
</table>

JavaScript:

var dataTableExample = 'undefined';
$(document).ready(function () {
    $('#ddlStatus').change(function (event) {
        loadDataTable();
        event.preventDefault();
    });
    loadDataTable();
});

function loadDataTable() {
    var status = $('#ddlStatus').val();
    var oDataUrl = "<site>/_api/web/lists/getbytitle('OrdersLibrary')/items?$select=Title,OrderID,OrderStatus,CustomerID&$filter=OrderStatus eq '" + status + "'";
    $.ajax({
        url: oDataUrl,
        type: "GET",
        dataType: "json",
        headers: {
            "accept": "application/json;odata=verbose"
        },
        success: mySuccHandler,
        error: myErrHandler
    });
}

function mySuccHandler(data) {
    if(dataTableExample != 'undefined') {
        dataTableExample.destroy();
    }
    dataTableExample = $('#example').DataTable({
        "aaData": data.d.results,
            "aoColumns": [{
            "mData": "Title"
        }, {
            "mData": "OrderID"
        }, {
            "mData": "OrderStatus"
        }, {
            "mData": "CustomerID"
        }]
    });
}
function myErrHandler(data, errCode, errMessage) {
    alert("Error: " + errMessage);
}

Thanks wjervis! I really like your approach, but it doesn't work for me either. Were you able to to reload the data based on the change of your drop down option? The only way I have been able to get almost what I would like is using SPServices and $("table").remove();, but again, that way I ca't take advantange of Datatables addons. — cubanGuy, Jun 24 at 15:34
Yes, I am able to reload the DataTable. What error do you get when you use my approach? Are you making sure you destroy the DataTable object before reloading the data? — wjervis, Jun 24 at 15:36
I don't get any error, and the data load perfectly, but only once. Fiddle. Notice that I modified your code and added an extra drop down, and a button to trigger the event — cubanGuy, Jun 24 at 15:42
Open up the Dev Tools, and add some breakpoints (or debugger statements), see what executes. Are you sure your click event is happening? — wjervis, Jun 24 at 15:49
The only error that shows is this in the mySuccHandler function: "Uncaught TypeError: undefined is not a function.... Thanks! — cubanGuy, Jun 24 at 16:02

Aboba · Answer 2 · 2014-06-23 17:20:37Z

up vote 0 down vote

Use SPServices(JQuery addon) and bundle the getlistitems call into a function that can be activated by your event (drop down change)

answered Jun 23 at 17:20

Aboba
5104

Thanks, but I have done already, and I'm it works ok. Actually the drop downs are populated using SPServices. The issue is that I would like to be able to use the many addons DataTables offers (e.g. Export to Excel, PDF, paging, etc). – cubanGuy Jun 23 at 17:47

add a comment |

asked	1 month ago
viewed	127 times
active	1 month ago

current community

your communities

more stack exchange communities

SharePoint list as data source for jQuery DataTables plugin

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged sharepoint-foundation jquery rest datatable or ask your own question.

Linked

Hot Network Questions

current community

your communities

more stack exchange communities

SharePoint list as data source for jQuery DataTables plugin

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sharepoint-foundation jquery rest datatable or ask your own question.

Linked

Related

Hot Network Questions