I hope there’s something I’m missing here…
It was my understanding that “thisTable.find(thisRow)” would allow me to find the row number, given the filtering and sorting applied to the table. But the numbers aren’t calculating right. I’ve used this before, but can’t figure out how to get it working this time.
Here’s my doc: Model Info · Copy of Wall Calc
Hello @Wesley_D ,
You simply mistyped the formula because you need a capital F for .Find()
if it only would be that easy, nope non of the tools we apply to get an index will work in this case because the table is sorted and the value of
thisRow is a sorted value in either
Rank() Thee is no function to break this sorting and force 1,2,3,4 etc order.
I believe you have to hard-code the same filters that the table is using into your formula.
nope, this is a sorting not a Filter. with a Filter you can create something like a group index, the numbering starts with one again once you relate to an item of an new group. That is not the case here.
@Nick_HE is right. You have to apply the same filtering and sorting to
thisTable before you can
Find() on those rows you’re seeing.
You can use the view’s name in your formula, e.g.
[My tasks].Sort(...).Find(thisRow) to work with a pre-filtered dataset. But you’ll still have to sort because without sorting, the order of rows will correspond to how the rows were added and/or dragged around (sometimes this breaks when you add rows through a detail view though)
thisTable always refers to the whole dataset. Sorting is always at UI level only and not the dataset level. Filtering is at UI level on base tables and at dataset levels on views (i.e. a view actually stores the IDs of rows included in that view).
Capital or not capital F doesn’t make a difference. Function names are case insensitive.
Thank you for this clarification because in some languages the case is to be taken into account.
hi @Paul_Danyliuk ,
would love to see your solution to get a 1, 2, 3, 4 etc order, the table has two sortings active .
I copied the doc and opened it.
The pattern is to write a formula that first sorts the rows of thisTable using the same sort ‘key’ as the view, and then Find() the sort key in that list. The sort key is basically a concatenation of all the individual sort columns.
I am using the ‘+’ operator to concatenate the cells for brevity, but you might need to use the longer form; Concatenate(Brand, Model) if Brand was a number (since then it would try to do addition).
Can’t open the doc. There’s two ways:
Sort inside out with two formulas:
.Sort(true, Model).Sort(true.Brand). If sorting is still stable, this should work. Not super efficient though because it unnecessarily sorts all rows by model before resorting them again by brand. Advantage: no need for extra columns.
Make a separate column that’s a
List(Brand, Model) and sort on that column. Coda sorts arrays properly, first by the first element and then by the following ones.
@Xyzor_Max’s Concatenate/+ solution may work in most cases but not all.
Very good advice sir Paul:
good learning for all readers, first sort the table on all sorting before applying the
Yes!!! This is exactly what I was looking for. Thanks!
I’d suggest the 2nd solution though, especially on larger tables. Performance of sorting should be significantly better with lists rather than the two calls to .Sort()
(never benchmarked it though, I’m just theorizing)