Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
450 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 25

Accepted Solution

by:
Will Loving earned 2000 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

963 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