3

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!

5
  • 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, 2014 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, 2014 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, 2014 at 18:17
  • So adding your code to something like this: $("#idForDropDown").change(function(){ //Your code here }); will not work?
    – wjervis
    Jun 23, 2014 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, 2014 at 20:17

2 Answers 2

4

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);
}
5
  • 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, 2014 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, 2014 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, 2014 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, 2014 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, 2014 at 16:02
2

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

1
  • 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, 2014 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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