These are the 3 basic relationship criteria for a "date conflict" on a piece of equipment. I just wrote "Equip 2" for the target Table Occurrence name; you can come up with a better name.
Equip::EquipID = Equip 2::EquipID
Equip::Start ≤ Equip 2::End
Equip::End ≥ Equip 2::Start
If you store the dates in two separate fields, a pair of separate relationships can be checked for an overlap in the date ranges:
Reservation::EquipID = ReservationByStartDate::equipID AND
Reservation::StartDate > ReservationByStartDate::StartDate AND
Reservation::StartDate < ReservationByStartDate::EndDate
Reservation::EquipID = ReservationByEndDate::equipID AND
Reservation::EndDate > ReservationByEndDate::StartDate AND
Reservation::EndDate < ReservationByEndDate::EndDate
ReservationByStartDate and ReservationByEndDate are new table occurrences of Reservation.
IsEmpty (ReservationByStartDate::equipID ) and IsEmpty ( ReservationByEndDate::equpId )
will be true only if the selected equipment has not been reserved for the range of days starting with startdate and ending with end date.
I am working on a similar system involving equipment availability.
In my situation, the Inventory::TotalAvailable field shows how many of the item are in inventory.
On the Invoice layout, there is a portal showing Inventory records; each row has a button with a script to add it to the LineItemByInvoice table.
I would like to show a field Inventory::QTYAvailablable, to display how many of each item is available for the current invoice record using the fields Invoice::StartDate & Invoice::EndDate
Would I essentially be using some variation of Phil's last post in a calculated field Inventory::TotalAvailable? What other TO's and/or fields would I need to add?
The solution I've outline here was intended to track the availabilty of one specific item. (also works for checking the availability of reserved rooms.)
It won't work as posted for a checking the availabilty of a group of items unless you track reservations on an item by item basis.
I also left out a third way reservation date ranges overlap. The above two relationships identify reservations records when the start date falls within the start and end date interval, (first relationship) and when the end date falls within the specified start and end date interval (2nd relationship). It's also possible that the specified date interval could encompass both the start and end date interval of a conflicting reservation record:
Reservation::EquipID = ReservationByEnclosure::equipID AND
Reservation::EndDate > ReservationByEnclosure::EndDate AND
Reservation::StartDate < ReservationByEnclosure::EndDate
which means the complete test would be:
IsEmpty (ReservationByStartDate::equipID ) and IsEmpty ( ReservationByEndDate::equpId ) and IsEmpty ( ReservationByEnclosure::equipID )
If you do set up your system to track each individual item out, you could use a similar expression in an unstored calculation field that returns number and then a summary field or a Sum function (from a related table) could count the number of items in a group that are available for the specified time frame.
PS. Threads like this that are more than 3 months old no longer appear in Recent Items when someone posts to them. This makes them easy to miss and I almost missed this one. You may want to start a new thread that includes a link to this older thread so that your posts appear in Recent Items where more people will see them.
Thanks for the tip, I'll give it a try tonight!
I am having quite the time wrapping my head around this one.
Could you elaborate further on how I could implement this in the system?
The portion of the database in question includes the following tables:
Currently, there is a portal on the invoice to add items from the table "InventorySearch", a T.O. of "Inventory", which is currently related through a SearchFilter type relationship. Each Invoice has StartDate & EndDate fields. I would like to view a field on each portal record that shows how many of each item is available for the dates of the current Invoice.
Items are not tracked individually; LineItems::QTY is the qty of that particular Item# on an invoice.