Dear FM talents,

I have a challenging problem. In a table with:

- students (indexed ID related to other table)

- dates (date)

- lesson start (time)

- lesson end (time)

I'd like to prevent making an new record with a lesson that starts or finishes while another lesson takes place. I've made a self-join and a calculated field (with patterncount(list(etc.);dates)) to identify the lessons that take place on the same day, but I don't know how to do it with the times. It only works when two lessons start or finish at exactly the same time, but I want to identify the lessons that have a time overlap.

Any ideas?

Thank you!

EB

Your relationship needs to be something like:

Events::StudentID = Events 2::StudentID

AND

Events::Date = Events 2::Date

AND

Events::StartTime < Events 2::EndTime

AND

Events::EndTime > Events 2::StartTime

AND

Events::EventID ≠ Events 2::EventID

I am not sure what you're trying to do with "patterncount(list(etc.)". All that's required to count the related records is:

Count ( Events 2::EventID )

Thank you! I was trying to do these relationships through calculations... quite impossible...

One more question though: these are AND relationships, can I make OR relationships as well?

For instance:

start time > 2::start time

AND start time < 2::end time

OR

end time > 2::start time

AND start time < 2::end time

What would be the purpose of that? All possible overlaps (and only overlaps) are already included in the relationship as stated:

...

Events::StartTime < Events 2::EndTime

AND

Events::EndTime > Events 2::StartTime

...