3 Replies Latest reply on Mar 19, 2009 1:41 PM by Orlando

    Define a Total Item on Parent based on Children's Type

    LeoCA

      Title

      Define a Total Item on Parent based on Children's Type

      Post

      We need help on creating items on parent (Budget) based on children's (Funds) record type.

       

      Funds have two columns fund_type and amount (in addition to referential keys). The values for fund_type are:

          IF - Initial_Funding

          AF - Additional Funding

          ...

       

      There are several records for each fund_type.

       

      We want to define Total_Initial_Funding, Total_Additional Funding,... on Budget.

       

      We tried

       Total_Initial_Funding=Sum (If (Funds::Fund_Type="Initial_Funding";Funds::Amount ;0) )

      It sums up all amounts in the Funds not just for "Initial_Funding".

       

      Any idea how we can accomplish this.  Thanks.

       

      LeoCA

       

        • 1. Re: Define a Total Item on Parent based on Children's Type
          Orlando
            

          Hi LeoCA

           

          One option for this would be to place the summary calculation in the Funds table.

           

          It will involve having a calculation for each category and then a summary referencing each calculation, but this will do what you want.

           

          So in your Funds table create a calculation:

           

          AlertType_Initial_Funding (Number) 
           
          If ( fund_type = "IF" ; Amount ; 0 ) 

           

          And repeat this for each category you have.

           

          Next create a field, called 'Total_Initial_Funding' and make it a Summary that is set to 'Total of' and point it to 'AlertType_Initial_Funding'

           

          And again repeat for each category.

           

          Now on your Budget record layout drag the Total_... fields onto the layout and they should sum up each of the categories for you.

           

          Another approach would be to use a recursive Custom Function to loop through and calculate the totals from the Budget table, but this is the simplest approach.

           

          I hope this helps and do let me know if anything is unclear or need further explanation. 

           

          • 2. Re: Define a Total Item on Parent based on Children's Type
            LeoCA
              

            Thank you Orlando. You always give me information relevant to my question.

             I modified your direction a little and it seems working fine for us now.  This is what we do:

             

            ON Funds Table - define items:

             

              IF_Amount    Calculation   =If ( fund_type = "IF" ; Amount ; 0 )  

              AF_Amount   Calculation   =If ( fund_type = "AF" ; Amount ; 0 ) 

              CO_Amount   Calculation   =If ( fund_type = "CO" ; Amount ; 0 ) 

              ....

             

            ON Budget Table - define items:

             

              Total_IF_Amount    Calculation   =Sum (Funds::IF_Amount)

              Total_AF_Amount   Calculation   =Sum (Funds::AF_Amount)

              Total_CO_Amount   Calculation   =Sum (Funds::CO_Amount)

              ...

             

            We are not too comfortable with TOTAL on children's records.  How does TOTAL of Summary type work?  Any difference from Sum Function of Calculation Type?

             

            Thanks.

             

            LeoCA

            • 3. Re: Define a Total Item on Parent based on Children's Type
              Orlando
                

              No worries, glad to help.

               

              Either option will work for what you are trying to achieve, for getting a total of records through a relationship both work the same way. 

               

              If you wanted to have a total of a found set of records, in the table you are in, then a Total of Summary would work better and if you were setting up SubSummary reports Summary fields would be the way to go.