Using Date Ranges to filter/find/exclude Date Ranges
I have records containing date ranges (startdate, enddate).
I need to perform a find using a date range, and have Filemaker return the ID's/records that DO NOT have any records within that date range. Think similar to a reservation system. Find all rooms (ID's) that are not reserved for any dates between 4/1/2010 & 4/15/2010.
DATA LAYOUT/SAMPLE DATA (all tables related via ID)
Table1 (ID is unique)
ID StartDate EndDate
A 05/01/2010 05/03/2010
G 05/16/2010 05/17/2010
A 05/14/2010 05/16/2010
Table3 (a table containing a seperate record for every ID in Table2 from startdate to enddate inclusive)
FIND EXAMPLES & DESIRED RESULTS
Enter a date range & have FM return all ID's that have NO dates within those ranges
Range 04/29/2010 to 05/30/2010 would return nothing (each ID has some of the dates)
Range 05/10/2010 to 05/15/2010 would return would return G (since for A, a record exists for dates 5/14 & 5/15/2010)
Range 05/08/2010 to 05/12/2010 would return A & G (since no records exist for either in that date range)
Does my data structure allow for a successful find using Filemaker?
I realize I may not need both Table2 & Table3 to accomplish this.
What would be the recommended approach?
Advance thanks for all that take time to help.
FM Pro Advanced 10 on MAC