Solved

# Trying to build a solution to account for paid holidays that won't have to be updated every year

Posted on 2011-03-14
441 Views
Last Modified: 2012-05-11
Here is the script I have been using to calculate which pay period to add holiday hours to and how many hours to add:

If [Truncate(WeekOfYear(Time Cards::Begin Week)/2;0) = "26"]
Set Field [Time Cards::Paid Holidays; "8:00"]
Else If [Truncate(WeekOfYear(Time Cards::Begin Week)/2;0) = "7"]
Set Field[Time Cards::Paid Holidays; "8:00"]
Else If [Truncate(WeekOfYear(Time Cards::Begin Week)/2;0) = "11"]
Set Field [Time Cards::Paid Holidays; "8:00"]
Else If [Truncate(WeekOfYear(Time Cards::Begin Week)/2;0) = "13"]
Set Field [Time Cards::Paid Holidays; "8:00"]
Else If [Truncate(WeekOfYear(Time Cards::Begin Week)/2;0) = "18"]
Set Field [Time Cards::Paid Holidays; "8:00"]
Else If [Truncate(WeekOfYear(Time Cards::Begin Week)/2;0) = "23"]
Set Field [Time Cards::Paid Holidays; "16:00"]
Else If [Truncate(WeekOfYear(Time Cards::Begin Week)/2;0) = "25"]
Set Field [Time Cards::Paid Holidays; "16:00"]
Else
Set Field [Time Cards::Paid Holidays; "0:00"]
End If

The problem is that I have to recalculate which week of the year the holidays fall and change this script every year.
0
Question by:ARule
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
4 Comments

LVL 12

Expert Comment

ID: 35128903
Put this information in a table and drive any calculations from it.  This allows you the most flexibility as you can delete, add and change dates as you see fit.
0

LVL 25

Accepted Solution

Will Loving earned 500 total points
ID: 35129043
To use a calculation which determines the date of each holiday for a given year, along with the week that it falls in that year. I've done something similar for a calendaring system which calculates specific dates that a school is closed. Some dates are easy, for instance July 4 or New Years Day because they always fall on a known date. Other holidays are trickier, such as Thanksgiving and Presidents Day. Easter and the Jewish Holidays are really interesting because they rely in part on a lunar based system.

For all of these I recommend checking out the custom Functions for calculating holidays at http://www.briandunning.com/filemaker-custom-functions/  - just search on the word "holiday". You will need FileMaker Advanced to actually install the Custom Functions, but you can use the calculations directly if you wish. If you setup a calculation for the various holidays you can use a Global field for the year and just update the Year field annually.
0

Author Comment

ID: 35129517
Awesome... thanks!

I am creating the calculations in a separate table "holidays" and made the field yearNumber=Year(Get(currentDate))

If I change my open script to create a new record in this table everytime a new timecard is created, I should not have to worry about even updating the year field annually, correct?
0

LVL 12

Expert Comment

ID: 35129676
I took a look at the custom functions (and there are some good ones) but it seems they all want a list of holiday dates as one of the parameters.  While a single field with all the holiday dates would work, I still would put them in a table and pass them to the functions using the List ( Holidays::Date ) function.

I've been using a "Calendar" table for a few years now.  In this table I have all Dates and a break down of that date (Day in year, Week in year, month in year, quarter, day of week, etc...  and if you use a fiscal calendar it really starts to pay off.  In all there are about 50+ fields in my table that relate to a specific date. I currently have about a hundred years of dates to choose from. )  One of the fields is Holiday_Flag.  I can set the flag and now every holiday can be listed.

I found a great article about building and using a Calendar table.  It's focused around SQL Server, but hopefully it will make some sense to you.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Title # Comments Views Activity
Filemaker WebDirect Deployment 1 681
FileMaker Pro 7.5, Windows 7 Pro 64 bit and ODBC 4 453
change filemaker server web direct port 1 210
sending mail from filemaker pro 10 5 62
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
###### Suggested Courses
Course of the Month2 days, 18 hours left to enroll

#### 752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.