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

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

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

...

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

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:

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

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