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.