Click here to Skip to main content
15,882,388 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

i am using sharepoint 2019 ( on prem ), with Jsom to retrieve and bind data

using bootstrap with datable to bind with html table on dropdown change 

Problem:
i have date column which contains index no 1 on datatable ,having format "DD-MM-YYYY hh:mm A".

I have applied sort but its not working on "DD-MM-YYYY hh:mm A", instead working fine on "MM-DD-YYYY hh:mm A".


Solution required: how can i sort it on desc order default on "DD-MM-YYYY hh:mm A" , then allow user to make it asc and desc on column click , as we do usual


Code Sample :

<pre>datefinal = moment(oListItem.get_item('Created')).format('DD-MM-YYYY hh:mm');
    newRow = '<tr id=tr_' + oListItem.get_item('ID') + '> <td> <a href="#"  onclick="opendoc(' + oListItem.get_item('ID') + ",'DebtDocs','tblPrivatePlace'" + ');" >  ' + oListItem.get_item('FileLeafRef') + ' </a> </td>  <td>' + datefinal + '</td>

// $("#tblPrivatePlace").append(newRow);
var table = $(tablename).DataTable();
table.row("#tr_" + oListItem.get_item('ID')).remove().draw(false);
////

var table = $(tablename).DataTable();
table.row.add($(newRow)).draw();
table.order([1, 'desc']).draw();
Please guide

Thanks


What I have tried:

applied on load not working like document.ready()
Posted
Updated 30-Jun-22 21:53pm
v2

1 solution

Check your data: if it's string based then all comparison is string as well: which means that the whole comparison is based on the first pair of different characters encountered. So 01-01-2022 is before 10-12-1944 and so on.

To get any real sort order, the data loaded into the DataTable needs to be stored as Date based information, not string - then the display format should be irrelevant.
 
Share this answer
 
Comments
qasimidl 1-Jul-22 2:43am    
give me code or any relevant example to get it done please
OriginalGriff 1-Jul-22 3:22am    
How do you expect anyone to do that when we have no idea where your data comes from, how you are loading it, or what the heck you do with it after that?
qasimidl 1-Jul-22 6:41am    
here u go
$(document).ready(function () {

if ($.fn.dataTable.isDataTable('#tblPrivatePlace')) {
$('#tblPrivatePlace').DataTable();
}
else {
$('#tblPrivatePlace').DataTable({
"order": [[1, 'desc']],//order column in descending order.
"columnDefs": [
{ "type": "date", "targets": [1] }//date column formatted like "03/23/2018 10:25:13 AM".
],
"pageLength": 25
});
}

});


var oListItems, clientContext, collInvalidDoctype;

function RetrivePpDocs() {
$('#tblPrivatePlace tbody').empty();
$('#tblPrivatePlace').DataTable().clear();

if ((parseInt($('#ddldoctypePp option:selected').text()) > 1) || $('#ddldoctypePp option:selected').text() != '---Select Document---') {
oList = oWeb.get_lists().getByTitle("DebtDocs");
var camlQueryGem = new SP.CamlQuery();
{
camlQueryGem.set_viewXml('<view><query><where><and><eq><fieldref name="\'CompanyFk\'" lookupid="TRUE"><value type="\'number\'">' + parseInt(jQuery("#ddlcompanyPp").val()) + '<and><eq><fieldref name="\'DocTypeFk\'" lookupid="TRUE">' + '<value type="\'number\'">' + parseInt(jQuery("#ddldoctypePp").val()) + '<eq><fieldref name="\'IsPublic\'/"> <value type="\'Number\'">2<orderby><fieldref name="ID" ascending="False">');
}

oListItems = oList.getItems(camlQueryGem);
clientContext.load(oListItems, 'Include(ID,DocTypeFk,DebtDocsComments, CompanyFk,Created,FileLeafRef,Status,Author)');
clientContext.executeQueryAsync(onsuccess, onfailed);
}
}
function onsuccess() {

var itemCount = oListItems.get_count();
var itemCount = oListItems.get_count();
var datefinal;
var newRow;var tablename = 0;var status = 1; var statusNA = 3; var btns = ""; //var docurl = _spPageContextInfo.webAbsoluteUrl;
var comments = "";

if (collInvalidDoctype.get_count() > 0) {
var oListItemNA = collInvalidDoctype.itemAt(0);
statusNA = oListItemNA.get_item('Status');
}
enableControlls(statusNA, "#btnnotapplicablePp", "#btnuploadPp");
statusNA = 3;
for (var i = 0; i <= itemCount - 1; i++) {
var oListItem = oListItems.itemAt(i);
statusNA = 1;
status = oListItem.get_item('Status') == 1 ? "Under Review" : oListItem.get_item('Status') == 2 ? "Accepted" : "Not Accepted";


btns = Actionbtns(oListItem.get_item('ID'));
datefinal = moment(oListItem.get_item('Created')).format('MMM-DD-YYYY hh:mm A');

newRow = ' ' + oListItem.get_item('FileLeafRef') + ' ' + datefinal + ' ' + status + ' Comments ' + oListItem.get_item('Author').$5c_1 + ' ' + btns + '';
tablename = '#tblPrivatePlace';
comments = "";

var table = $(tablename).DataTable();
table.row("#tr_" + oListItem.get_item('ID')).remove().draw(false);
var table = $(tablename).DataTable();
table.row.add($(newRow)).draw();
table.order([1, 'desc']).draw();
status = 1;
}
qasimidl 4-Jul-22 2:42am    
any suggestion please

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900