3 Replies Latest reply on Jul 19, 2017 11:46 AM by TKnTexas55

    Calculation from Related Records

    TKnTexas55

      I am sure I am missing the obvious on this.  I have a master record (Contract) that can have anywhere from one to five (but no limit really) related records of services to be provided.  Each related record has a start date and end date with a status (active or inactive).  I want a calculated status on the parent record that swtiches to Inactive when all related records are inactive.  I am just not seeing the calculation for this.  Currently I have it manually toggled on the related records as well as the master record.

        • 1. Re: Calculation from Related Records
          Ralph_Buechle

          you need a formula field in related records of service to figure out if the record is active, which could be:

           

          "status" : if( Get(CurrentDate)≥ start date AND Get(CurrentDate)≤ end date; 1 ; 0)

          (calculation field result needs to be set as number)

           

          on the Master record you set a calculation field (result as needed, but text if you take the example below:

          if ( sum ( related_table::status) < 1; "inactive"; "active")

           

          hope that helps

           

          Ralph

          • 2. Re: Calculation from Related Records
            philmodjunk

            In contract, you can define this unstored calculation field:

             

            If ( IsEmpty ( FilterValues ( List ( Services::Status ) ; "Active" ) ) ; "Inactive" ; "active" )

            • 3. Re: Calculation from Related Records
              TKnTexas55

              In my related file I have a field for the status that I can toggle off or on.  As a service is added to Active and when a service is cancelled it is marked in active.  When all services have been cancelled, then I wanted the Master record to show Inactive. 

               

              Thank you both for the quick responses.  Thank you Phil for the solution I was drawing a blank on.