We'd need to see what expressions were used in the current_date_check and get_date_check_check calculation fields.
WIth the method you have here, what would you do if a committee member had two or more breaks in service?
START DATE Type = Text Options/Comments = Indexed No formula
BREAK IN SVC:From Type = Date
BREAK IN SVC: To Type = Date
get_date_check Type = Calculation Options/Comments = from contacts, =Date (12;31;2007)
get_date_check_check Type = Calculation Options/Comments = ((get_date_check-START DATE)-(BREAK IN SVC_to-BREAK IN SVC_from)) /365
Here's an obvious error: START DATE Type = Text
This field should be of type date. Dates entered into text fields cannot be evalauted as dates in calculations without first converting them into actual dates.
((get_date_check-START DATE)-(BREAK IN SVC_to-BREAK IN SVC_from)) /365
As best I can tell from the information provided, This would seem to be the proper calculation:
( Get ( CurrentDate ) - Start Date - BREAK IN SVC_to + BREAK IN SVC_from ) / 365
I don't see the purpose of Get_date_check in this calculation.
And storage options must be changed to make this an unstored calculation or it will not update automatically as time passes.
And since this calculation only allows for a single break in service, you may find that it is unable to handle all situations. If more than one break in service is possible, then a different method is needed in order to correctly calculate years of service.
Thanks! I will try your suggestion and see what happens. You're right about the multiple breaks. We have had that happen and we had to just make a note and figure it out manually.
You could set up a related table where you log each break in a different record and the you can set up a calculation that allows for an unlimited number of breaks.
Your BreakInService table would need at least these fields:
DateStart, DateEnd (type date)
Difference ; Unstored calculation: If ( IsEmpty ( DateEnd ) : Get ( CurrentDate ) - DateStart ; DateEnd - DateStart ) )
_fkID ; match field back to original table.
You'd then need to add a relationship matching these records to the correct records in your original table. Then the calcualtion would become:
( Get ( CurrentDate ) - Start Date - Sum ( BreakInService::Difference ) ) / 365
Interesting!!!! That just may be the ticket!