HolidayList is a parameter as is DateStart and DateEnd.
Make sure all three are defined as parameters and check the spelling to make sure what you declared as a parameter is exactly what you are using in the functions defined calculation.
HolidayList is included as a Parameter.
I took some screen shots, but I don't know how to get them into a post. I've tried uploading, but am not sure if only one image will upload. Anyway,
My Preferences Table includes the following Fields
My Holidays Table includes
the field WorkDays is a calculation field as follows:
Unstored, = WorkingDays ( startDate ; endDate ; Holidays::Date )
Using 01/01/12 as the startDate and 02/29/12 as the endDate the calculation returns 43 as the answer. Taking into consideration New Year's, MLK and President's Days, the correct answer is 40.
The issue would appear to be in the custom function definition--which is where you define the parameters to be used in the function.
The parameters are circled in red in the screen shot I am uploading. (To upload more than one screen shot with the same message post, use an image editing program to combine the images.)
Thanks for letting me know how to upload multiple screen shots.
The parameters appear to have been entered properly. Does the formula itself make sense?
I see "HolidayList" has become "Holidays".
Does this mean that you are no longer getting that error message?
Holidays needs to be a return separated list of of all Holiday dates.
What I see in your original post is:
WorkingDays ( StartDate ; EndDate ; Holidays::Date )
That only passes a single date to the function instead of a list of holiday dates.
If you have this relationship:
Portfolio::anyfield X Holidays::anyfield
Change the function call to:
WorkingDays ( StartDate ; EndDate ; List ( Holidays::Date ) )
and this will pass a list of all Holiday Dates in the Holidays table to the function.
I'm not receiving any errors now when I exit the Custom Function editor. However, the function still isn't working.
I've changed the function call to add the List (Holidays::"Date) as the third parameter. I've also tried changing the CF definition by changing
IsEmpty ( FilterValues (StartDate ; Holidays )
Is Empty (FilterValues ( List ( Holidays::Date ))
However, when I attempted to exit the CF editor, I received an error message indicating that it was expecting a Paren at the first colon in
Let ( $counter = $counter + ( Mod ( StartDate - 1 ; 7 ) < 5 and IsEmpty ( FilterValues ( StartDate ;
List ( Holidays::Date ) ) ) ); Case( StartDate > EndDate ; "" ; StartDate = EndDate ; $counter & Let( $counter = "" ; "" ); WorkingDays ( StartDate + 1 ; EndDate ; Holidays ) ) )
I've also tried changing the Date field in Holidays from "Date" to "Text" and back to "Date" without any effect.
As the Layout image shows, these changes (with and without the change in the CF defintion) does not make a change in the results.
It still is not picking up the number of holidays, which in this case should be 3.
Is the relationship set-up correctly? Any other suggestions?
The relationship between preferences and holidays should be either:
Preferences::anyField X Holidays::anyfield
Preferences::Startdate < Holidays::date AND
Preferences::Enddate > Holidays::date
Note that with the second relationship, you don't actually need the custom function to count the number of holidays.
Count ( Holidays::date )
Will return the number of holiday dates from StartDate to EndDate for that Preferences record.
That can be used in combination with this method: http://help.filemaker.com/app/answers/detail/a_id/5281/kw/%22number%20of%20days%22
To calculate the numer of elapsed days.
Wow! That solved the problem. I've gone round-and-round with the calculations and scripts trying to solve the problem, even used the
count ( holiday::dates ) at one point.
It turns out the key was getting the relationship correct. I didn't know about the operators available in making the link. Showing the <= and >=
operators in your example above led me to explore the relationship link further, voila, it works!
Thank you sooooo much for your help (and patience)!