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
4
438 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
Comment
Question by:ARule
  • 2
4 Comments
 
LVL 12

Expert Comment

by:North2Alaska
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 24

Accepted Solution

by:
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

by:ARule
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

by:North2Alaska
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
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…
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now