Create a dynamic sequence for records to show the next 20 chronologically

[John_Campbell]

Hi,

I have a base with a lot of records being submitted that have dates attached such as events. I want to be able to show the next 20 records coming up, keeping in mind this will change any time a new record is created with an upcoming date in the next day or two. So that might make record number 5 in the sequence be pushed out to record number 6 since a new once came in. But I still just want to present 20 records at any point in time.

Thanks!

[ScottWorld]

Unfortunately, this is only natively possible in Airtable by using the Record List extension.

Otherwise, you’d need to either:

(a) write your own JavaScript to mark the next 20 records on a regularly scheduled basis, or

(b) you could use Make to do this same thing, which is the only no-code automation tool that lets you put a limit on the number of items returned in a search, sorted in any order that you want.

[kuovonne]

You could manage this with a linked record field to a single “control record” in another table.

When a new event record is created, have an automation link it to the control record. 

In the control table, fields that calculate if there are over 20 linked records, and the minimum date of the linked event records. This could be done all in one rollup field, but you may be more comfortable initially building it across multiple fields.

Back in the original event table, have a rollup field of that minimum date in the control record so that a record can identify if it is the oldest linked record. Have this field trigger another automation that unlinks the event record from the control record. 

Finally, have a filtered view that only shows the event records that are linked to the control record.

Normally I don’t recommend using a single control record, but I think it is okay when it will be linked to only about 20 records.

@ScottWorld This method does not require any scripting or third-party services. 

There are a few caveats. If multiple events have the same date, when they become the oldest linked date, all of them will fall off at the same time, bringing the total to under 20. However, that is a reasonable decision and will self-correct as later events are added. 

If records are created without dates, you may have more than 20 records in the view. That should fix itself as soon as all events have dates. 

If an event dates change that would move the event off the list of 20, it won’t move off until after a new event is added. 

If an event date changes so that it would move onto the list of 20, you may need an automation to link it to the control record. This can get tricky if records are initially created without dates as you do not want to end up with an endless loop of linking/unlinking to the control record. 

@John_Campbell While this can be done, consider if the effort in setting up and maintaining the system is really worth it. It often isn’t worth the maintenance.