Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
451 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

581 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