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
437 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

19 Experts available now in Live!

Get 1:1 Help Now