Skip to main content

Filter or formula for Soonest date?

  • July 23, 2024
  • 1 reply
  • 22 views
DisraeliGears01
Forum|alt.badge.img+21

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? 

1 reply

B
Forum|alt.badge.img+11
  • Blake_D
  • Inspiring
  • July 23, 2024

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',' ')
 
 Then create a view that filters for "Filter for Soonest" is not empty.
An automation to automatically link to the event order table when an event is created can help as well.
Hope this helps!
TheTimeSavingCo
Terms & ConditionsAccessibility statement