Formula to dynamically calculate record sequence number in a sorted group

[David_Goldstein]

I have a base used for tracking waiting lists for multiple events. A simplified version of the base for purposes of this question is here. Each record is one person’s entry on a waiting list for a specific event. Each record has a Status that starts out as “Waitlisted”, and then we change the Status to other values as we clear people from the waiting list for an event.

We normally use a view that groups the records first by Event Name and then by Status, with records sorted by the Created date/time. This is the first view shown in the demo base. We take people off the waiting list for an event in the order that they signed up for the waiting list.

We often have people ask us, “what number am I on the waiting list for X event?” Rather than having to manually count rows (which could be 100 or more) to answer this question, I want to have a field that shows each person’s sequential position on each event’s waiting list, based on the Created date/time. The mocked-up field called “Position on Waiting List” illustrates this, but I need a formula that will dynamically calculate that number, and then recalculate it each time we change someone’s status from “Waitlisted” to something else. The field can be blank if the status is other than “Waitlisted”.

Here’s an example. Let’s say the waiting list looks like this:

We then register John Doe for Event 1, so his Status changes to Registered. When that happens, everyone else moves up one spot on the list, so the Position on Waiting List should recalculate like this:

Any ideas on how to write that formula? Thanks in advance.

[ScottWorld]

As far as I know, this is only possible by writing your own custom JavaScript or by using an external automation tool like Make.com. There are JavaScript experts on these forums that you can hire to write the script for you, and I am a Make.com expert who is also available for hire!

[kuovonne]

Welcome to the Airtable community!

It looks like you are managing everything in one table.

If you use a multi-table system with linked records, this is possible with formula fields and rollups. The rollups might take a while to re-calculate if you have a lot of linked records, but they would recalculate automatically with no scripting, no automations, and no third party tools.

If you are interested in having me guide you through setting up this system, you can book a meeting with me.

[David_Goldstein · Accepted solution]

Thanks for the hint. Using that and this post, which you also answered, I came up with a solution that works, as shown in my example base:

1. Create a separate table, called Events.
2. Convert the Event Name field in the Waiting List table to a Link to Events.
3. Expose the Record IDs in the Waiting List table (field named “ID”).
4. Create a Rollup field in the Events table of all the IDs for records in Waiting List with a Status of “Waitlisted”, aggregated using ARRAYJOIN().
5. Expose that Rollup field in the Waiting List table using a Lookup field (“All IDs”).
6. Use SEARCH to find the position of each record’s ID in the All IDs field.
7. Because the IDs are 17 characters long and SEARCH() returns the character position in the array (e.g., 1, 19, 37, 55), do a little math to convert the output of SEARCH() into an ordinal number:
   IF(Status="Waitlisted",1+(SEARCH({ID},ARRAYJOIN({All IDs}))-1)/(LEN({ID})+1),BLANK())
   (if the Status is something other than Waitlisted, the formula leaves the field blank)

So, it works, but it sure seems like there might be an easier way. Can this be simplified somehow?

[ScottWorld]

@David_Goldstein Great workaround to this dilemma! Love your ingenuity here! :slightly_smiling_face:

[kuovonne]

David_Goldstein:

Can this be simplified somehow?

You found the basic technique that I use and described my other post that you found. My formula has a few minor differences from yours:

• I include the final formula in the rollup itself, to have one less field.
• I leave off the third parameter in the IF function (BLANK()) as it is not needed.
• I add delimiting characters around the record ID in the unlikely but theoretically possible situation where the exact characters in a record ID happen to occur as a mixture of two other record ids.

[David_Goldstein]

@ScottWorld Thanks, but I can’t take much credit for it. @kuovonne pointed me in the right direction and then I found a post where she’d offered a solution to a similar problem. :slightly_smiling_face:

[David_Goldstein]

Thanks @kuovonne. Changing the “All IDs” field from a Lookup to a Rollup and including the formula in it does eliminate an extra field to clean it up a bit.

[thisonelife]

I have a similar condition where each status requires a unique sequential number as well. i.e. I have projects that have 5 different types of tasks.  The example you provided gives a sequential number for all tasks under the project (perfect!) but now I need a different sequential number for each task as well.