Sort / group table with external controls

There was a question in the community recently whether it was possible to control sorting and grouping of the table without having to edit table settings. This could be useful to e.g. make usage of the doc simpler for someone less familiar with Coda, and also to limit (make harder) to sort/group on columns that are not meant to be grouped/sorted.

Unfortunately the group/sort dialog doesn’t allow for formulas. So the trick is to use two dedicated columns for grouping and sorting, and then use formulas to populate those.

34 Likes

FYI another scenario when you may want to use this is if you need to color odd/even rows, and want the colors stay regardless of sorting:

And this trick may be even more useful now that we have Locking.

You can lock the section for Interact only, but still expose the way for end users to sort/group the view like this. And table settings will remain locked so that no one can mess up the doc.

1 Like

That’s cool! But how to use this I can’t see. Help me please. The formula itself will be useful for me

Hey — open this in a new tab, you’ll be able to show hidden columns that contain formulas, and also see table filters

This doc in a new tab in Play mode

1 Like

I did it before (forget to attach the screenshot). I menthioned this formula. There isn’t filter in the table )

Oh, yeah, sorry, meant sorting settings and not filter.

What about this formula? What is that you need help understanding?

How to use it in this doc? Can’t find the reference on it

I’m sorry, I don’t understand what you’re trying to accomplish and what exactly you have problems with.

This formula here calculates position of thisRow in a subset of this table. It’s not itself used for sorting — only the Sort asc and Sort desc columns take part in sorting. This column is only used to color the table in stripes.

It works like this. For each row, it finds its position in the same group (.Filter(Group = thisRow.Group)), given that all rows in this group are sorted either ascending by Sort asc column, or descending by Sort desc column. These columns are set up in such way that they are mutually exclusive: if one is populated, the other is blank. So effectively, only one sort applies and the other doesn’t reorder any rows. Since the same sorting is set up on the table itself, the order of indexes in this column will always mirror the order of rows as you see them. You change the sort — indexes will recalculate. Then you color odd and even rows by applying conditional formatting with a formula that checks whether the index is odd or even respectively.

There are references on Filter, Sort, and Find in the formula reference.

One thing to know here. In UI, sorting options work like this: the table is first sorted by the first specified column, and then for equal values, those are further sorted by the second specified column
image

In formula, it’s backwards. The second sort overrides the first one, but keeps relative positions of items that have equal values in specified column. However, in this particular example it doesn’t matter because one column will always be blank.

2 Likes

I see. You use it for conditional formatting. It’s amazing trick! Thanks for promt answer and explanation!

Here’s the way I use this now; not with canvas controls but with user-specific Flows table: