Topic: How to make datatable coiumn type as numeric so that it can be sorted

hanzc priority asked 9 months ago


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 }); });


Grzegorz Bujański staff answered 8 months ago


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.


hanzc priority answered 9 months ago


Sorry but am I doing anything wrong here? I used your snippet and tried and it's still not sorted. See attached picture.

enter image description here


hanzc priority answered 9 months ago


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.

enter image description here


Grzegorz Bujański staff commented 9 months 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


Grzegorz Bujański staff answered 9 months ago


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 9 months 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 9 months ago

Hi there

Any updates?


Grzegorz Bujański staff commented 9 months 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 9 months ago

Do you have any code sample on how this is done?


Grzegorz Bujański staff commented 9 months ago

I prepared an example in which such a solution was applied: https://mdbootstrap.com/snippets/standard/grzegorz-bujanski/3746810#js-tab-view



Please insert min. 20 characters.

FREE CONSULTATION

Hire our experts to build a dedicated project. We'll analyze your business requirements, for free.

Status

Answered

Specification of the issue

  • User: 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