- Change Language (English) Change Language (Français) Change Language (Deutsch) Change Language (Italia) Change Language (日本語) Change Language (España)
- My Stuff
- Mes infos
- Meine Infos
- Le mie cose
- Mis cosas
- FileMaker - English
- FileMaker - French
- FileMaker - German
- FileMaker - Italian
- FileMaker - Japanese
- FileMaker - Spanish
- Bento - English
- Bento - French
- Bento - German
- Bento - Italian
- Bento - Japanese
- Bento - Spanish
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