Since FileMaker Pro 8.5, the List function has been a huge resource in implementing many features. One aspect it’s very useful in is calculating business date deadlines with holidays factored in. Say you had a BeginDate and needed to determine 10 business days after that date and show that calculated DeadlineDate on the same layout.

Using a script trigger is not ideal for this situation and there are no existing functions that enable this feature. You will have to use a custom function to run through the days. Now, without holidays, you know there are 2 weekend days after every 5 business days so there are numerous calculations that will let you determine the DeadlineDate given. FIleMaker lists a variety of different one-line custom function solutions to accomplish this goal: http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=5053

However, to factor in holidays, a single-line function won’t work because holidays can be interspersed within and there’s no way to keep looking until you reach a business date. You may start on a holiday, move into a weekend, move to a holiday week, move to another weekend, etc. and technically, 0 business days have passed.

The method needed to implement the DeadlineDate will thus need to involve recursion (basically, a function calling itself until an end condition is met). Just as an example, let’s say we want to determine a 5 business day deadline. We’ll make a custom function called Get5BusinessDaysAfter (StartDate; Holidays; Counter). To set up this function, we’ll need a Holidays table and a few fields.

First, make sure you have a Holidays table with at least a HolidayDate field. Join the Holidays table occurrence up to your current table occurrence using a cartesian join (X). This way, you can view all holidays from your current table occurrence. Now, in your current table, you’ll need these fields:
-MyDate date (to enter in the start date)
-DaysAfter number calculation (to determine how many total days should elapse, business or otherwise
-DeadlineDate date calculation (to show the ending date after 5 elapsed business days),
-HolidaysList text to store all the holidays in 1 field

The DaysAfter calculation should = Get5BusinessDaysAfter (MyDate + 1; HolidaysList; 1). Basically, you start counting on the next day (day 1), include the list of holidays and start on a counter of 1 out of 5 business days to find.

The DeadlineDate = MyDate + DaysAfter. This is simply the original start date plus the calculated elapsed days to make 5 business days.

The HolidaysList = List(Holidays::HolidayDate). Since you’ve added all your holidays into the Holidays table, this field aggregates every single holiday into 1 field. Having the List function is essential for custom functions because there’s no way to use a normal field or use a relationship within a function.

Now that we have all the fields set up, we can move on to the definition for our Get5BusinessDaysAfter function:

Case (
Counter > 5; 0;
DayOfWeek ( StartDate ) = 1 or DayOfWeek ( StartDate ) = 7; 1 + Get5BusinessDaysAfter(StartDate+1; Holidays; Counter);
PatternCount(Holidays; StartDate) > 0; 1 + Get5BusinessDaysAfter(StartDate+1; Holidays; Counter);
1 + Get5BusinessDaysAfter(StartDate+1; Holidays; Counter+1)
)

Basically, this is 1 case statement with a termination case, a default case, and 2 additional cases. Our primary goal is to count up the number of days from the start date until we get 5 business days (first line, which is the termination case). In the first case, if we encounter a Saturday or Sunday, we move on to the next day without incrementing the Counter. In the second case, if we find that the current date is in the list of holidays (passed in from our list field), we also move a day forward without incrementing the Counter. In the default case, only on a non-weekend and non-holiday will the Counter increase.

So the function will keep calling itself while adding up all days until the Counter goes over 5. Let’s see how this would work if we start from 12/22/09. First off, we know 12/26 and 12/27 of this year is a Saturday and Sunday respectively. And one of our favorite holidays of the year is on 12/25. So here’s what our function would look like:

Get5BusinessDaysAfter (MyDate + 1; HolidaysList; 1)
0. Get5BusinessDaysAfter(12/23/09; …12/25/09…; 1) : We start with 12/23 and it falls into the default case so we add 1.
1. Get5BusinessDaysAfter(12/24/09; …12/25/09…; 2) : We continue with 12/24 and it also falls into the default case so we add 1.
2. Get5BusinessDaysAfter12/25/09; …12/25/09…; 3) : 12/25 is detected as part of our list of holidays in the 1st case so the Counter is not incremented as we move another day forward.
3. Get5BusinessDaysAfter(12/26/09; …12/25/09…; 3) : This is a Saturday and falls under case 2 so we simply move another day forward without adding anything.
4. Get5BusinessDaysAfter(12/26/09; …12/25/09…; 3) : This is a Sunday so we simply move another day forward without adding anything.
5. Get5BusinessDaysAfter(12/27/09; …12/25/09…; 3) : Monday the 28th is a normal day so we add 1.
6. Get5BusinessDaysAfter(12/27/09; …12/25/09…; 4) : Tuesday the 29th is a normal day so we add 1.
7. Get5BusinessDaysAfter(12/28/09; …12/25/09…; 5) : Wednesday the 30th is a normal day so we add 1.
8. Get5BusinessDaysAfter(12/27/09; …12/25/09…; 6) : The Counter has exceeded 5 so the recursion finishes and evaluates.

To reach the termination condition of 5 business days after our start date, we incremented the value of the function itself 8 times; if you review the code, you can see this is because we have “1 + Get5BusinessDaysAfter” in every case (note that the value of the function is added at the end of each recursion; the first time the function is called, the value of the function is 0).

So now that we know to add 8 to 12/22/09, we get 12/30/09, which is exactly 5 business days afterwards. When you show the DeadlineDate field, you can see it calculate right as you change the MyDate. And that’s how you use a custom recursive function combined with the List function to calculate a business date deadline with holidays factored in.