Table not sorting properly in specific column (MVC)

Question

Quick problem:

I've used some javascript i found to sort all my tables by clicking their header, and this works absolutely fine in most cases. Strings, ints, etc all get sorted properly all across the page when tagged appropriately.

            function sortTable(n) {

        var table, rows, switching, i, x, y, shouldSwitch, dir, switchcount = 0; //declares necessary variables

        table = document.getElementById("toSortTable"); 

        switching = true; 

        dir = "asc"; 

        while (switching) { 

            switching = false; 
            rows = table.getElementsByTagName("tr"); 

            for (i = 1; i < (rows.length - 1) ; i++) { 

                shouldSwitch = false; 

                x = rows[i].getElementsByTagName("td")[n]; 
                y = rows[i + 1].getElementsByTagName("td")[n]; 

                if (dir == "asc") { 

                    if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) { //compares the 2 values
                        shouldSwitch = true; /
                        break;
                    }

                } else if (dir == "desc") {
                    if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) { 
                        shouldSwitch = true; 
                        break;
                    }
                }
            }
            if (shouldSwitch) { 
                rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
                switching = true;
                switchcount++; 
            } else {
                if (switchcount == 0 && dir == "asc") {
                    dir = "desc";
                    switching = true;
                }
            }
        }
    }

As said, this JS seems to work on most pages i assign it, but in one table it gives me some issues. On one of my tables the sorting simply won't provide the right order.

https://i.stack.imgur.com/iPY5H.jpg (don't know how to embed this)

The table has been clicked (i.e. sorted) in both images by total billing cost. I read that botched sorting can occur when one of the values contains a whitespace, so the field gets recognized as a string instead of dec/int, but this was not the case in the DB, as well as the weird entry showing up in the middle and not before / after the rest of the ints/decs.

The only thing i can imagine being off is my controller that populates my VM:

            public ActionResult Index()
    {
        List<InvoiceVM> listOfInvoices = new List<InvoiceVM>();
        var iNVOICES = db.INVOICES.Include(i => i.INVOICES_TO_CREDIT);



        foreach (var entry in iNVOICES)
        {
            bool check = false;
            foreach (var checkDupe in listOfInvoices)
            {
                if (checkDupe.invoiceNo == entry.INV_NO)
                {
                    check = true;
                }
            }
            if (check == true)
            {
                check = false;
            }
            else
            {
                InvoiceVM toAdd = new InvoiceVM();
                toAdd.account = entry.ACCOUNT;
                toAdd.subDate = entry.SUB_DATE;
                toAdd.cc = entry.CC;
                toAdd.reference = entry.SOLUTION_OWNER;
                toAdd.invoiceNo = entry.INV_NO;
                toAdd.project = entry.PROJECT_ID;
                toAdd.ikeaProject = entry.SLTN_NAME;
                toAdd.invoiceNo = entry.INV_NO;
                toAdd.autoNo = Convert.ToInt32(entry.AUTO_ID);

                foreach (var invoice in iNVOICES)
                {
                    if (invoice.INV_NO == toAdd.invoiceNo)
                    {
                        toAdd.totalCost += Convert.ToInt32(invoice.EMP_TOTAL_COST);
                    }
                }

                listOfInvoices.Add(toAdd);

            }

        }
        return View(listOfInvoices);

    }

(I know this could probably be solved via some super simple GroupBy + Sum making this code look super trash, happy to take that suggestion if anyone wants to pitch it in).

Anyway, this populates properly, as the values displayed in the pictures above are the proper sums of values in the actual Invoice table. Also, the individual values on the "Total Cost" are higher for the 42k, than the 11k (in case the sorting used an original value instead of the summed up value). Both of these values have been combined in the controller by other values, but this doesn't feel relevant once the values have been passed to a VM.

I'd look for more answers to this, and I'm sure the mistake is somewhere in my controller, but I just have no idea what to look for. The sorting works perfectly in all other tables, and even works perfectly fine in that table on the same page, for all columns but the Total Cost one. Any help would be appreciated.

Thanks a lot!

Answer 1

Are you absolutely certain this script sorts the numeric values correctly in other tables? You did mention that as long as everything is "tagged" appropriately, but what does this mean? Are you somehow able to specify that specific table data cells are numbers only?

As for something being wrong in controller, I doubt that is the case here. The controller does feed the view with the data but the sorting is entirely down to the javascript.

It is of course possible to do the sorting in controller, you can view an example here.

Down to business now though, here your script retrieves the values from specified table cells:

x = rows[i].getElementsByTagName("td")[n]; 
y = rows[i + 1].getElementsByTagName("td")[n]; 

and here is one of the comparisons:

x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()

I don't think retrieving the innerHTML and changing it to lower case is able to tell a difference between a number and a string. I could be wrong, but I did do some simple testing and it coped well with alphabetical sorting. However with numbers it considered 1002 to be smaller than 14, for example.

You could try adding some more conditional checking if the x and y variables are numbers or strings then save them to new numeric variables and then do the sorting.

Or you can try alternative sorting solution: DataTables. It is quite nifty a package, although jQuery based. (not in any way affiliated with them, I just it myself).