Help figuring this out for holidays

[Pescador]

I found this on FileMaker.com forum. I found it interesting and am trying to get it to work correctly. I have been working on it but have not got it to work properly. I am pasting the text below. Will someone with more understanding interpret it and make a sample of it. Thanks in advance.

If a holiday (of one or more days) occurs between StartDate and EndDate, and you don't want to count the holiday as a work day, then some changes are required. First, create a new Table, HOLIDAYS, which contains the following fields:

Date (Date)

Counter (Number)

Holiday (Text) - Optional

Use the following table to enter the values:

Date Counter Holiday

1/1/07 1 New Year's Day

1/19/07 2 Martin Luther King

2/16/07 3 President's Day

5/31/07 4 Memorial Day

7/5/07 5 Independence Day Observed

9/6/07 6 Labor Day

10/11/07 7 Columbus Day

11/25/07 8 Thanksgiving

12/25/07 9 Christmas

1/1/08 10 New Year's Day

In the Relationships Graph, create a relationship between StartDate in the DATES table and Date in the HOLIDAYS table. Create another table occurrence for the DATES table (DATES 2) and the HOLIDAYS table (HOLIDAYS 2) and create a relationship between EndDate in the DATES 2 table and Date in the HOLIDAYS 2 table.

Create the following fields in your main table:

Lookup Low (Number, lookup)

Lookup starting from the DATES table from related table "HOLIDAYS", copy value from field "::Counter"

If no exact match, copy next lower value

Lookup High(Number, lookup)

Lookup starting from the DATES 2 table from related table "HOLIDAYS 2", copy value from field "::Counter"

If no exact match, copy next lower value

These two fields determine the number of holidays that have occurred between StartDate and EndDate. For example, StartDate = 6/15/07 and EndDate = 7/15/07. 6/15/07 looks up into HOLIDAYS, and no match is found. However, the next lower value is 5/31/07 (Memorial Day), and 4 is returned from Counter. 7/15/07 looks up into HOLIDAYS 2, and no match is found. However, the next lower value is 7/5/07 (Independence Day Observed), and 5 is returned from Counter. Subtracting Lookup Low from Lookup High (5-4=1) returns the number of Holidays that have occurred between 6/15/07 and 7/15/07.

[Vaughan]

Why don't you post your efforts to date. Then we can see your errors and correct them.

A much better learning experience for you and others! :

[comment]

The method you describe was probably meant for version 6 or lower. It is easier in version 7 and higher - define your relationship to the Holidays table as:

Main::StartDate ≥ Holidays::Date

AND

Main::EndDate ≤ Holidays::Date

A calculation in the Main table =

Count ( Holidays::Date )

will return the number of holidays in range.

---

P.S. Please remove your duplicate post here:

http://www.fmforums.com/forum/showpost.php?post/256208/

Edited June 25, 2007 by Guest

[hfinger]

This is an elegant, yet simple solution to the problem. It works great.

ONE ISSUE HOWEVER! The greater than and less than signs (I believe) are backwards. When I reversed them everything worked perfectly.

Should be:

Main::StartDate ≤ Holidays::Date

AND

Main::EndDate ≥ Holidays::Date

That looks for holidays between the start and end dates.

The method you describe was probably meant for version 6 or lower. It is easier in version 7 and higher - define your relationship to the Holidays table as:

Main::StartDate ≥ Holidays::Date

AND

Main::EndDate ≤ Holidays::Date

A calculation in the Main table =

Count ( Holidays::Date )

will return the number of holidays in range.

---

P.S. Please remove your duplicate post here:

http://www.fmforums.com/forum/showpost.php?post/256208/

[comment]

You are right. I just wanted to see if anyone would notice in less than 4 years... :P