How to sort a table based off status text values?

I’ve been attempting to sort a table based off specific values and can’t seem to find the correct format to implement this using formulas.

Here’s the breakdown.

I have a column which will be titled “Status” from now on. The Status column lets you chose from the following choices:

  • Completed
  • Top Priority
  • Second Priority

etc… (I’ll stop there for the sake of simplicity).

I have this table grouped based off a different category and do not want to create another set of internal groupings. Instead, I would like to sort this table so that all Top Priority rows are shown 1st, all Second Priority rows are shown 2nd, etc…

The Problem

When I sort by “Status” either Ascending or Descending, it sorts alphabetically.

I looked around for a solution and found nothing specifically for this, but I found several other solutions which made a hidden column and checked another column’s value to determine that value for sorting.

I was hoping there was a simple away to do this and have tried using a switch function and cascding if statements, but all of it ends up extremely convoluted and doesn’t end up working.

Most of the time I have ended up with a “circularReference” error as I try to set the value of the sorting column on “thisRow”.

The Goal

The goal is to populate the Sorting column with numbers that correspond to Status. For example: If status is “First Priority” then Sorting would = 1 and when I sort by sorting, they will rise to the top.

Please help me out if you can.

Thank you -

Appendix

Added a redacted example of the table below.

Category Task Name Status Deadline Contact Description Notes Sorting
Blog Blog A First Priority May [Redacted] [Redacted] [Redacted]
Blog Blog B Second Priority Apr [Redacted] [Redacted] [Redacted]
Blog Blog C Completed May [Redacted] [Redacted] [Redacted]

Hey @Ava_Hernandez, welcome to the Community!

First, sorting by a separate column is, in fact, the simplest and most straightforward way to go about it.

If Status is a lookup column and there’s a table of statuses (three rows for each), add a numeric column on that Statuses table, assign values 1, 2, 3 to your rows to designate orders, then add a column Status.Order on your original table to pull in those 1s, 2s and 3s, and sort by that column.

If Status is a simple list of text values, then instead of going through a separate table make a column in your original table that would calculate those 1s, 2s, 3s like this:

List("Top Priority", "Second Priority", "Completed").Find(thisRow.Status)

Then again sort by this new column.

Either way you need a numeric column to sort by, and it should be you who sets the logic of which number is assigned to each row (i.e. by checking the order of this row’s status).

There’s also a trick to force arbitrary order on alphabetic sorting but you don’t need to do this here. Linking just for the record. You’d need this solution for hacking group sorting.


Second, keep in mind that any grouping you have in your table will override sorting on non-grouped columns. I.e. if you’re grouping by project and Project A has all tasks Completed, and Project B has all tasks Top Priority, this will not reorder groups to put Project B over Project A. Sorting on non-grouped columns only applies on rows within each group individually.

1 Like

Thank you!

List("Top Priority", "Second Priority", "Completed").Find(thisRow.Status)

This worked perfectly with a “Select List” column! :slight_smile: