Drag and drop sorting of table rows in priority order

A common design pattern is making choices from a list, and then adding those choices to a table of results.

Often the table will use some sort of Javascript plugin — Footables, Datatables, etc. — to provide sorting and searching functionality.

But what if you want the results in a table, and the only extra you need is a quick way to sort the table into a prioritized list? And you want to do it using a touch-friendly drag/drop interface?

Here’s a simple way to do that, using jQuery and jQuery UI. Check out the demo to see it in action.

HTML

First, we build a table with some data in it:

<table class="table" id="diagnosis_list">
    <thead>
        <tr><th>Priority</th><th>Name</th><th>Favorite fruit</th><th>Vegetarian?</th><th>&nbsp;</th></tr>
    </thead>
    <tbody>
        <tr><td class='priority'>1</td><td>George Washington</td><td>Apple</td><td>N</td><td><a class='btn btn-delete btn-danger'>Delete</a></td></tr>
        <tr><td class='priority'>2</td><td>John Adams</td><td>Pear</td><td>Y</td><td><a class='btn btn-delete btn-danger'>Delete</a></td></tr>
        <tr><td class='priority'>3</td><td>Thomas Jefferson</td><td>Banana</td><td>Y</td><td><a class='btn btn-delete btn-danger'>Delete</a></td></tr>
        <tr><td class='priority'>4</td><td>Ben Franklin</td><td>Kumquat</td><td>N</td><td><a class='btn btn-delete btn-danger'>Delete</a></td></tr>
        <tr><td class='priority'>5</td><td>Alexander Hamilton</td><td>Red grapes</td><td>N</td><td><a class='btn btn-delete btn-danger'>Delete</a></td></tr>
    </tbody>
</table>

It’s just a normal table, with an ID and explicit <thead> and <tbody> sections. This lets us make the <tbody> sortable, while leaving the <thead> alone. Also note that the first <td> in each row has the class “priority”. This is where our javascript will write each row’s priority number.

CSS

The demo includes basic Bootstrap styles for overall look and feel. So we only require a little bit of custom CSS to make the interactions more enjoyable.

.ui-sortable tr {
    cursor:pointer;
}    
.ui-sortable tr:hover {
    background:rgba(244,251,17,0.45);
}

When hovering over a draggable table row, this CSS displays the hand cursor and turns the background of the row a transparent yellow.

Javascript

To get the sortable behavior, we call in the jQuery UI Javascript file. Note that we don’t call in the jQuery UI CSS: we want the functionality, not the styling.

Then we add the following code:

$(document).ready(function() {
    //Helper function to keep table row from collapsing when being sorted
    var fixHelperModified = function(e, tr) {
        var $originals = tr.children();
        var $helper = tr.clone();
        $helper.children().each(function(index)
        {
          $(this).width($originals.eq(index).width())
        });
        return $helper;
    };

    //Make diagnosis table sortable
    $("#diagnosis_list tbody").sortable({
        helper: fixHelperModified,
        stop: function(event,ui) {renumber_table('#diagnosis_list')}
    }).disableSelection();

    //Delete button in table rows
    $('table').on('click','.btn-delete',function() {
        tableID = '#' + $(this).closest('table').attr('id');
        r = confirm('Delete this item?');
        if(r) {
            $(this).closest('tr').remove();
            renumber_table(tableID);
            }
    });
});

//Renumber table rows
function renumber_table(tableID) {
    $(tableID + " tr").each(function() {
        count = $(this).parent().children().index($(this)) + 1;
        $(this).find('.priority').html(count);
    });
}

This is the core of the demo, so let’s go through it in detail.

    //Make diagnosis table sortable
    $("#diagnosis_list tbody").sortable({
        helper: fixHelperModified,
        stop: function(event,ui) {renumber_table('#diagnosis_list')}
    }).disableSelection();

This calls the jQuery UI “sortable” method on the <tbody> element of the table. It includes a helper function called fixHelperModified. Once the sort is finished, it calls the renumber_table() function.

What is the point of fixHelperModified? I’m so glad you asked:

    //Helper function to keep table row from collapsing when being sorted
    var fixHelperModified = function(e, tr) {
        var $originals = tr.children();
        var $helper = tr.clone();
        $helper.children().each(function(index)
        {
          $(this).width($originals.eq(index).width())
        });
        return $helper;
    };

The best way to see what this function does is to execute the sortable call without it. By default, any table row you drag will collapse down to minimum size, leaving you with a strange looking table:

bad_sortable_table

The helper function maintains the full width of the row, avoiding a weird visual experience while dragging. Thanks to Brian Grinstead for making this JSFiddle that illustrates the problem and a couple of different solutions, as originally raised on Stack Overflow.

When dragging is done, it triggers the renumber_table() function:

//Renumber table rows
function renumber_table(tableID) {
    $(tableID + " tr").each(function() {
        count = $(this).parent().children().index($(this)) + 1;
        $(this).find('.priority').html(count);
    });
}

This puts all the table rows into a jQuery object, then uses jQuery’s .each() method to loop through each row and renumber them based on their position in the table. “count” is the index of each row; the code adds 1 to this because indexes start at 0, and we want to start at one. Then it replaces the content of the “priority” <td> with count.

Bonus: Delete a table row

