You could build a relationship for scheduling records to its own table, joined by the Data and Employee fields. (You also want to include a ≠ join on the scheduling record's own primaryKey field so a record won't be related to itself.)
Then you can use conditional formatting to hide or make visible a text plock that says "Overlap" or color the record background red, or whatever warning you want if a related record has a start time ≤ this record's start time, but also has an end time greater than this records start time, or
Start time before this record's end time, etc....
Note: the record's will have to be committed before you are going to see the results, so someone has to be paying attention to the warnings to fix the record, as it already exists at that point.
Thank you for the reply....I just can't get it work. So the relationship would look something like this ?
How do I relate the start and end time ?
I wouldn't even attempt to use the times in the relationships.
Checking the related records' values for earlier starts and later finish times is handled by the calculations used in Conditional Formatting of the layout objects.
Understood, going to test it out and thanks once agian.
I'm so sorry to bother you. I understand the concept, but I can't get it to work. Could you kindly update the attachment so that I can take it apart, please ????
found it in your original post... working on it...
It's on the first post ....THANK YOU SOOOO MUCH !!!
Ok, I've attached a file with this working.
I had to do a bit of data restructuring, as the calc for DOM wouldn't work for the needed relationship, and I added a TO to the relationship graph for identifying the same person's assignments on the same day to different schedule parent records.
I applied conditional formatting only to one to the Start Time, but a similar calc could be setup for the End Time field as well.
Overlap_Rev.fmp12.zip 12.4 K
I'm on my way out, but when I get home I going to look at it. I REALLY APPRECIATE IT . !!!!
Yep, over, and OUT here as well.
IT WORKS !!!! pure genius. Thank you Mr. Huston !!!!
You can relate the times by adding this:
Start.Time <= related.End.Time
End.Time >= related.Start.Time
Thank you too, going to try this out as well.
There's something not right about this conditional calc. Your third statement is the same as the first.
If you're going to check conditions, there are three:
1) Starts during
2) Ends during
3) Starts before, ends after
You're missing a check for #3.