1 2 Previous Next 19 Replies Latest reply on Dec 16, 2013 8:53 AM by philmodjunk

    Time off acrruals

    EP

      Title

      Time off acrruals

      Post


           Trying to finish a Human Resources file.  I have a Personnel table and a Personnel history table.  I am trying to track vacation day accruals.  I have a TimeOff table (which I think I might merge with the Personnel History table) that allows a user to enter Time Off Events.  On the Personnel table, I have a field called TimeOffAllowed and a field called EffectiveDate- these fields allow the user to enter the amount of vacation time an employee is allowed and went it when into effect.  I need help in creating a calc field that will use those 2 fields to determine how much time an employee has accrued (monthly accruals). Thanks

        • 1. Re: Time off acrruals
          philmodjunk

               The first key detail is whether the amount in TimeOffAllowed is ever reduced. If you simply increase the total in this field every time the employee receives additional vacation days or hours, then the relationship between Personnel and the related table (either Personnel History or TimeOff Allowed depending on whether you merge these two tables) can be used to sum the number of days or hours of vacation time used by each employee.

               A calculation field defined in Personnel can compute the total hours using the Sum function:

               Sum ( TimeOffAllowed::VacationTimeUsed )

               Or you can define a summary field in TimeOffAllowed (or PersonnelHistory if you track this in that table) to compute the total of VacationTimeUsed and then, when you refer to that summary field from the context of Personnel, it will also return the same total. Such a summary field can also be used in summary reports based on the TimeOffAllowed/PersonnelHistory table to produce a summary report of each employee, when they took time off and how much time they have remaining.

               And FileMaker 12/13 users can use sum in a SQL query via ExecuteSQL to also compute such a sum.

          • 2. Re: Time off acrruals
            EP
                 

                      The first key detail is whether the amount in TimeOffAllowed is ever reduced. If you simply increase the total in this field every time the employee receives additional vacation days or hours, then the relationship between Personnel and the related table (either Personnel History or TimeOff Allowed depending on whether you merge these two tables) can be used to sum the number of days or hours of vacation time used by each employee.

                 TimeOffAllowed will change if the employee gets a formal raise (i.e.- an employee has an annual review and is granted an increase). 

                 CORRECTION: TimeOffAllowed is in the Personnel History table.

                 I plan to use TimeOffAllowed and EffectiveDate in a portal.  When a new record is entered, this would have to correspond with the Accrual Calc field.  For example, Mary starts employment on 1/1/13 and gets 12 Vacation days.  Starting 7/1/13 she will begin to receive 24 Vacation days per year.  I would need the calc to reflect an accrual rate of 1 day per month for January through June, and then 2 days per month from July through December (total of 18 days for that year).

            • 3. Re: Time off acrruals
              philmodjunk

                   You can still add a "total time off allowed" field to Personnel that uses Sum (PersonnelHistory::TimeOffAllowed) to compute the total instead of manually updating the total in a number field in Personnel. A relationship to a new Tutorial: What are Table Occurrences? of PersonnelHistory can be used for this purpose in order to exclude data recorded in PersonnelHistory that has an effective date that is still in the future.

                   Personnel::__pkPersonnelID = PersonnelHistory|Current::_fkPersonnelID AND
                   Personnel::cToday > PersonnelHistory|Current::EffectiveDate

                   Define cToday as an unstored calculation field that returns a Date data type:

                   Get ( currentDate )

                   If you are using FileMaker 12 or newer, you also can use ExecuteSQL to compute this total without needing to add the PersonnelHistory|Current table occurrence to your relationship graph.

              • 4. Re: Time off acrruals
                EP

                     This all makes sense Phil.  The only thing I'm  unsure of is how to stop a record from accruing.  For example, using the same example above (Mary), how would I get the record to stop accruing past June so the new accrual rate (new record) can be used to accrue at the new rate.  Should I create a TerminationDate alongside the EffectiveDate field?

                • 5. Re: Time off acrruals
                  philmodjunk

                       What happens to unused, accrued time off? Is it lost, retired with financial compensation or does it "roll over" into the next year?

                       Why would such a change take place in June? Is this the start/end of a fiscal year with your organization?

                  • 6. Re: Time off acrruals
                    EP
                         
                              What happens to unused, accrued time off? Is it lost, retired with financial compensation or does it "roll over" into the next year?
                         
                               
                         
                              Why would such a change take place in June? Is this the start/end of a fiscal year with your organization?
                         Unused time off is either lost or retired w financial compensation depending on employee type. It is never rolled over into the new year.  
                         Such a change may happen during an employees annual review if they are eligible for an increase.
                          
                         Thanks
                    • 7. Re: Time off acrruals
                      philmodjunk

                           And why did you specify June in your example? was this due to June being the start of your fiscal year and thus the date that all accrued time off totals have to reset to zero?

                           If this can be tied to a specific year or fiscal year, you can incorporate additional match fields into your relationship so that you only match to Personnel History records for the current Fiscal year that do not have a date still in the future.

                      • 8. Re: Time off acrruals
                        EP

                             I just chose June to keep the example simple (at least that was my intention).  Accruals reset every 1/1/XXXX.

                             In the attached example I found a way to change the accrual rate mid year.  

                             The only problems I am having at the moment are:

                             1-in the top box (time off records), I only want to get a SUM of days used ONLY when the Type = "PTO" and haven't been able to successfully do this with an IF Statement.

                             2-My Days Accrued column in the lower box only accrues if a full month is detected.  My formula is the following:

                             (PTOAllowed / 12) * ((Year(PTOTermDate + 1) - Year(PTOeffectDate + 1)) * 12 + (Month(PTOTermDate + 1) - Month(PTOeffectDate + 1)) - If (Day (PTOTermDate + 1) = Day (PTOeffectDate); 0; If (Day (PTOeffectDate + 1) < Day (PTOeffectDate+1); 1; 0)))

                             Is there any way to "pro-rate" a month so that employees that start/end mid month get accrued credit for the partial month?
                              
                             I plan to use filtered portals for my SUM fields below each portal that filters by date above.  This should solve the issue of resetting accruals at the beginning of the new year if either (a) the HR user inputs a new PTO allowed record for each employee at the beginning of each year, or I come up with a script to do this automatically.  
                              
                             Thoughts?

                              

                        • 9. Re: Time off acrruals
                          EP

                               In the above post, please excuse the poor layout.  I like to get things working first and then make it look nice.

                          • 10. Re: Time off acrruals
                            EP

                                 Any additional help would be appreciated. I am still a bit stuck.

                                 Thanks

                            • 11. Re: Time off acrruals
                              philmodjunk
                                   

                                        I only want to get a SUM of days used ONLY when the Type = "PTO"

                                   Add a calculation field:

                                   If ( type = "PTO" ; Days )

                                   Then define your summary field to sum this field instead of the Days field.

                                   

                                        Is there any way to "pro-rate" a month

                                   Day ( DateField ) / Day ( date ( Month ( DateField ) + 1 ; 0 ; Year ( DateField ) ) )

                                   Will compute the fraction of the current month represented by the date in DateField. If there is 30 days in the current month and it is the 30th day of the month, the above ratio evaluates as 1. You can multiple the amount of PTO to be accrued for one month by this ratio to get a prorated figure.

                              • 12. Re: Time off acrruals
                                EP
                                     

                                          Day ( DateField ) / Day ( date ( Month ( DateField ) + 1 ; 0 ; Year ( DateField ) ) )

                                     

                                          Will compute the fraction of the current month represented by the date in DateField. If there is 30 days in the current month and it is the 30th day of the month, the above ratio evaluates as 1. You can multiple the amount of PTO to be accrued for one month by this ratio to get a prorated figure.

                                     I used this formula to understand how it works, and I do now.  I just can't figure out how to incorporate this into my original formula of counting the months between the Effective Date (PTOeffectDate) and the Stop Date (PTOTermDate):

                                     (PTOAllowed / 12) *
                                     ((Year(PTOTermDate + 1) - Year(PTOeffectDate + 1)) * 12 + (Month(PTOTermDate + 1) - Month(PTOeffectDate + 1)) - If (Day (PTOTermDate + 1) = Day (PTOeffectDate); 0; If (Day (PTOeffectDate + 1) < Day (PTOeffectDate+1); 1; 0)))

                                     Do I need an IF statement to determine if the date in either field is a partial month?

                                     Thank you,

                                • 13. Re: Time off acrruals
                                  philmodjunk

                                       I really haven't been able to parse out all the details of your calculation. That portion of this calc that represents the accrued PTO for just the current month should be multiplied by my suggested ratio. If this is a "full month", you are multiplying by 1 and the amount isn't changed. But you may want to round a value here so that you aren't computing really small fractions of a month.

                                       Hmmm, come to think of it, I have this fraction backwards. We don't want the ratio of the day of the month to the days in the month. You want:

                                       (Days in month - day of month ) / days in month

                                       In otherwords, the closer to the end of the month that an employee is hired, the smaller the fraction of the total monthly PTO should accrue.

                                       In FileMaker, you could use:

                                       Let ( D = Day ( Date ( Month ( DateField ) + 1  ; 0 ; Year ( DateField ) ) ) ;
                                               (D - Day ( DateField ) ) / D
                                             )

                                  • 14. Re: Time off acrruals
                                    EP

                                         Yea, it was backwards, now it makes sense.

                                         My original formula basically finds the number of months between the 2 date fields and multiplies by the acdrual rate.   Accruals will happen at each month's completion, so would I need your pro-rated formula to apply to the first month IF not the 1st of the month (for employees not hired on the 1st of a given month).  I'm thinking of an IF statement that uses your pro-rated formula only if the 1st of the month is not detected in the start date (PTOeffectDate).  Does this sound like a legit way to do this?

                                    1 2 Previous Next