I am trying to calculate available hours of vacation after each pay period. The calculation would be dependent on a StartDate field and Today's Date and an AccrualRate field (each employee accrues X hrs per period) - DaysTaken, but I'm not sure how it should be constructed.

This is probably the sort of things one should pay for but I work for a non-profit so we try to get as much as we can for free! Or if you can point me to a good template that would be appreciated.

thanks,

Chuck

Describe for us how you wish the calculation to work, there is no "standard" policy for accrual, you'll have to tell us. Also describe what each record represents in your file(s).

-bd

As I understand it, the accrual rate needs to be a field that can have different values for different employees, since the rate is either 3.33, 4 or 5 hours per pay period (which to make matters more complicated, is paid on the 1st and the 15th, so the number of days varies).

Also, each record in the database represents individual employees.

thanks for any help,

Chuck

You could try something like this:

http://www.FileMaker.com/ti/101879.html

The above link describes a method using repeating fields. I'd probably steer clear of that and use a related database (by employee id) where each record represents a pay period. We'll call the relation "accrued" and be sure to allow creation of related records.

There are various methods one can use to add records to a related file. One way is to put a portal in your employees database, and set up a script that goes to the last row of the portal then set the field "accrued::rate" to the employee's rate. This will create a new record in the accrued file. Now in your employees file create a calculated field, "Hours accrued" that is Sum(accrued::rate).

So, call me crazy, but now I'd probably set up a 3rd file for the vacation hours used. Set up as above a relation, a portal, a script, and a calculated total "Hours used" that is Sum(vacation::hours).

Finally, you simply make a calculated field Hours accrued - Hours used.

This method doesn't really need to consider Status(CurrentDate), since the hours will always be up-to-date.