Jump to content
Sign in to follow this  
VAB

Prevent to events in one periode of time

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 )

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.