Jump to content

Compare Dates&Times for Overlap


This topic is 8408 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 8408 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.