Filter or formula for Soonest date?

Hi all,

Wondering if there's a filter or formula solution (not sorting) to filter all results down to only the "Soonest one" ranked according to date field.

I have a form in one table linked to a view in another table, ideally so people can only sign up for one linked choice (it's a monthly event registration). Currently I have the filter set for Date within the next month which mostly works, but a day or two before the event, both the current and next month's start displaying (because it's only 28 days away, etc.) Perhaps my logic brain isn't on today, but I can't think of good filter terms to only include the soonest event. The other thought I had was a formula which would assign numbers or filters to the nearest date (-1 for past, 1 for soonest, 2 for next, etc.) and then I could make that the operative filter, but I don't see an obvious example of that in the Formula Reference Base or guidance. Any advice?

Page 1 / 1

This may be the long way...

Create a formula field that shows days until event.

DATETIME_DIFF({Event Date}, TODAY(), 'days')

Create a Event Order table to link all events to. Link all events to one record calling it Event Order. In the Event Order table create a rollup field to get the MIN(values) of "Days Until Event"

Create a lookup field in the main table to look at that rollup field. In the main table create a formula field "Filter for Soonest"

IF({Days Until Event}={Days Until Closest Event},'Soonest Event',' ')