Test for "sequentiality" of related dates

[Rich S]

Howdy, howdy...

I have an Events database with a related table (Dates) since a named event can occur on multiple dates, e.g., SAT Study Hall can occur on 6/1/2023, 6/3/2023, and 6/11/2023, or 6/1/2023, 6/2/2023, and 6/3/2023. (As an aside, the reason why the Dates table is related is because there's also a grandchild table (Attendees); each day's attendees can be the same or different depending on whether all the attendees show or not. This conforms to the one-to-many relationship model...I hope.)

Currently, I have a checkbox/flag field users click to show whether an event's dates are sequential or non-sequential; the result of that is a calculation that displays the dates in either a comma-delimited fashion (for non-sequential dates) or separated by a single hyphen (for sequential dates, e.g., Min (Date) & " - " & Max (Date).)

I thought I might be able to eliminate the checkbox/flag altogether by using either a script trigger to fire a script, or by way of a calculation; the former would require the user to click somewhere/something to commit the record after entering an event's date(s), so I was wondering if a calculation could be coded to determine the "sequentiality" of an event's related dates...but I can't think of how. Maybe use the WHILE command? I don't know.

Thanks in advance for your help!

Edited May 30, 2023 by WF7A

[comment]

Maybe something like this (adapted to dates and possibly using the While() function instead of recursing)?
https://www.briandunning.com/cf/734

---
EDIT: if you only need a Boolean result, then it can be simpler - say something like:

While ( [ 
i = 1 ;
result = True
] ; 
i < Count ( Dates::EventID ) and result = True
; [ 
result = GetNthRecord ( Dates::Datefield ; i ) + 1 = GetNthRecord ( Dates::Datefield ; i + 1 ) ;
i = i + 1
] ; 
result 
)

Caveat: not tested very thoroughly.

 

 

Edited May 30, 2023 by comment

[Rich S]

Thanks for the edit--I'll try it tonight. I couldn't get the custom function to work, so I'll blame myself for being dense.

[comment]

Another possible approach (not necessarily better) is:

Let ( [
N = Count ( Dates::EventID )
] ;
ValueCount ( UniqueValues ( List ( Dates::Datefield ) ; 3 ) ) = N
and
Max ( Dates::Datefield ) - Min ( Dates::Datefield ) = N - 1
)