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

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

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!

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

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! 

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