Jul 23, 2024 11:03 AM
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?
Jul 23, 2024 12:56 PM - edited Jul 23, 2024 12:58 PM
This may be the long way...
Create a formula field that shows days until event.
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"