Finding Records where today's date is between startdate and enddate

[Matthew Shiner]

I want to be able to find all records of people who are scheduled out for today, given a vacation request with a start date and end date.

 

So looking to find all records where today's date falls between (or is equal) to start date and the end date.

 

Relatively new to Filemaker.

 

[Ocean West]

if you have filemaker advance (required) you can add this custom function

http://www.briandunning.com/cf/232  (note the comments)  here is the code

DateValuesStrict ( dateBegin ; dateEnd ) 

Let ( [
dateList = Case ( 
dateBegin = "" ; "?"; 
dateEnd = "" ; "?"; 
not IsValid ( GetAsDate ( dateBegin ) ) ; "?"; 
not IsValid ( GetAsDate ( dateEnd ) ) ; "?"; 
GetAsDate ( dateBegin ) > GetAsDate ( dateEnd ) ; "?"; 
dateBegin = dateEnd ; dateEnd & ¶; 
dateBegin & ¶ & DateValuesStrict ( GetAsDate ( dateBegin ) + 1 ; GetAsDate ( dateEnd ) )
);
result = dateList
];
result
)

 

its a recursive custom function that when you add a calculation field called DateArray:

DateValuesStrict( staff::startDate ; staff::endDate )

your results would be a text result if the data in the date fields were 8/25/2015 and 8/31/2015 respectively

8/25/2015
8/26/2015
8/27/2015
8/28/2015
8/29/2015
8/30/2015
8/31/2015

Then you could search on that array field daily with the current date: i.e.: 8/29/2015 you would find all records who are on vacation for today.

Edited August 29, 2015 by Ocean West

[eos]

its a recursive custom function that when you add a calculation field called DateArray:

Wouldn't it be easier to search for records where dateStart <= current date and dateEnd >= current date?