Hi.  

I have a ton of calculations in my Fire Dept database yet I can't seem to get this simple one to return what i expect.  

I have a "Date of Hire" field with a year in it.  I am trying to have a calculation field return a simple year.  ie. 12 years.  

I had found the calculation here in the forum for the calculation result of XX Years, XX Months, XX Days but that doesn't work when the fire dept only enters a year of hire instead of a full date.  

 

Here is what I was trying to use.  

 

Thanks in advance for all your time!

Chris 

( year( get( currentdate)) - Date of Hire) & " Years"

Hello,  

My fire dept asked to make changes to our years of service calculation that I don't know how to complete.  

We have a field with DATE OF HIRE (Date field) and we have a DATE OF INACTIVITY (Date field).  The current calculation returns a result in a YEARS OF SERVICE field ie. 7 Years, 2 Months, 15 Days. 

The interesting part is now they want to add in a members past service in another department and also their service in the military. 

What would be a good way to approach this.  Should I add a start and end date field for each (second department service and military dates)?

Or would it be easier to just have them enter an active amount of days or years?  I assume a start and an end date for both military service and previous fire department service is the best way to go but I am going to need some guidance on adding those to the current calculation?   

 

YearsOfService.fmp12

 Probably the best way to go about this is to create a new related table for YearsOfService. Each record in  this table would be one service stint for one member. It would have fields for MemberID, Service Agency (or maybe just a service agency ID if you want to have a separate table for agencies [recommended!] ), StartDate, EndDate, YrsOfService (similar to your current calc). 

This table would be related to Members by Members::ID = YearsOfService::MemberID. Move all the time of service data from the Members table to this table, and add records for former service as needed. Add a field in the Members table for TotalService = Sum(YearsOfService::YrsOfService). (While you can still report YrsOfService with OurFD with a separate TO adding Agency to the relationship).

The advantages of this are:

• Minimal disruption of your Members table.
• Members can have an infinite number of prior service stints, while not bloating the file with lots of blank fields for those who have only 1.
• It never fails, if you set up n instances of prior service in your Members table, someone will come along with n + 1 former services.
• Ease and flexibility of reporting such things as how many members were poached from ThatOtherFD, or total service considering a hiatus between stints

You're learning the mission creep that bosses are famous for (after you fought them tooth and nail to get it in the first place). My former colleague used to call this the "Say, can you make it pink and hang from the ceiling?" syndrome.

Thank you very much Doughemi!. 

I think with that detailed description it is within my ability to make that happen.  I can work on these changes and try things out.   

Just one questions.  How would I deal with the members current stint of membership since there would be no END DATE for that time period?

Thanks

Chris. 

Wherever you have DATE OF INACTIVITY in your current calc, replace it with

Case(
IsEmpty(DATE OF INACTIVITY); Get(CurrentDate);
DATE OF INACTIVITY
)

If I were doing this, I would sort the relationship between Members and YearsOfService by StartDate (Descending) so that the most recent stint can be shown either in simple related fields or at the top of a portal.