Compare Dates&Times for Overlap

[Cian]

I have been using FMP for awhile and recently took on the task of creating a simple reservation/checkout database for AV equipment. I need to page through the current reservations and checkouts and see if the proposed checkout/reservation conflicts with any of the current checkouts & reservations. I am having trouble getting the script to work for both multiple day checkouts and hourly checkouts. Has anyone else done this? Examples? Help?

Thanks,

Cian

[jenniweiss]

Can you describe the script you've tried and where it doesn't seem to be working?

[BobWeaver]

For these situations it's always easiest to convert date and time into a single number to keep calculations simple. To do this, multiply the date by 86400 (number of seconds in a day) and add it to the time. You can do this arithmetic because Filemaker stores date and time values internally as days and seconds (past midnight) respectively. Suppose you have these fields for your existing reservations records:

ResOutDate, Date

ResInDate, Date

ResOutTime, Time

ResInTime, Time

you can convert these to:

ResOut = ResOutDate * 86400 + ResOutDate

ResIn = ResInDate *86400 + ResInTime

Then if you want to check for conflicts with a proposed reservation, calculate the proposed rental date/time fields in a similar way:

ProposedOut = ProposedOutDate * 86400 + ProposedOutTime

ProposedIn = ProposedInDate * 86400 + ProposedInTime

Now you can go through each record and use the following to test for conflicts:

Status = Case(ResIn<ProposedOut,"OK",

ResOut>ProposedIn,"OK"

"Conflict")

If any test returns "conflict" you know you have a problem.