Visual Position of the row in table

#1

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

9 Likes

Row number detection
Consecutive Row Numbers?
Running total column based on value in previous row
Random number/character generator
Simple thing I can't work out
#2

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

I actually get this :

0 Likes

#3

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

#4

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.

0 Likes

#5

Hey @Krunal_Sheth

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

0 Likes

#6

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

0 Likes

#7

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

0 Likes

#8

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.

3 Likes

#9

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

0 Likes