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

25 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])

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

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

5 Likes

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!

Can I get some help with this please?

None of these are working. Even in your embedded example, it’s not ordered numerically.

I want it to number rows by visual order, regardless of grouping or sorting; instead, I’m just getting a ‘visual’ ordering as if it was ungrouped; this is not what I want, it’s random and wrong and I don’t want to have to manually order every item in the list for it to work correctly. That’s the whole point of grouping and sorting.

I can’t autosort without grouping, either, because the groups are in a custom order, and are not alphabetic. So I don’t know how to do this except numbering them manually, which I do not want to do.

And on top of that, it wouldn’t fix the issue where it will auto order on a referencing table. (Table A is messed up when ungrouped and unsorted; when table B references that table, and I tell it to sort by how it’s ordered, it’s doing it by the messed up sorting which is stupid and unintuitive. It should read how it’s actually sorted/grouped, right?)

So I have [table 1] which is grouped by [Categories] B, A, C, and should have numbered [items] 1, 2, 3, 4, 5, 6… Their row ID’s aren’t ordered correctly, and I expect them to be moved/changed/added to/etc. Since I added half of them while grouped, their actual ordering without groups is something like 7, 8, 2… so their calculated ‘visual order’ ends up like that, not numerical based on how the rows are grouped and sorted.

Table 2 references [Items], and groups/categorizes other things based on it, and because [table 1]'s ‘visual’ order is wrong without grouping, it gets the [items] order wrong when grouping by them.

…it’s also annoying that I can’t change the select items’ names without having to reselect them. Is there a way to do this? I’m looking for Airtable’s functionality here. I’d rather not have 3 tables for each type of relation.

@Jason_Nordin - apologies you are finding it annoying and product doesnt work as you expect.

I would be happy to help if you could please put your example document with expected result and I will take a look right away.

we are also available via in product chat and by email at support@coda.io if thats easier.

I ended up doing it manually, so it wouldn’t help much. Do you understand what I’m looking for?

Unique Autonumbering of rows per table, by how they are visually ordered, despite being grouped or sorted.

The other issues I can put in a support chat.

1 Like

Hoping I can re-activate this thread and get an answer to what @Jason_Nordin was asking for.

Here’s a link to my test doc: https://coda.io/d/Grouped-Find-Problem_d_L2LXl1o9s

What I want is to create a formula column that always displays the row’s current visual position within a table, regardless of grouping.

https://share.getcloudapp.com/YEuApz2Q

I’ve read through everything I could find related to this and have yet to find an answer. To be absolutely clear, I’m NOT trying to find a row’s position within the group. I want a column that always accurately numbers the row’s current visual position, ignoring grouping.

@Tyler_Berggren I made the change in your document and created a copy for rest of the folks to see (this approach works as long as groups are not manually arranged)

Thank you for your help @Krunal_Sheth! I should have specified earlier, but for my use case, manual re-arranging of groups will be common. :grimacing:

Any ideas?

@Tyler_Berggren unfortunately not - whats the order by its manually rearranged? you could have All Groups column take same logic into its ordering and rest of the columns should fixed up.

btw, mind telling me more about why is visual order needed in your doc - maybe i can recommend something different?