How to sort a table based off status text values?

1

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]
2

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.

2 Likes
3

Thank you!

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

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

4

Hi from France,

I’m a regular user of Airtable and I’m beginning to recreate an Airtable base in Coda as an exercise/test before eventually switch tool.

So here is my take on this question, from an Airtable user point of view :wink:

The sorting rows by a “Select List” column (be it in a custom order or alphabetically) is handled quite elegantly by Airtable as ther is no need for hacks like special “order” columns, etc.

  • In the settings for the column/field you create a custom order by default.
  • Then, if you want, there is a button to alphabetize the custom list (albeit only in descending order).
  • The order (custom or alphabetized) will be used for sorting and grouping rows of a table.

Not a deal breaker for Coda over Airtable for now, but I’m surprised Coda didn’t implement that feature this way. We end up dealing with enough tables and fields/columns in these tools: every features that help us reduce their number is welcome :wink:

3 Likes
5

Agreed. Lark Suite, Seatable, Airtable, Clickup all allow you to sort by the order in which you set the fields. It feels unfinished to allow reordering of the list options, and not have the ability to sort on that list order.

1 Like
6

Hi Kevin,

Coda provides three different ways to order a column in a table.

Sort Order - The first is to sort in an alphanumeric sequence, as Ava did in her document.
Table Order - The second is to use the table sequence. In this instance, the table entries are presented in the positions into which they were inserted in the table.
Entry Order - The third is the time sequence in which entries were added to the table. You can access this sequence either through the “rowID” or the “created on” property in the table.
Custom Order - a sequence for a lookup column based on a complete different column. Using this functionality, you can create lookups in which the sequence in the lookup drop-down can be different for different tables from which the lookup is done.

Regards
Rambling Pete

7

Hi Piet,

I don’t understand the difference between the second and third way. Table entries are presented in the positions into which they were inserted in the table is also the time in which they were entered in the table?

I used a rudimentary workaround of putting a number in front of my “Top priority” label so it goes ahead of my “High priority” label.
With Lark Suite, if I change the order of the labels, in the same way I can change the order of the labels in Coda, Lark Suite lets me order the labels by the order in which I placed them. It’s a little thing that saves me a lot of time and makes the table look more business-user friendly. It would be great if Coda added the same functionality.

8

Hi Kevin

Have a look at the explanation here: https://coda.io/d/_dNmJCR6fw97/Table-Order-vs-Entry-Order-vs-Sorted-Order_suM8y

What you refer to below is the second option I mentioned above. (I have renamed it to Table Order to be more descriptive. ) The third option, Entry order, is the time stamped sequence in which rows were entered. Which is different than Table order.

To be complete, I have also added Custom Order.

R
p

1 Like
9

Ahh I see. So I have to create a separate table and reference that column to get the order I want. Makes sense. Thanks Piet!

10

Hi,

Nope, that just sorts the lookup drop down. This still doesn’t solve my use-case. Back to the janky adding numbers in front of the text. :expressionless:

11

Have a look again at the Table Order concept. It does EXACTLY what you say you do in Lark. There is no need to add numbers in front of your entries.

12

This is how I entered the priorities:

image

This is how it sorts if I do alphabetical:

image
image633×589 28.7 KB

I want Top to be above High, to be above Medium, to be above Low. Can’t sort this even though it’s in the order in which I’ve dictated the list to be.

So I convert the List into a table:

image
image437×897 33.2 KB

And then I click on Lookup>Sort by Table:

image
image671×785 59.9 KB

And it still doesn’t sort my top priority above my high priority above my medium above my low.

1 Like
13

Ah, now it is clear.

P

1 Like
15

Dear @Kevin_Morris,

Please check out this solution. Feel free to copy to be able to understand the formulas and approach.