Numbers Sorting as String

[njaParticipant]

Hi,
I have a grid that is sorting some, but not all, number columns as strings. Specifically, I am working with the quantity and catalogpage columns. The quantity columns starts out set to 0 and is editable field. catalogpage page comes from the database and is a decimal field.

I have set the data type to number in the source and done formatting for the fields using cellsformat: ‘F0’, for example.

I have the reference to the sorting script… <script type=”text/javascript” src=”/jqwidgets/jqxgrid.sort.js”></script>

So after entering some quantity values and sorting descending I get:
2328 7 9780814436240
1635 6 9780814436530
2250 4 9780814436783
1507 333 9780814436882
2235 33 9780814436363
1578 3 9780814436417
2009 1 9780814436196
1897 0 9780814433638

and not
1507 333 9780814436882
2235 33 9780814436363
2328 7 9780814436240
1635 6 9780814436530
2250 4 9780814436783
1578 3 9780814436417
2009 1 9780814436196
1897 0 9780814433638

Which is what I would expect.

Hope you can spot what I am missing.

Thanks,
nja

            // ============  ORDER GRID == prepare the order grid data =============//
            var source = {
                datatype: "json",
                datafields: [
                    { name: 'id', type: 'number' },
                    { name: 'publishername', type: 'string' },
                    { name: 'isbn', type: 'string' },
                    { name: 'title', type: 'string' },
                    { name: 'subtitle', type: 'string' },
                    { name: 'author', type: 'string' },
                    { name: 'authorcity', type: 'string' },
                    { name: 'authorstate', type: 'string' },
                    { name: 'publishdate', type: 'string' },
                    { name: 'discount', type: 'number' },
                    { name: 'usprice', type: 'number' },
                    { name: 'catalogpage', type: 'number' },
                    { name: 'ordering', type: 'number' },
                    { name: 'state', type: 'number' },
                    { name: 'bindingname', type: 'string' },
                    { name: 'releasestatusname', type: 'string' },
                    { name: 'imprint', type: 'string' },
                    { name: 'quantity', type: 'number' },
                    { name: 'catalog', type: 'string' },
                    { name: 'localsalespoint', type: 'string' },
                    { name: 'catalogcopy', type: 'string' },
                    { name: 'totalretail', type: 'number' },
                ],
                url: '/components/com_sails/views/neworderform/itemslist.php?venid=' + strVendorId + '&catlist=' + strFormatedCatList
            };

 var dataAdapter = new $.jqx.dataAdapter(source);

            $("#jqxgrid").jqxGrid({
                source: dataAdapter,
                width: '99%',
                height: splitterHeight,
                theme: 'classic',
                enabletooltips: true,
                filterable: true,
                showfilterrow: true,
                sortable: true,
                editable: true,
                selectionmode: 'multiplecellsadvanced',
                showtoolbar: true,
                toolbarheight: 40,
                showstatusbar: true,
                showaggregates: true,
                columns: [
                    { text: 'id', datafield: 'id', width: 150, hidden: true, editable: false, cellclassname: cellclass, },
                    { text: 'Qty', datafield: 'quantity', width: 65, cellsalign: 'center', editable: true, filterable: false, aggregates: ['sum'], cellclassname: cellclass, },
                    { text: 'ISBN', datafield: 'isbn', width: 142, editable: false, cellclassname: cellclass, },
                    {
                        text: 'Title', datafield: 'title', width: 250, editable: false, cellclassname: cellclass,
                        cellsrenderer: function (row, column, value) {
                            var data = $('#jqxgrid').jqxGrid('getrowdata', row);
                            return '<div style="overflow: hidden; text-overflow: ellipsis; padding-bottom: 2px; margin-right: 2px; margin-left: 4px; margin-top: 4px; " title="Local Notes: ' + data.localsalespoint + '">' + value + '</div>';
                        }
                    },
                    {
                        text: 'Discount', datafield: 'discount', cellsformat: 'F4', width: 80, editable: true, filtertype: 'number', align: 'right', cellsalign: 'right', columntype: 'numberinput',
                        createeditor: function (row, cellvalue, editor) {
                            editor.jqxNumberInput({ decimalDigits: 4, digits: .01 });
                        }, cellclassname: cellclass,
                    },
                    { text: 'Price', datafield: 'usprice', cellsformat: 'F2', width: 70, cellsalign: 'right', editable: false, filtertype: 'number', cellclassname: cellclass, },
                    { text: 'Total Retail', datafield: 'totalretail', cellsformat: 'F2', width: 100, cellsalign: 'right', editable: false, filterable: false, aggregates: ['sum'], cellclassname: cellclass, },
                    { text: 'Status ', datafield: 'releasestatusname', width: 60, cellsalign: 'center', editable: false, filtertype: 'checkedlist', cellclassname: cellclass, },
                    { text: 'Binding', datafield: 'bindingname', cellsalign: 'center', width: 105, editable: false, filtertype: 'checkedlist', cellclassname: cellclass, },
                    { text: 'Catalog', datafield: 'catalog', width: 175, editable: false, filtertype: 'list', cellclassname: cellclass, },
                    { text: 'Page Nbr', datafield: 'catalogpage', width: 75, cellsalign: 'right', editable: false, filtertype: 'number', cellclassname: cellclass, },
                    { text: 'Publisher', datafield: 'publishername', width: 150, hidden: true },
                    { text: 'Subtitle', datafield: 'subtitle', width: 150, hidden: true },
                    { text: 'Author', datafield: 'author', width: 160, hidden: true },
                    { text: 'Authorcity', datafield: 'authorcity', width: 150, hidden: true },
                    { text: 'Authorstate', datafield: 'authorstate', width: 150, hidden: true },
                    { text: 'Pub Date', datafield: 'publishdate', width: 80, hidden: true },
                    { text: 'Imprint', datafield: 'imprint', width: 100, hidden: true },
                    { text: 'ordering', datafield: 'ordering', width: 150, hidden: true, hidden: true },
                    { text: 'state', datafield: 'state', width: 150, hidden: true, hidden: true },
                    { text: 'localsalespoint', datafield: 'localsalespoint', width: 150, hidden: true, },
                    { text: 'catalogcopy', datafield: 'catalogcopy', width: 150, hidden: true },
                    {
                        text: 'Detail', datafield: 'detail', columntype: 'button', width: 60, cellsrenderer: function () {
                            return "Detail";
                        }, rendered: function (element) {
                            $(element).jqxTooltip({ position: 'mouse', content: "Sorry, no details available for New Items" });

                        }, buttonclick: function (row) {
                            // open the popup window when the user clicks a button.
                            detailrow = row;
                            var dataRecord = $("#jqxgrid").jqxGrid('getrowdata', detailrow);
                            var gridWidth = $("#jqxgrid").jqxGrid('width');
                            var offset = $("#jqxgrid").offset();

                            var itemDetail = "/index.php/items/item/" + dataRecord.id;
                            var adjustments = window.open(itemDetail, '', 'width=900,height=800,scrollbars=1,resizable=1');
                            return false;

                        }
                    },
                ],
                rendertoolbar: function (toolbar) {
                    var me = this;
                    var container = $("<div style='margin: 5px;'></div>");
                    var span = $("<span style='float: left; padding-right: 40px'><h4>Order Items</h4></span>");
                    toolbar.append(container);
                    container.append(span);

                    // ================= Build Discount Combo Box ================//
                    var discountsource = ["40", "43", "44", "45", "46", "47", "50", "52", "52.5", "55", "60", "100"];
                    var discountComboBox = $("<div style='float: left; margin: 5px;'><div id='jqxDiscountComboBox'></div></div>");
                    container.append(discountComboBox);

                    var changeZeroDiscountButton = $("<div style='float: left; margin: 5px;'><span style='margin-left: 4px; position: relative; '>Set all Zero Discounts</span></div>");
                    container.append(changeZeroDiscountButton);

                    var changeAllDiscountButton = $("<div style='float: left; margin: 5px;'><span style='margin-left: 4px; position: relative; '>Set All Discounts</span></div>");
                    container.append(changeAllDiscountButton);

                    var chooselistButton = $("<div style='float: left; margin: 5px;'><span style='margin-left: 4px; position: relative; '>Choose List</span></div>");
                    container.append(chooselistButton);

                    var addButton = $("<div style='float: left; margin: 5px;'><span style='margin-left: 4px; position: relative; '>Add New Item</span></div>");
                    container.append(addButton);

                    var deleteButton = $("<div style='float: left; margin: 5px;'><span style='margin-left: 4px; position: relative; '>Delete Item</span></div>");
                    container.append(deleteButton);

                    var saveButton = $("<div style='float: left; margin: 5px;'><span style='margin-left: 4px; position: relative; '>Save</span></div>");
                    container.append(saveButton);

                    var clearButton = $("<div style='float: left; margin: 5px;'><span style='margin-left: 4px; position: relative; '>Clear Sort/Filters</span></div>");
                    container.append(clearButton);

                    toolbar.append(container);

                    // build the widgets
                    discountComboBox.jqxComboBox({ source: discountsource, width: '100', height: '20', });
                    changeZeroDiscountButton.jqxButton({ width: 155, height: 15, theme: 'darkblue' });
                    changeAllDiscountButton.jqxButton({ width: 130, height: 15, theme: 'darkblue' });
                    addButton.jqxButton({ width: 95, height: 15, theme: 'darkblue' });
                    chooselistButton.jqxButton({ width: 85, height: 15, theme: 'darkblue' });
                    deleteButton.jqxButton({ width: 85, height: 15, theme: 'darkblue' });
                    saveButton.jqxButton({ width: 60, height: 15, theme: 'darkblue' });
                    clearButton.jqxButton({ width: 122, height: 15, theme: 'darkblue' });

                    // toolbar events/actions 
                    changeZeroDiscountButton.click(function (event) {
                        var value = discountComboBox.jqxComboBox('val');

                        if ((value != null) && (value != "")) {
                            value = value / 100;

                            // get all the rows (this may have to be changed if we have paging 
                            var datainformations = $('#jqxgrid').jqxGrid('getdatainformation');
                            var rowscounts = datainformations.rowscount;

                            for (var i = 0; i < rowscounts; i++) {
                                var preSetDiscount = $("#jqxgrid").jqxGrid('getcellvalue', i, "discount");

                                if (preSetDiscount == .0000) {
                                    // @param row index.
                                    // @param column datafield.
                                    // @param cell value
                                    $("#jqxgrid").jqxGrid('setcellvalue', i, "discount", value);
                                }
                            }
                        }
                    });

                    changeAllDiscountButton.click(function (event) {
                        var value = discountComboBox.jqxComboBox('val');

                        if ((value != null) && (value != "")) {
                            value = value / 100;

                            // get all the rows (this may have to be changed if we have paging 
                            var datainformations = $('#jqxgrid').jqxGrid('getdatainformation');
                            var rowscounts = datainformations.rowscount;

                            for (var i = 0; i < rowscounts; i++) {
                                $("#jqxgrid").jqxGrid('endcelledit', i, "discount", false);
                                $("#jqxgrid").jqxGrid('setcellvalue', i, "discount", value);
                            }
                        }
                    });
                    deleteButton.click(function (event) {
                        // delete last selected row
                        if (lastselectedrow != -1) {
                            var value = $('#jqxgrid').jqxGrid('deleterow', lastselectedrow);
                            lastselectedrow = -1;

                            // set save marker
                            isDirty = true;

                        }

                    });
                    addButton.click(function (event) {
                        // get grid data information (this may have to be changed if we have paging 
                        var datainformations = $('#jqxgrid').jqxGrid('getdatainformation');
                        var rowscounts = datainformations.rowscount;

                        if (rowscounts > 0) {
                            var offset = $("#jqxgrid").offset();
                            $("#popupWindow").jqxWindow({ position: { x: parseInt(offset.left) + 60, y: parseInt(offset.top) + 60 } });

                            // set up our fields
                            $("#qtyNew").val('0');
                            $("#isbnNew").val('13 digit ISBN if available');
                            $("#titleNew").val("-");
                            $("#discountNew").val("0.0000");  //.jqxNumberInput({ decimal: '0.0000' });
                            $("#priceNew").val("0.00");   //.jqxNumberInput({ decimal: '0.00' });

                            // show the popup window.
                            $("#popupWindow").jqxWindow('open');
                        }

                    });
                    saveButton.click(function (event) {
                        if (isDirty) {
                            $('#myIsDirtyModal').modal({ keyboard: false });
                        }

                    });
                    clearButton.click(function (event) {
                            $("#jqxgrid").jqxGrid('removesort');
                            $('#jqxgrid').jqxGrid('clearfilters');
                    });

                }
            });

[DimitarParticipant]

Hi nja,

I think the issue comes from the very large numbers you have (larger than JavaScript itself supports). As an alternative, you can implement your own custom sorting solution. A sample one is shown in the demo Custom Sorting.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[njaParticipant]

Hi,
Thanks. I have had some luck with the setting the columntype to number and tweaking a custom editor. It is very neat the way the different widgets work together.

Not having as much luck with the custom sorting routine, but I am sure I will get it.

Thanks for your help,
nja

[Author]

Posts