AnsweredAssumed Answered

Using Date Ranges to filter/find/exclude Date Ranges

Question asked by ptt555 on May 5, 2010
Latest reply on May 7, 2010 by aammondd

Title

Using Date Ranges to filter/find/exclude Date Ranges

Post


Hi all,

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       ID_Description

A        Room1

D        Room2

G        Room3

 

Table2

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)

ID      Date

A       05/01/2010

A       05/02/2010

A       05/03/2010

G       05/16/2010

G       05/17/2010

A       05/14/2010

A       05/15/2010

A       05/16/2010

 

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

 

Outcomes