Articles in this section

See more

How to Custom Sort Tables

  • Updated
Follow

There are many methods for sorting tables automatically, see How to Automatically Sort Rows and Columns. However, you may have a specific, custom order of the rows/columns you want to use. One option is to use a rule that references a Combo box, Calculation listing the order, or separate table to base the row order on, see How to Select and Sort Rows and Columns Based on Another Item. In the rare case where one of the other solutions doesn't fit what you need, this article describes how to write your own custom Rule to specify the order of rows or columns in a table programmatically to go from an unsorted table...

mceclip0.png

... to one based on a specified order:

mceclip1.png

Requirements

Note: You must apply the rule directly to the input table if you see a table icon in the Data > Data Source > Data field in the object inspector of a visualization.

TableInput.png

You will also see a warning in the Rules section of the object inspector if you have applied a rule directly to a non-compatible visualization, and you will need to apply it to the input table instead. For example:

RuleCannotBeApplied.png

Method

1. Select your table.

2. From the object inspector, go to Data > Rules.

3. Click the Plus (+) button. 

4. Select New custom rule (write your own JavaScript) > Edit JavaScript.

5. Paste the below into the dialog:

form.setHeading("Custom sorting"); 
form.setSummary("Custom sorting");

var labels = ['Pepsi Max','Coke Zero','Diet Pepsi','Diet Coke','Pepsi','Coke'];
var last_index = null;

for (var i = 0; i < labels.length; i++) {
   table.moveRowAfter(table.rowIndex(labels[i]), last_index);
   last_index = table.rowIndex(labels[i]);
}
  • The first 2 lines set the name of the rule that appears in the object inspector and the dialog window.
  • We then set the order of the labels by specifying them in an array called labels. Only specify labels that match those in your table exactly. Any others not listed will be left at the end.
  • The main part of the code is kept in a for..loop to run through each label in labels.
  • Here, we are sorting the table rows so we use the table.moveRowAfter function which requires the index of the row to be moved and its new position.
  • table.rowIndex finds the label in the row and returns its position.
  • last_index gets updated with the correct position based on the new order.

6. OPTIONAL: To apply this script to columns instead, you would adjust the code within the loop as follows:

table.moveColumnAfter(table.columnIndex(labels[i]), last_index);
last_index = table.columnIndex(labels[i]);

7. Press the blue Play button > OK > OK.

Next

How to Use Rules in Displayr

How to Customize a Rule

How to Create a Custom Rule

How to Modify Table Rows and Columns Using a Rule

How to Automatically Sort Rows and Columns

Related articles