6 Replies Latest reply on Aug 6, 2013 12:54 PM by ccameron

    Correcting a Programmer's Error?

      Title

      Correcting a Programmer's Error?

      Post

           A former consultant entered the following formula to calculate the total number of years a committee member has served, based on start date and [supposedly] calculated to the day the record is accessed.  What makes it difficult is that some members quit for a few years, then came back to serve again so there is a "break in service" element as well.  The field for TOTAL YRS. SERVED is not adding up correctly when we double-check it manually.  What is wrong with this formula and how can I fix it?

            

           If(IsEmpty(end_of_service);

           Round(current_date_check;2);
           Round(get_date_check_check;2))

           The fields being used are: START DATE     BREAK IN SVC:From      BREAK IN SVC:To     TOTAL YRS. SERVED

            

        • 1. Re: Correcting a Programmer's Error?
          philmodjunk

               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?

          • 2. Re: Correcting a Programmer's Error?

                 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

            • 3. Re: Correcting a Programmer's Error?
              philmodjunk

                   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.

              • 4. Re: Correcting a Programmer's Error?

                     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.

                • 5. Re: Correcting a Programmer's Error?
                  philmodjunk

                       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

                  • 6. Re: Correcting a Programmer's Error?

                         Interesting!!!!  That just may be the ticket!