Local Sorting with float values 'undefined'

[TonyLeech]

Hi, can you tell me if is normal that local header sorting for float values doesn't work if any of the cells in that column are 'undefined', null, or text?

When I change the undefined cells to 0 or simply "" (empty) then the local header sorting works ok so I think my code is working correctly.

I should say I'm still on v3.2.0 today as I haven't made time to upgrade to your new release.

What I am doing is displaying the column as currency using the render, but the currency conversion automatically makes empty cells appear like £0.00 rather than undefined.  In my case it is important that undefined is empty or rendered as 'n/a' because it represents a stocktaking value difference, so £0.00 difference would be considered very good, whereas undefined or empty clearly shows the stocktaking is incomplete.

If this is normal operation for v3.2.0 can you tell me if the new currency feature of v3.3.0 has fixed this?

Many thanks as ever for your kind help.

Tony

[paramvir]

I'm not sure what you mean by sorting doesn't work if any of the cells in that column are 'undefined', null, or text. Probably an example would help.

number field ( integer and float ) is supposed to have numbers, it may also have null, undefined values; text should better be avoided in number columns for sorting purpose.

Sorting priority of null and undefined values are considered by grid to be equal to 0, it can be customized in v3.3.0 with sortType callback.

In versions <= v3.2.0, you have to make the column dataType = 'string' before using sortType callback.

[TonyLeech]

I'm using sortModel like this...

                  var sortModel = {
                     type: 'local',
                     single: false,
                     space: true
                  }

My column is this...

                        { title: "Value £", minWidth: 70, align: "center", dataIndx: "diffval", dataType: "float", editable: false,
                           filter: { type: 'textbox', condition: "between", listeners: ['keyup'] }
                        }

...where diffval is a calculated column that I build running a function from "load" like I found in one of your other forum answers like this...

                  function computeDifferences(rowData) {
                     var diffQty;
                     if (rowData.total != null && rowData.mrpqty != null) {
                        diffQty = Number(rowData.total) - Number(rowData.mrpqty);
                     }
                     var diffVal;
                     if (diffQty != null) {
                        rowData.diffqty = diffQty;
                        if (rowData.unitcost != null && rowData.unitcost != "") {
                           diffVal = Number(diffQty * Number(rowData.unitcost));
                           rowData.diffval = diffVal;
                        } else {
                           rowData.diffval = "";
                        }
                     }
                  }
                  
                  myGrid.on("load", function () {
                     var grid = this,
                        data = grid.option('dataModel.data');
                     for (var i=0, len = data.length; i<len; i++) {
                        var rowData = data;
                        computeDifferences(rowData);
                     }
                  });

If somebody forgot to fill in 'unitcost' in my database then the calculated value difference is not valid.  In this case I would prefer to leave the calculated value difference as null.  Null is better than Zero here because a calculated value difference of zero would mean we have an exact price match rather than an unknown price.  If I remove this line...

                        } else {
                           rowData.diffval = "";

...so that the diffval remains null or undefined then the sort doesn't work clicking the column header (i.e. sorting value differences from highest to lowest).  But if I set the cell to "" as original code above then the sort works correctly.

Furthermore I want to format the column as currency, but I can't seem to test for "" and 0 (zero) as different so both end up as £0.00, then you can't distinguish them on the grid.  I can test for Null and 0 (zero) so they can be distinguished on the grid, but as explained above the sort no longer works if Null is in the cells.

If I have not confused you further with my explanations, then I would welcome your suggestions.

Best regards.

[TonyLeech]

I should add that I have now remembered to test for

                       if (ui.rowData.diffval === "") {

rather than only checking !="".  And using === does distinguish between "" and 0.  So I can at least display my grid with differences between £0.00 and "n/a".

I'm still interested in your thoughts about sorting with Nulls because that would probably still be easier to work with rather than "".

Many thanks.

[paramvir]

Got it.

null values are not sorted properly in v3.2.0 http://jsfiddle.net/z4ya6m7x/

while they work fine in v3.3.0 http://jsfiddle.net/soqrxade/

dataType: 'string' and sortType callback can be used to fix it for v3.2.0 : http://jsfiddle.net/z4ya6m7x/1/

Please let me know whether it helps to fix the issue faced by you.

[TonyLeech]

That's good.  Thank you for confirming that my null values are as expected in v3.2.0.  I will try your jsfiddles in my code, and realise that I should upgrade to v3.3.0 as soon as possible.  I will confirm the fix once all changes and upgrades are in place (however I will be out of the office for a couple of days).  I believe your support will fix it.

Your support, as ever, is excellent.

Best regards.