finding time conflicts between classes with multiple meeting times

[dgroyal]

I am building a schedule generation database, and I'm trying to figure out an efficient way to identify schedule conflicts between classes.  Each class can have up to 5 meeting times.  I've got a way that works, but it's very brute force and I think there has to be a better way.

 

Currently, I've got a Student in Classes table related to itself by student ID and a non-equal Student in Class ID.  In other words, they will connect for all of the classes that a student is in other than the particular class being referenced.  Through this relationship, I'm checking for class time conflicts using this calculation:

 

Case ( sgClassTimes::timeStart ≥ sgClassTimesCONFLICT::timeStart and sgClassTimes::timeStart ≤ sgClassTimesCONFLICT::timeEnd ; "conflict" ; GetNthRecord ( sgClassTimes::timeStart ; 2 ) ≥ sgClassTimesCONFLICT::timeStart and GetNthRecord ( sgClassTimes::timeStart ; 2 ) ≤ sgClassTimesCONFLICT::timeEnd ; "conflict" ;  GetNthRecord ( sgClassTimes::timeStart ; 2 ) ≥ GetNthRecord ( sgClassTimesCONFLICT::timeStart ; 2 ) and GetNthRecord ( sgClassTimes::timeStart ; 2 ) ≤ GetNthRecord ( sgClassTimesCONFLICT::timeEnd ; 2 ) ; "conflict" ; "" )

 

The table with CONFLICT in the name is the reciprocal table.  The formula above works for classes with up to two meeting times, but I need to go up to 5 and doing that in this way will lead to a very long calculation.  Ideally, I'd like something that checks for a conflict in ANY matching record, rather than having to go through them all 1 by 1 using GetNthRecord.

 

Any suggestions?  I apologize if I haven't explained this well, and please don't hesitate to ask if you need any more information.

 

Thanks!

Dave

[eos]

Here is one way to do it. If you don't want to store date ranges with the records, you can use a script instead of relationships to collect/calculate/compare the relevant values on the fly and detect any conflicts.

 

EDIT: Eliminated a TO and streamlined the very roundabout method to collect ranges.

Conflict_eos.fmp12.zip

[dgroyal]

Thanks for sharing this!  Unfortunately, I'm using FM 11 so I can't open the file.  I've thought about doing it with a script, though, and I was actually working on a solution in that vein today.  I'd love to see your file, if it isn't too much trouble to save it in an FM 11 compatible format.

 

Thanks again!

[eos]

Unfortunately, I'm using FM 11 so I can't open the file.

 

I used to be able to read – those were the days … Anyway, here it is again.

Conflict.fp7.zip

[dgroyal]

Wow, thanks so much for this!  I don't fully get it at first glance, but I'm going to dig into it.  This is extremely helpful! 

[dgroyal]

Just to follow up, I used your approach and got it working.  Thanks again!