Visual Position of the row in table

Hi,

here’s a tip to calculate visual position of the row in table -(if rowid, rank does not work for your case AND if you dont have ‘SORT’ applied)

as you can see in image, visual position always updates based on position of the record.

Formula here is Schedule.FormulaMap(if(CurrentValue = thisRow, 1, 0)).Find(1, 0) - it enumerates through each record in table, assigns 1 for the thisRow and then tries to find position of 1 in list.

EDIT: - some of my colleagues pointed out that the above formula relies on the FormulaMap’s order of execution - and that could change in future. suggestion was to use Schedule.Find(thisRow) - we know that it shows error in formula builder - but you can ignore the formula builder error for now - it does not affect the result. We intend to support this scenario more directly in future and when we do, this approach might break.

Hope this helps.

Thank you,
Krunal.
Engineer - Coda

10 Likes

Hey there, would you have a tip to use this in a grouped table?

I actually get this :

Hi, below are two threads which shed some light on similar cases as well:

Personally, I find myself using this formula mostly, adding the Row ID column in advance:

Filter(thisTable,Group=thisRow.Group).[Row ID].Find([Row ID])

When you use multiple filters you can get the same results for nested subgroups:

Filter(thisTable,Group=thisRow.Group).Filter(Subgroup=thisRow.Subgroup).[Row ID].Find([Row ID])

2 Likes

I’m using this formula to remove the Formula error:

thisTable.[Row ID].Find(thisRow.[Row ID])

How (I think) it works?

Row ID is unique per row.
thisTable.[Row ID] returns a list of all Row IDs
Find(thisRow.[Row ID]) then finds the position of the current Row ID within that list.

There is one caveat though - it does not seem to auto calculate.

Hey @Krunal_Sheth

Would you have a formula to calculate position after sorting by a row
.test1

@Loi_Quang_Ho could you please put an example doc and expected result? hard to tell from GIF.

@Krunal_Sheth: Sorry for make my request difficult to understand.

I use your formula to fill No. column:

What I want is that after sorting and grouping Part column, I will get No column with consecutive row Numbers as above. You please help me.

Thanks, I see what you are trying to do, What you want is find the record within the group and then use the result as a number.

Formula is: [Table 1].Filter(Group=thisRow.Group).Find(thisRow)

Here’s an example.

4 Likes

@Krunal_Sheth: Thank you so much for your strong support, It works for me.

Definitely use Rank! Works with sort on. Do not need Row ID either. Use the column that you’ve sorted with in the formula. Only caveat is: duplicate values will return duplicate visual position result. To be expected though. See the screenshot. NOW, if anyone can help me offline with the Running Bal column!

After using Rank for a while, I wanna say that the thisTable.Find(thisRow) solution I discovered recently is in fact superior and the most logical. And you can use it to find thisRow's position either in the whole table or per-group.

Hi @Justin_VanDervort,

Not sure if you got answer to your running Balance column - but here’s a post that has running balance column. Hope that helps!

Thank you.

1 Like

@Krunal_Sheth, I did get my running balance column (here). However, your approach is nice and clean. I might test that. But there are also so many other great little features wrapped up in this sample doc!! Thanks so much!