Topic: How to make datatable coiumn type as numeric so that it can be sorted
Expected behavior Datatable columns should be sortable ascendingly and descendingly when user click on the column header. It should not only be sorted in string lexical way, but also need to be sorted numerically.
Actual behavior Only part of the table is sorted corrected on some columns only.
To be exact, the codes below have yield incorrect sortable columns for "Missing" and "Extra" columns.
Question is, how do I tell the function that my column "Missing" and "Extra" are actually numeric values and not string. Strangely, the sorting behavior is correct for columns "SOH" and "Scanned".
Resources (screenshots, code snippets etc.)
<script>
var columns = [
{ label: 'SKU', field: 'SKU', sort: true },
{ label: 'Description', field: 'Name', sort: true },
{ label: 'Color', field: 'Color', sort: true },
{ label: 'Size', field: 'Size', sort: true },
{ label: 'Dept Id', field: 'Dept_Id', sort: true },
{ label: 'Dept Name', field: 'Dept', sort: true },
{ label: 'SOH', field: 'SOH', sort: true},
{ label: 'Scanned', field: 'Scanned', sort: true },
{ label: 'Missing', field: 'Missing', sort: true},
{ label: 'Extra', field: 'Extra', sort: true}
];
const asyncTable1 = new mdb.Datatable(
document.getElementById('datatableDept'),{ columns },{ loading: false });
fetch('/Services/StocktakeWebService.asmx/GetAllVariances', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify(params)}).then(response => response.json()).then(data => {
asyncTable1.update({ rows: data.map((user) => ( {
...user,
SKU: `${ user.SKU} <a href='#' onclick=\"OnClickEAN('${user.SKU}');\">See EANs</a>`,
Description: user.Description,
Color: user.Color,
Size: user.Size,
Dept_Id: user.Dept_Id,
Dept: user.Dept,
SOH: user.SOH,
Scanned: user.Scanned,
Missing: user.Missing,
Extra: user.Extra
} ))}, { loading: false }); });
I suppose you are sorting when data that has a string in the age column is loaded. You have 2 datasets in the snippet. After starting the snippet, basicData is loaded - here the age column is strings. After pressing the update button, the data is reloaded and newData is loaded - here the age column is numbers and sorting should work fine.
Sorry but am I doing anything wrong here? I used your snippet and tried and it's still not sorted. See attached picture.
Hi
Correct me if I am wrong but I just realize that mdb datatable can only sort by string and not numbers.
For instance, looking at your snippet: https://mdbootstrap.com/snippets/standard/grzegorz-bujanski/3746810#js-tab-view
If you were to change the values of the numbers to a bigger values as show in the attached screenshot, the sorting result shows sort by string and not by numbers.
What was expected is that datatable can sort by numbers.
Grzegorz Bujański staff commented 2 years ago
From the screenshot, i can see that a string was passed, not a number. See my snippet again - I changed one value there to 431 - at the beginning its string, and it is actually sorted as a string, but after clicking update it is number, and sorted as number
Can you prepare a snipet with this problem? I checked this sort and I can see that the numeric data is sorting correctly: https://mdbootstrap.com/snippets/standard/grzegorz-bujanski/3735683#js-tab-view
hanzc priority commented 2 years ago
I guess the problem should be within the column mapping part. I've created a snippet and manually add the data via your method and it works ok.
https://mdbootstrap.com/snippets/standard/hanzc/3735730#js-tab-view
As my need is to update the table asynchronously, that's why I need the JS script to fetch the data, which returns a json data to be populated into the table. I need to tell the datatable that I am populating numbers, not string. Hence sorting should be based on numeric format and not as string sort.
Check out this article. https://stackoverflow.com/questions/20273325/datatables-sort-numbers-is-not-working-properly
hanzc priority commented 2 years ago
Hi there
Any updates?
Grzegorz Bujański staff commented 2 years ago
Our datatable does not have this option. In this case, you need to delete the current datatable instance using the dispose method and then create a new instance with the new data.
hanzc priority commented 2 years ago
Do you have any code sample on how this is done?
Grzegorz Bujański staff commented 2 years ago
I prepared an example in which such a solution was applied: https://mdbootstrap.com/snippets/standard/grzegorz-bujanski/3746810#js-tab-view
FREE CONSULTATION
Hire our experts to build a dedicated project. We'll analyze your business requirements, for free.
Answered
- ForumUser: Priority
- Premium support: Yes
- Technology: MDB Standard
- MDB Version: MDB5 3.10.2
- Device: PC
- Browser: chrome
- OS: win 10
- Provided sample code: No
- Provided link: No