I helped another couple of users with a room reservatations problem that dealt with this same issue. (Don't let a room be reserved if a previous reservation record for the same room overlaps the date range entered.)
First, I'd enter the three pieces of information into global fields and use a script to check for availability and create the new record only if the item is available. Define three new fields in your Rentals table: gItemID, gDateOut, gDateIn and select global storage for each.
You only describe one table here for your rental records so I will just set this up using that one table.
There are three ways a date range can overlap here.
- Date Out can fall within the date range, Date Out ... Date In.
- Date In can fall within the range.
- The date range of the new rental period "encloses" the date range of an existing rental record. (DateIN of the new rental is earlier than DateIN of an existing rental record and DateOut of the new rental is later than the DateOut of an existing rental record.)
It takes three relationships to check all three possibilities. Since we're working with a single table here, that requires 3 new table occurrences of your Rentals table. To create a new table occurrence of an existing table, select it in Manage | Database | Relationships and then click the button with two green plus signs. You can then double click this new occurrence to open a dialog where you can change its name.
You'll need these three relationships to check the above 3 possibilities:
Rentals::gItemID = RentalsOut::ItemID AND
Rentals::gDateOut > RentalsOut::DateIn AND
Rentals::gDateOut < RentalsOut::DateOut
Rentals::gItemID = RentalsIn::ItemID AND
Rentals::gDateIn > RentalsIn::DateIn AND
Rentals::gDateIn < RentalsIn::DateOut
Rentals::gItemID = RentalsEnc::ItemID AND
Rentals::gDateIn < RentalsEnc::DateIn AND
Rentals::gDateOut > RentalsEnc::DateOut
RentalsIN, RentalsOut and RentalsEnc are your three new table occurrences of Rentals.
On your Rentals layout, add your three new global fields to the layout and put a button next to them to perform this script:
If [IsEmpty ( RentalsOut::ItemID ) and IsEmpty ( RentalsIn::ItemID ) and IsEmpty ( RentalsEnc::ItemId ) ]
Set Field [ Rentals::ItemID ; Rentals::gItemId ]
Set Field [ Rentals::DateOut ; Rentals::gDateOut ]
Set Field [ Rentals::DateIn ; Rentals::gDateIn ]
Show Custom Dialog ["This item is not available"]
Hi, thank you very much, this is very very helpful..... you are the best~