Assistance with 2 calculations

[Skiffy]

Hi everyone,

I'm updating a database (originally created in FMP5)New HR.fmp12.zip for our HR Manager and not having a lot of success with a couple of calculations.

In the attached file (Username = Manager, password = pass), the main layout is "Employee Info".

The calculation for Years with firm is presently "If(Work Status ="Active";(Today-Date of Hire)/365;0". This gives a rough length of employment but isn't accurate due to leap years. Nor does it provide a calculation for Inactive employees. I would like to give the result as 4 years 3 months - days aren't necessary. For Active employees the calculation should be today - Date of Hire, and for Inactive employees it should be Date of Termination - Date of Hire.

The second calculation is for the various types of Leave: Sick, Vacation, Leave Without Pay and Other. I have set it up that the previous years' vacation etc is set up to archive manually once a year (around NewYear). The Archive field is set to "No" upon record creation. So the calculation for reporting should be based on Archive = "No". The two fields that need the calculation are Remaining Vacation and Vacation Days Used. This is what I have tried for Vacation Days Used, but it is still totalling both archived and unarchived records: If(Vacation::Archive="No";Sum ( Vacation::Vacation Length );0).

Many thanks for your assistance.

Skiffy

[LaRetta]

Hi Skiffy!

Your first calculation ( to get result as x years, x months ) would be:

Let ( [

end = Case ( Work Status= "Active" ; Get ( CurrentDate ) ; Date of Termination ) ;

x = 12 * ( Year ( end ) - Year ( Date of Hire ) ) + Month ( end ) - Month ( Date of Hire ) ;

y = Div ( x ; 12 ) ;

m = Mod ( x ; 12 )

] ;

Case ( y ; y & " year" ) & Case ( y > 1 ; "s" ) &

Case ( y and m ; ", " ) &

Case ( m ; m & " month" & Case ( m > 1 ; "s" ) )

)

Notice the yellow field on your Employee Info layout (at the bottom). Also, you were using Today which is an indexed creation date. If you truly want it to update, it should be unstored calculation with Get ( CurrentDate ) or you can just use Get ( CurrentDate ) right within your calculations as I have done.

For your second calculation, you must filter the relationship to only Archive = NO. I added a calculation string with the words NO which is used in the new table occurrence to Vacations~current. You then simply summarize this instead. To clearly see the fields I modified, I have changed their names by adding zz at the beginning so they pop to the end of HR_Information for easy review. Let me know if you have questions or if I misunderstood your need. :^)

New HRrev.zip

[Skiffy]

Hi LaRetta and others.

Thanks for the first calculation for Length of Service. That is working fine.

However, the other is still not working. The fields are still calculating both the archived and the non-archived leave, even in the revised database you sent. See the Vacation for Jane. She has 4 days of archived leave and 4 days of leave for this year. On the HR Info layout, I only want to see the sum of the leave she has taken for this year (the non-archived leave).

Many thanks,

Karen

[LaRetta]

Hi Karen,

Sorry, I had neglected to re-point both calcs to the new table occurrence from: Sum(Vacation::Vacation Length) to: Sum(Vacations~current::Vacation Length)

[Skiffy]

Yay! It works!

Many thanks,

Karen