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.

 

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, 201510 yr by Ocean West

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?