Filter inputbox disappered after using "columnDefs" in datatable

KsunChan

Hi all,
I am new to bootstrap & datatable, today i got some issue that need your hand very much.

I am working on a project, upgrade it from VS2013 to VS2019.

During the upgrade, I used bootstrap 5 to make the project looks better and responsive. But I encounter an issue while I turn one of my asp gridview(ID = "GridView1") into datatable.

The dataset of the gridview contain 24 data columns; But user can select which columns they wanna to show in the result panel.

In the gridview, column 17 and 18 is a datetime with format 'dd MMM YYYY hh:mm:ss", it is expect that user can be sorting asc or desc by clicking the up or down arrow with these columns.

<asp:BoundField DataField="opendate" SortExpression="opendate" HeaderText="Call Date" DataFormatString="{0:dd/MM/yyyy hh:mm:ss tt}"></asp:BoundField>
<asp:BoundField DataField="closedate" SortExpression="closedate" HeaderText="Close Date" DataFormatString="{0:dd/MM/yyyy hh:mm:ss tt}"></asp:BoundField>

But the first issue I counter is that, the sorting of these two columns didnt in date format, but in string format(e.g. it sort "01 Jun 2023" as first record, then "02 Nov 2019" as second record while asc order declared).

I tried few ways, such as setting DataFormatString="{0:dd/MM/yyyy hh:mm:ss tt}" in these two BoundField in the gridview; Convert the output as datetime in SQL before return to the dateset...etc. But still not working.

Until I added "columnDefs", and dedicated that 2 columns are date as below:

$('#<%= GridView1.ClientID %>').dataTable({
"scrollX": true,
"aLengthMenu": [[10, 50, 75, -1], [10, 50, 75, "All"]],
"iDisplayLength": 10,
"order": [[1, "desc"]],
"columnDefs": [
{ "type": "date", "targets": [17, 18] }
],
"bInfo": null,
stateSave: false,
stateSaveCallback: function (settings, data) {
localStorage.setItem('DataTables_' + settings.sInstance, JSON.stringify(data));
},
stateLoadCallback: function (settings) {
return JSON.parse(localStorage.getItem('DataTables_' + settings.sInstance));
}
});
});

Then, the sorting of these 2 column work properly as expected.

But then, I encounter another issue, I found that the "Filter" inputbox which provide by datatable disappered in some case; After investigation, I found that if I select number of display columns < 18, the "Filter" inputbox will be disapper. only when display columns >= 18 the "Filter" inputbox will appear again.

I also tried to remove the "columnDefs" tag from the datatable config above, the "Filter" inputbox will appear but then I will not be able to sort the date properly again.

May I ask how can I keep the date sorting correctly with my "Filter" inputbox showing no matter how many display column I select please?

Please give a hand, many thanks.

KsunChan

Dear All,

I knew I should provide a test case for help; But I feel so sorry that I unable to create a workable demo via https://live.datatables.net/ or https://jsfiddle.net/.

I can reproduce the date sorting problem below:
https://live.datatables.net/suzepata/1/

But I cannot reproduce the Filter input disappear issue, as when I Post the following set:

$('#example').dataTable({
"scrollX": true,
"aLengthMenu": [[10, 50, 75, -1], [10, 50, 75, "All"]],
"iDisplayLength": 10,
"order": [[1, "desc"]],
"columnDefs": [
{ "type": "date", "targets": [17, 18] }
],
"bInfo": null,
stateSave: false,
stateSaveCallback: function (settings, data) {
localStorage.setItem('DataTables_' + settings.sInstance, JSON.stringify(data));
},
stateLoadCallback: function (settings) {
return JSON.parse(localStorage.getItem('DataTables_' + settings.sInstance));
}
});
});

in the javascript tag, it prompted error...Thus I unable to provide the test case of it properly....

colin

The date example appears to be working for me, it's ordering correctly. If there is an issue that I'm missing, it would be worth looking at the examples on this page: https://datatables.net/examples/datetime/index.html . There's are several examples demonstrating how to order with different date formats.

We would really need to see the other issue to be able to understand what's happening. Are you able to link to a page that demonstrates the problem?

Colin

KsunChan

Hi Colin, thanks for your reply.

But may I ask whats the output you found from my demo link "https://live.datatables.net/suzepata/1/" please?

Because when I try to sort by column "Start Date" the datatable return data sequence as below:

Since I sort the "Start Date" in ascending order, shouldn't the result sequence should be like the following demo?:
https://live.datatables.net/suzepata/4

I also read the page you suggest and tried example there, I can reproduce and resolve the sorting via define:
"columnDefs": [
{ "type": "date", "targets": [4] }

Which is I used in "https://live.datatables.net/suzepata/4". At the beginning, I also use "columnDefs" and having the following result in my project:

With "columnDefs", "columnDefs": [{ "type": "date", "targets": [17, 18] }], select all columns to output:

This looks everything are correct, column "Call Date" sort properly, and "Filter" textbox is available.

But due to user be able to select which column to display in the result, I found that when selected column less than 18...

With "columnDefs", "columnDefs": [{ "type": "date", "targets": [17, 18] }], select one column to output:

The "Filter" textbox will disappeared.

I also keep try working on it and google more example to follow, such as include moment.js and declare fn.moment but whatever I did, once I remove the "columnDefs", the sorting of start date will not correct (become like picture 1, it sort via the 01,02,03 as a string, instead of a full date).

Then I found another example "https://gist.github.com/Kriztinemendoza/aa84a3dc8f579a380ce210c0ec08e034".

This give me the correct sort order in those date field without me to use "columnDefs"; But I encounter another issue in the paging footage....

Original:

After follow example "https://gist.github.com/Kriztinemendoza/aa84a3dc8f579a380ce210c0ec08e034":

Then I keep looking around where I be able to tune the paging footage back to orginal.....

I feel so sorry if I case any inconvenience to you or if my question is too silly, because I am new to bootstrap and datatable, I am still learning how to work on it properly and in a right way....

KsunChan

Please give a hand; Many thanks.

kthorngren

You shouldn't need to use columns.type to force the date setting. Datatables will perform type detection. The preferred method is to use the DataTable.datetime() function as shown in the ordering examples Colin linked to.

I took the moment.js example and updated your test case with the moment.js CDN and added the format string using DataTable.datetime().
https://live.datatables.net/suzepata/5/edit

There is a Luxon example if you prefer that library.

But due to user be able to select which column to display in the result, I found that when selected column less than 18...

How and when are you doing this? I suspect this happens before Datatables initialization so you are creating a table with fewer columns and when Datatables tries using "targets": [17, 18] you are getting Javascript errors causing the script to stop. This results in table formatting and usage issues. Look for errors in the browser's console.

One option is to use a classname instead of column index. See the columnDefs.targets docs for details. This way if there are no columns with the classname you won't get errors. However if you get the above method working you won't need to set the columns.type. If you still need help with this then please post a test case showing the issue so we can help debug.

Kevin

KsunChan

Hi Kthorngren, thanks for your reply.

I will have more try again on Colin and your advise; Many thanks.