It would be helpful for creating running balances and for other situations in which you want to refer to a row relative to the position of the current row to have a RowNumber() function that works like RowID(), but returns the current position of a row in a table (matching the row number that appears in the table at the left edge of the row). The result would be dynamic in that it would change based on view, sort order, manual changes of row order, etc.
2 Likes
do you use the formula
thisTable.Find(thisRow)
to give you the result you are looking for?
i find it works for me quite well
max
1 Like
Is this what you’re looking for?
The suggested row number is really not the same as any of the options that we currently have available. Rank, Find(thisRow), RowID(), and whatever you can think of always fail in one situation or another (filtering, sorting, deleting, manually moving things around).
There is no equivalent to having access to the row number as we see it on the left side of the table. Although there are a lot of work-arounds, simply try to get all the odd rows to have a conditional format and see that there will always be a situation where it fails.
3 Likes
Thank you for posting this, @Shiraaz_Peerkhan. I think this method works as long as rows are never sorted or manually changed to cause the RowID values to be out of order.
Thank you, @Agile_Dynamics. This is a new one on me; very clever. It seems to hold up to manual changes in row order, but not to sorting the table by another column.
I think you’re right, @joost_mineur. I need something more robust, that holds up to manual rearrangement as well as to filtering and sorting the table.
Todd
I always keep a ‘master’ table on a hidden page that is never sorted or filtered.
In that table the thisTable.Find(thisRow) will always generate a row-number that is stable.
Elsewhere in the document, you can have VIEWS of that table that are filtered and sorted as required - but my row-by-row calculations will always reference the ‘master’ table where the computed row-number is stable.
There is one other technique for generating a rock-solid row-number that you might want to look at;
- create a column, say, RowNumber (as a number type)
- in the Number Options menu, you can enter a formula for this column when a new row is created
- use the formula thisTable.Count()+1
- each time a new row is added, this generates a row number as a numeric value
- it is not a formula column, to the value generated is not impacted by filters or sorting
max
3 Likes
@Agile_Dynamics do you know if there’s a risk of race conditions with your count()+1 stamping approach?
What I mean is, if a row is created via the API, and one second later a user makes a row in the UI, do you know if they would get different numbers?
1 Like
One of my use cases is to turn a table into an outline. For this, I need to create a hierarchical numbering system in a column (such as 1.2.1.3). For this purpose, I need to be able to look up the hierarchical number in the previous row and generate the hierarchical number for the current row based on the previous row’s hierarchical number and the indentation level of the current row. The user needs to be able to insert rows anywhere in the table and manually drag rows to reorder them, all the while keeping the number hierarchy (dynamically). I think that for this purpose, I need to be able to reference the previous row regardless of the original order in which it was created. I don’t think that the solutions shared on this thread allow for this—but please correct me if I’m wrong.
Ah! I’ve done this before!
I don’t know where that doc is, but here’s an approach that might work
It does use thisTable.Find(thisRow).
Now the question is: how do you want to display it? Would it be best in a bulleted list outline?
1 Like
Thanks for sharing this @Connor_McCormick1. This might work for my financial statement docs!
1 Like