2 Replies Latest reply on Feb 10, 2011 9:06 AM by philmodjunk

    Calculations using dates, help



      Calculations using dates, help


      FMP10 on PC and MAC

      We have a FMP file that employees use in a department that keeps track of their hours worked during a pay period.  Pay period starts on Thursday mornings and ends on the following Wednesday, at the end of the day.  We use this to keep track of employees personal time and vacation time.  Here is where I need help.

      1)  Employees' personal time resets every January 1 of every year.  The maximum amount anyone can ever receive is 40 hours.  How can I get FMP to automatically reset the personal time on January 1 of every year?  I keep track of personal hours used, so I would just need this to go to zero on January 1 of every year, how can I do this automatically?  Personal hours do not carry over to the next year.

      2)  Employees' vacation time resets on the anniversary date of when they started.  I already have every employee's starting date.  If an employee started working on March 8, 2009, how can I get the employee's vacation time to reset on every March 8th?

      3)  The amount of vacation time varies.  Here is the breakdown:
      After 1 Year of Service (from employee's starting date): 40 Vacation Hours (this is 5 vacation days)
      After 2 Years of Service (from employee's starting date): 80 Vacation Hours
      After 5 Years of Service (from employee's starting date): 120 Vacation Hours
      This is the general rule, but sometimes new employees can start with 40 or 80 vacation hours so I need to be able to modify the vacation time if necessary.  How can I get FileMaker to automatically update the available vacation time based on CurrentDate - StartingDate ?  And will I be able to override this and just enter a number for some employees?  I keep track of vacation hours used, so I need this to go to zero on the anniversary of the employees' starting date, how can I do this automatically?  Vacation hours do not carry over after the anniversary of the employee's starting date.

      Does this make sense?  Thoughts? 

        • 1. Re: Calculations using dates, help

          There are various date functions in FMP. The handiest one for you would be :

          DayOfYear (date) which returns the number of days since January 1 (which would = 1 on January 1st, to answer that part). 

          (You need to use 4-digit dates for date functions to work properly.) 

          There is also the function :

          Year (date) which returns the numeric value of the year from the date, e.g. the number 2011.

          Using both of these you can set up Calculation fields to return the Vacation hours entitlement for example.

          For the automated operations, I'm guessing you need a Calculation using Functions that will - if a particular value is returned - run a script to reset the value of given fields. However, you would need someone who has done that to confirm if it's possible. (I've written and run scripts; and I've defined many a complex Calculation, but I've not done both together).

          • 2. Re: Calculations using dates, help

            You've specified a lot in one post. Given the scope, much of this is a generalized outline of what you might do.

            While it's certainly possible to craft a script that resets values in certain fields to zero either on the first of the year or on their employment anniversary, I suggest that you create a new record with the new data instead. This preserves the data from the past year--which may be very important if an employee ever questions the accuracy of your records on personal and vacation time usaage. You could define a record with an employeeID, a date, (Years of employment for a VacationHours table), and either the personal hours or the vacation hours appropriate for that employee. Since these hours fields would be simple number fields, you'd be able to edit them as needed to document exceptions to your general rules. The date and employee fields would then match records in such personalTime and VacationHours tables to the correct employee and time period.

            For entering the actual vacation hours, I'd define a look up table with one record for each combination of Max years employment and Vacation hours. A looked up values field option can then copy these values into new records in the VacationHours table when a new record is created there.

            There are several ways you can trigger a script to run on regular intervals. Server can schedule a script to run. An OS level scheduler can be scheduled to open a FileMaker file with a script that runs, in FileOptions, you can specify that a script can be set to run each time the file is opened. Install OnTimer can set a script to run every X seconds--which could be an interval of once a day--as long as the window that "installs" it remains open.

            Simplest implementation is to set a script that runs each time the file is opened. Such a script first checks to see if it needs to run. Since it runs once a day (vacation hours) or once a year (Personal time), you'll need a table where you record the date the last time the script was run. If the script has already run for that day/year, it just exits. If not, it performs a find for the appropriate employee records and then loops through that found set of records, generating the new vacation hours and personal time records.