Conflicting events recognition in scheduling script

[martingaik]

Hi, I was reading FMForums for quite a while now, but never needed to register - now it's a time. I have this mind buggling problem with my scheduling script, but let me explain how it works first:

there is an event table that contains following fields:

date, starttime, endtime, tutor, classroom

now - if you have an event in the database and you want to create a new one it should go and check if there is a record that already uses the resources in the same time - if it does, there is a conflict... so

my script does the following job in simple words:

# setting variables first

> set variable [color:green]$$date_new

> set variable [color:green]$$starttime_new

> set variable [color:green]$$endtime_new

> set variable [color:green]$$tutor_new

> set variable [color:green]$$classroom_new

# prepare foundset for investigation

> omit current record

> go to first record

# start the loop

> loop

> check if [color:red]date = [color:green]$$date_new

and (([color:red]tutor = [color:green]$$tutor_new)

or ([color:red]classroom = [color:green]$$classroom_new))

and ([color:red]starttime ≠ "")

and ([color:red]endtime ≠ "")

>> check if ([color:green]$$starttime_new ≤ [color:red]starttime) and

([color:green]$$endtime_new > [color:red]endtime)

>>> show dialogbox with conflict error

>> endif

>> check if ([color:green]$$starttime_new ≥ [color:red]starttime) and

([color:green]$$endtime_new ≤ [color:red]endtime)

>>> show dialogbox with conflict error

>> endif

>> check if ([color:green]$$starttime_new < [color:red]endtime) and

([color:green]$$endtime_new ≥ [color:red]endtime)

>>> show dialogbox with conflict error

>> endif

> endif

> go to next record (exit if last)

> end loop

and that is all. The script should check for overlapping events, but it doesn't work - the second equation does not produce the dialog box even if the time of events overlap each other

the equation should operate like that:

if start time of a [color:green]new event is bigger or equal to the start of [color:red]existing event and the end time of the [color:green]new event is smaller than the end time of [color:red]existing event there is a conflict

I did something wrong but I just cannot find out what? Thanks a lot for all your help.

Edited May 17, 2007 by Guest

[Robert Kidd]

Hi

I would try to set up a relationship to check for conflicting records. This has the advantage that you don't need to trigger the script and looping scripts can pottentially be slow.

Have a look at the attached file and see if it does what you want.

Robert

EventCheck.zip

[comment]

Check your relationships again - it doesn't look right.

[Robert Kidd]

Ah ha, 2nd attempt.

EventCheck.zip

[comment]

:waytogo:

[martingaik]

Yes that seems to be working, but what if an event A occurs at 9:00 and finishes at 14:00 and event B starts at 10:00 and finishes at 13:00? In other situations it really solves the problem, as the new event gets a status "Event Clash" but in the situation described above it causes both events to have the status of "Event Clash". It does not seem stable enough and I was playing with those relationships but no luck so far...

also, at this moment it only uses one resource - that is Office 1 for example... I am novice in filemaker and some things are making me really confused here... like... what should I do to have another resource... let's say I have a multimedia projector... what should I do then?

[color:red]Thanks Robert Kidd and Carpal Tunnel,

you are opening my eyes for something new.

[Robert Kidd]

You would get the conflict flagging against both records with this method, but I wouldn't have thought this was a bad thing.

You would need 1 relationship per resource to check, again not a bad thing as you would be able to identify in the error message which resource the conflict occurred with.

[martingaik]

Thanks Robert,

so what can I do to make it consistent... as at the moment it works for some cases and it doesn't for other. It is not a bad idea to indicate clashing of both events, but how can I make it work in all cases?

[Søren Dyhr]

Robert - although it originates from the fm7 days, notice what John Sindelar says here:

I've found using range calcs like these to be quite a bit faster than using theta joins within FileMaker- especially when there are multiple relationship predicates. I imagine this kind of thing will be in our tool kit for a while

...from: http://www.clevelandconsulting.com/support/viewtopic.php?t=299&highlight=smartranges

If you attended Devcon in 2005 did you notice that LeCates talked about a fix to theta joins, from version 7 to 8, but similar did Moyer talk of the benefits from fewer indexes in very large solutions. This scaling issue is of some importance when the indexes to transport in networks can be limited somewhat.

--sd