Getting the row number

[Michael_Norris]

In Excel, you can use the ROW() function to return the row number of the current cell. A feature like this in Airtable would be useful, as I have a list of events that I want to recalculate their date depending on the order that the user has the rows in. I can’t use the autonumber feature, because that doesn’t renumber when the user reorders the rows. There doesn’t appear to be any way to get Airtable to work out what the order of records is, and calculate a formula based on that.

[W_Vann_Hall]

Not a solution, but I recently posted a reply to another user looking for spreadsheet-like functionality within Airtable with some thoughts on why that wasn’t likely — or, in many cases, even desirable. By not being row order-dependent, Airtable supports functionality unavailable in a spreadsheet. The solution, then, is not to retrofit spreadsheet-like behavior into Airtable but to find ways to reimplement algorithms in ways that decouples them from row order — something, admittedly, that Airtable doesn’t necessarily make easy to do.

I was able to find a way to provide the functionality desired by the other user. If you can elaborate more on what you’re trying to accomplish, perhaps someone here can help, as well.

[Michael_Norris]

Thanks for your reply. My scenario is that I want to use Airtable to help manage and administer a series of events that happen from March through to October every year. I have a table called ‘Event Info’ which has the event’s title, the speaker’s name, their email address, a coordinator for the event, and the date that the event occurs on. It is the last field that I’m trying to make happen automatically. So every year, there are always exactly 24 events, and they always happen on a Wednesday. The exact Wednesdays, however, depend on when Easter falls and what day of the week 28 February is. I have managed to successfully code a table (‘Event Dates’) in Airtable that automatically generates these dates, but I can’t work out how to get the dates from the Event Dates table into the main Event Info table without manually coding a Link field for each event. Furthermore, I want the user to be able to drag-and-drop a row to change the order of events, and for the dates to automatically update.

[Michael_Norris]

Thinking more conceptually here, I get that order-agnosticism is a useful trait of a database. However, I think that there’s a relatively easy solution, at least in my case: by having an Auto-Number field type that allows for the option of recalculating the numbers on record re-ordering. I don’t see how that would break the database metaphor — it would just be the Airtable UI repopulating the field content when it detects a record re-order operation.

[El_Poussin]

I would also desperately need to get the row number (or “rank” of records).
I guess it’s really not weird to add this to Airtable, if you consider for instance the ROW_NUMBER() function in SQL => it could be added as un function in the Formula field type.
Or as Michael_Norris proposed, an option on the Auto-number field to recalculate the numbers on re-ordering or sorting would be great.

But really any solution would be fine to me :slightly_smiling_face:
Right now I can’t figure out how to do this, any hint will be greatly appreciated !

[W_Vann_Hall]

I’ve been experimenting with different ways of addressing at least adjacent (in time) records, and I should have a couple approaches up by the end of the week. In the meantime, if you read through this post, you’ll find a technique I threw together to allow the topmost record in a list of grouped records to be identified. Should the user reorder the lists through drag-and-drop, there’s a simple two-step process to reprioritize them, bringing the newly topmost records to attention first. (To be honest, I’m not exactly sure why it works — but at the moment, it does. If enough people try it, it transitions from ‘undocumented feature’ to ‘legacy functionality’ and acquires an air of permanence.) Admittedly, the function is somewhat orthogonal to the one you seek, but with a little tweaking, you might be able to reach the destination by a different route.

You can also find the first wave of some of my experiments in persistence and cross-record calculation in the second generation implementation of my Gold Price demo base. By converting dates into their ‘days since epoch’ representation, I can create formulas with the ability to access data from records an arbitrary number of increments distant from the current one. This avoids the roll-over issue inherent in systems based on cardinal date. I’ve worked up routines to handle intervals of every day and every workday, and yesterday I stumbled across something that leads me to believe I’ll be able to access N records previous — even when new records are added at random intervals.

That may not sound like much — but believe me: It is. The rudimentary systems I’ve released to date have all depended on there being data from the previous day — and on the next-most recent record always being from the day before. Should either of those conditions not hold, the system will require manual intervention to reestablish sync. Should this random-interval engine make it through testing and development, not only will it support more peripatetic workflows, it will provide a more robust and much more forgiving platform for managing even tightly regimented processes.

[W_Vann_Hall]

The suite of routines supporting multi-record data access mentioned in the previous reply can be found in in this post from the Show and Tell forum.

Multi-record calculations (c.f. 'running balance,' 'row number,' and the like) Show and tell

tl;dr version: A frequent query and/or complaint concerns users who wish to access data from a record for use in a calculation performed in a different record from the same table. Often this takes the form of a user asking ‘how to retrieve a value based on row number’; other times the user wishes to perform a calculation that assumes knowledge of the previous record — for instance, maintaining a running balance. A recently published base contains four approaches to this problem, three involving …

[Jacob_Turley]

I know this is an old thread, but I need it for this use case:

We have a table of hundreds of “tags” that get attached to a different table (tags). I want a simple bar chart of the top 20 tags by company count(I have a roll up field that shows this number). I am slightly accomplishing this using the auto number field, but I don’t think it recalcs if a row changes rank. Is there any way to limit a view/chart to use only the top 20 rows etc?

[Oli_Steadman]

I would definitely find a ROW() or ROW_NUMBER() function extremely useful.

[Michael_Norris]

And the other thing I would find extremely useful is the row number within a particular view.

[Melissa_Frank1]

Ditto! I have a pipeline of tasks I am working on that I need someone else to see are all on my radar. As priorities change, I drag the records around to reorder them, and renumbering the “rank” field from the change on down the list is a pain. A formula to return the record’s current row number in the view would save me so much time. Or at least have the number field type let us do the step fill that we can do with date fields?