Calculation to see if a time slot is effected by calendar items

[markreynard]

Having a problem getting my head around this.

 

I have 4 time slots that can be used to book interviews in.to our meeting room, which is used for other things by other people other than for interviewing.

 

I am builing a system so that users can allocate a timeslot to a candidate for an interview, but first I would like to query the calendar to make sure that the slots arent effected by other events in the calendar

 

 

OK.

 

These are the timeslots.

 

9:00 - 1030

10:30-12:00

13:00-14:30

14:30-16:00

 

So im thinking i have the following fields

 

Date

Timeslotonestart

Timeslotoneend

Timeslottwostart

timeslottwoend

timeslotthreestart

timeslotthreeend

timeslotfourstart

timeslotfourends

 

Then in my Calendar items I have these fields.

 

ID: self generated serial

Date

Starttime

Endtime.

 

I could have a record in my calendar items database say from 8am to 12pm on 01/04/2015. This would effectively make Timeslotone and Timeslottwo unavailable.

 

I have tried to use the calcualtion count  to count related databases using > < but it doenst want to work.

 

File is attached

 

I would be grateful for any help

 

 

 

 

checking times.zip

[GisMo]

Use a self-join relationship where Date Matches and Time Matches. For the Time, you'll need some greater-than/less-than relationships to get the time overlapping correct. You can then see if there's a conflict immediately upon creation of the appointment.

[comment]

See also:

http://fmforums.com/forum/topic/64891-prevent-to-events-in-one-periode-of-time/?p=307692