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!

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

  • 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 '14 at 17:47

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.