How to make datatable coiumn type as numeric so that it can

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

hanzc pro premium priority asked 3 years 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 }); });

Add comment

Grzegorz Bujański staff answered 3 years ago

1 0 Best answer

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.

Add comment

Grzegorz Bujański staff answered 3 years ago

0 0 Best answer

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 pro premium priority commented 3 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 pro premium priority commented 3 years ago

Hi there

Any updates?

Grzegorz Bujański staff commented 3 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 pro premium priority commented 3 years ago

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

Grzegorz Bujański staff commented 3 years ago

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

Add comment

hanzc pro premium priority answered 3 years ago

0 0 Best answer

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 3 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

Add comment

hanzc pro premium priority answered 3 years ago