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.
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.
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
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.
