Wednesday, October 14, 2015

Using jQuery to sort table by a pseudo column

In many cases, the web engine that generates data tables (html) is close-sourced and you cannot sort those tables. It is not because the engine does not have a mechanism to sort data. It's just that its behaviors does not satisfy your need. For example, the PowerSchool customization engine.

In PowerSchool, I can output a table of lunch balance information of a student using this template syntax:

~[tlist;gldetail;studentid=~(curstudid);alternatecolor;sortcmd;date,>,time,>;]
  <td>~(date)</td>
  <td>~(time)</td>
  <td>~(neteffect)</td>
  <td>~(runninglunchtotalbalance.students)</td>
  <td colspan="3">~(transdescription)</td>

Did you see the > (greater than) sign? It says that the table will sort by the date and time columns in ascending order. What I want is to make it sort date and time in descending order. So I tried to replace the > with the < (less than) hoping that it would reverse the sorting order. But it turned out all the balance data was messed up. The thing is it calculates balance data on the go (up to the current transaction) and depends on date & time when you input transaction data into the system (or the order of the records that matters) and the beginning balance of the student.

Fortunately, my limited javascript knowledge is just enough for me to find a way to change the display of the html table. What I did is using jQuery to add an extra column as the first column of the table and add row numbers to each of the row. Doing that way, I had the order of the transactions generated. I then sorted the table (tablesorter plugin of jQuery) using that new column. And woala, It just works flawlessly. Here is the source code: