Below is a working datatable for my SPO list called Companies. I am having 2 issues that I can't seem to get right. I've implemented what I could learn from the DT website and board, but no luck. Below is a simple datatable with one calculated column (text) called calcCompanyRecent. Full script is below. Thanks in advance!
Request #1
I want the DT Order list items by most recently Modified (descending order). The Modified column is NOT included in this datatable. The list keeps defaulting to col1 (asc) for the sort order
Request #2
There are some duplicates in this list, but I want all of the duplicates removed. I have attempted the solutions on the DT site, but nothing seems to work.
<!—Data tables CSS -->
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.11/css/jquery.dataTables.min.css"/>
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.1.2/css/buttons.dataTables.min.css"/>
<link rel="stylesheet" href="https://cdn.datatables.net/select/1.1.2/css/select.dataTables.min.css"/>
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.0.2/css/responsive.dataTables.min.css"/>
<!—Data tables JS -->
<script src="https://cdn.datatables.net/1.10.11/js/jquery.dataTables.min.js"></script>
<script src="../../SiteAssets/solutions/datatable-editor/dataTables.altEditor.free.js"></script>
<script src="https://cdn.datatables.net/buttons/1.1.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/select/1.1.2/js/dataTables.select.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.0.2/js/dataTables.responsive.min.js"></script>
<script type="text/javascript">
// Datatable Script
$(document).ready(function() {
loadMyItems();
});
function loadMyItems() {
var siteUrl = _spPageContextInfo.siteAbsoluteUrl;
var oDataUrl = siteUrl + "/_api/web/lists/getbytitle('Companies')/items?$select=calcCompanyRecent&$orderby=Modified desc&$top=20";
$.ajax({
url: oDataUrl,
type: "GET",
dataType: "json",
headers: {
"accept": "application/json;odata=verbose"
},
success: mySuccHandler,
error: myErrHandler
});
}
function mySuccHandler(data) {
try {
var dataTableExample = $('#table_id').DataTable();
if (dataTableExample != 'undefined') {
dataTableExample.destroy();
}
dataTableExample = $('#table_id').DataTable({
scrollY: 200,
responsive: true,
"aaData": data.d.results,
"iDisplayLength": 5,
"aoColumns": [
{
"mData": "calcCompanyRecent"
}]
});
}
catch (e) {
alert(e.message);
}
}
function myErrHandler(data, errCode, errMessage) {
alert("Error: " + errMessage);
}
// Invoke CompanyList NewForm Modal
function openCoListModal() {
SP.UI.ModalDialog.showModalDialog({
width: 800,
height: 500,
url: "/Lists/companylist/AddNew.aspx"
});
}
</script>
<script type="text/javascript">
// Using the DialogOptions class
function OpenDialog(strPageURL) {
var dialogOptions = SP.UI.$create_DialogOptions();
dialogOptions.url = strPageURL; // URL of the Page
// Width of the Dialog
dialogOptions.width = 800;
// Height of the Dialog
dialogOptions.height = 530;
// Function to capture dialog closed event
//dialogReturnValueCallback - A function pointer that specifies the return callback function. The function takes two parameters, a dialogResult of type SP.UI.DialogResult Enumeration and a returnValue of type object that contains any data returned by the dialog.
dialogOptions.dialogReturnValueCallback = Function.createDelegate(null, CloseCallback);
// Open the Dialog
SP.UI.ModalDialog.showModalDialog(dialogOptions);
return false;
}
// Dialog close event capture function
function CloseCallback(strReturnValue, target) {
location.reload(true);
}
var columnDefs = [{
title: "Title"
}, {
title: "calcCompanyRecent"
}];
var myTable;
myTable = $('#table_id').DataTable({
sPaginationType: 'none',
data: 'data.d.results',
dom: 'Bfrtip', // Needs button container
select: 'single',
responsive: 'true',
altEditor: 'true', // Enable altEditor
buttons: [{
text: 'Add',
name: 'add' // do not change name
},
{
extend: 'selected', // Bind to Selected row
text: 'Edit',
name: 'edit' // do not change name
},
{
extend: 'selected', // Bind to Selected row
text: 'Delete',
name: 'delete' // do not change name
}]
});
});
</script>