You may have noticed I skipped a code block:

    //Delete button in table rows
    $('table').on('click','.btn-delete',function() {
        tableID = '#' + $(this).closest('table').attr('id');
        r = confirm('Delete this item?');
        if(r) {
            $(this).closest('tr').remove();
            renumber_table(tableID);
            }
    });

As the note says, this code is triggered when a row’s “Delete” button is clicked. It does several things:

  1. Gets the ID of the table;
  2. Shows a confirmation dialog asking if you really want to delete the row;
  3. If you confirm the deletion, it finds the <tr> tag for the row the button is in, deletes the row, and then calls the renumber_table() function to, uh, renumber the table. This last is why it grabbed the ID of the table in Step 1; it needs to pass the ID to the renumbering function.

Clickable labels that highlight when selected

I’ve been designing a touch-friendly form for one of our clients. One thing we wanted to do was make checkboxes and radio buttons easier to tap, and clearly show what has been selected. A little bit of CSS and JavaScript did the trick. Check out the demo to see it in action.

This example uses Bootstrap classes, so you can drop these snippets straight into your own Bootstrap-based site and it should work just fine.

HTML

    <form id="testForm" class="form-horizontal" method="post">
    <div class="form-group">
      <div class="col-md-4">
        <label class="radio" for="option-0">
          <input name="options" id="option-0" value="1" type="radio">
          Option #1
        </label>
        <label class="radio" for="option-1">
          <input name="options" id="option-1" value="2" type="radio">
          Option #2
        </label>
        <label class="radio" for="option-2">
          <input name="options" id="option-2" value="3" type="radio">
          Option #3
        </label>
      </div>
    </div>
    <div class="form-group">
      <div class="col-md-4">
        <label class="checkbox" for="cb-option-0">
          <input name="checkboxes" id="cb-option-0" value="1" type="checkbox">
          Option #1
        </label>
        <label class="checkbox" for="cb-option-1">
          <input name="checkboxes" id="cb-option-1" value="2" type="checkbox">
          Option #2
        </label>
        <label class="checkbox" for="cb-option-2">
          <input name="checkboxes" id="cb-option-2" value="3" type="checkbox">
          Option #3
        </label>
      </div>
    </div>
    </form>

Pretty straightforward. Just standard Bootstrap form-groups, with the radios and checkboxes wrapped in label tags with appropriate “checkbox” and “radio” classes.

CSS

label.radio-inline,
label.checkbox-inline,
label.radio,
label.checkbox {
    margin-right:2%;
    cursor:pointer;
    font-weight:400;
    padding:10px 10px 10px 30px;
    background-color:#dcdfd4;
    margin-bottom:10px!important
}
label.radio-inline.checked,
label.checkbox-inline.checked,
label.radio.checked,
label.checkbox.checked {
    background-color:#266c8e;
    color:#fff!important;
    text-shadow:#000 1px 1px 2px!important
}

Again, fairly straightforward: We give all the labels a gray background and some padding. If we add the class “checked”, we use a blue background with white text.

JAVASCRIPT

//When checkboxes/radios checked/unchecked, toggle background color
$('.form-group').on('click','input[type=radio]',function() {
    $(this).closest('.form-group').find('.radio-inline, .radio').removeClass('checked');
    $(this).closest('.radio-inline, .radio').addClass('checked');
});
$('.form-group').on('click','input[type=checkbox]',function() {
    $(this).closest('.checkbox-inline, .checkbox').toggleClass('checked');
});

The Javascript ties it all together.

When a radio button is clicked, it finds the parent form-group and then from there looks for any radio buttons. It removes any existing “checked” classes from the radio buttons, then adds the “checked” class to the label for the radio button that was just clicked.

When a checkbox is clicked, the code is simpler: it just finds the label for that checkbox and toggles the “checked” class on it.

Database bloat, Asyncoperation (System Job) Maintenance, and Dynamics CRM Online

We’ve been running Dynamics CRM Online for several years now and our database has continued to grow to the point we were having to add storage again. We requested a list of the top 10 largest tables in our database from Microsoft support and found to our surprise that the Asyncoperation Base table was triple the size of any other table in our database. It was a surprise to us because there is a standard bulk delete job in all CRM Online organizations called “Delete completed system jobs” that sounds like it should have been maintaining the size of this table and only keeping things around that completed in the last month.

For years, we’ve known that maintenance of this table was important on premise-based installations, and have referred back to this KB Article many times to help customers perform this maintenance in a supported way directly in SQL.  We also knew we wouldn’t be given access to SQL in the Online environment, so wanted to replicate the functionality of that KB in the Online environment.  When you look closely, it’s taking action on specifically the records captured by this SQL Query:

Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase where
OperationType in (1, 9, 12, 25, 27, 10)
AND StateCode = 3
AND StatusCode in (30, 32)

If you convert that into a Bulk Record Deletion job in CRM Online, and add the clause to only delete those older than 1 month, it translates into this query:

Async Query

The system job types, when expanded, looks like this:

Job Types Expanded

You may also want to remove the Status Reason clause limiting it to only Succeeded and Cancelled jobs.  The only other ones left are Failed, and if they failed over a month ago, you may be OK with deleting the job recording that.  It’s up to you.

In our case, running this job deleted over 2 million records and freed up over 10GB of storage in our database.  The system feels snappier too.

I hope this tip works for you!

Bart