Calculation to add sum of child amounts to parent total amount.

[John_Titor]

Hi Guys,

I have a PARENT and CHILD list, the CHILD list has a field called AMOUNT and the PARENT list has a field called TOTAL.

I need to add the sum of the amount fields to the total amount field.

I can use java script to increase the value of the total amount each time a child is added. The user will always create a parent record and add child records to it. So that is an option, but I have no idea what the function would look like. I haven't used any calculations in java script in alpha yet.

Anyone able to help me or point me in the right direction?

Thanks a lot.

[lgrupido]

Re: Calculation to add sum of child amounts to parent total amount.

Is this a LIST, a GRID, or a UX?

Is the Total field in the parent a strictly calculated field or is it an actual field in the parent table?

[John_Titor]

Re: Calculation to add sum of child amounts to parent total amount.

Hi sorry. It is a list. The total field should ideally be an actual field in the parent table. I just want to know how to enter the newly created child record's amount value into a variable and how to apply it to the parent tables field.

I did something like this but it doesn't seem like it is working right -
ss+(2016-07-05+at+08.09.25).png

Can I just declare global variable like that up there?

[GGandhi]

Re: Calculation to add sum of child amounts to parent total amount.

usually you get the pointer to the parent then address the field using the pointer prefix then it will work.
i am sure it is the same in the list also, even though i don't have access to those.

[[email protected]]

Re: Calculation to add sum of child amounts to parent total amount.

If you are using an Sql backend I would do the calculations based on a trigger in the database.
What happens if you delete a child row?
What happens if you change the amount in the child row?

After the update/insert of a child I would refresh the parent row to show the newly calculated total.

[lgrupido]

Re: Calculation to add sum of child amounts to parent total amount.

I agree with pieter, above. If you give us more info on your situation, we can make some suggestions on how to write the triggers. (For instance, what database are you using.)

[Davidk]

Re: Calculation to add sum of child amounts to parent total amount.

John, in order to change the Parent Total, you'd need to have the Detail View of the Parent set up. I'll assume you've done this and TOTAL is part of the Detail View.

In the Child List, for the AMOUNT field, in the List Fields dialog, make sure Compute Summary Values is checked.

In the Child List Event, afterClientSideSummaryCompute, add this code...

var amtTot = csSummary['AMOUNT'].total;
{dialog.Object}.setValue('TOTAL',amtTot);

When you click on a row in the Parent List, the Child List will sync and show rows for that Parent. afterClientSideSummaryCompute will fire and the summary value will be written to the TOTAL control of the Parent Detail View.

If you add a row to the Child List, then the event will fire again and the TOTAL control will be updated. Then you can Save and Sync the Parent List to save those Totals (assuming you've saved and sync'd the Child rows).

You need to be a bit careful because Child Adds do not need to be saved and sync'd to affect the Parent - because it's all client-side. However, Child Deletes are different. The row isn't physically removed from the Child until the List is sync'd... at which time the afterClientSideSummaryCompute fires and your Parent TOTAL is updated.

In general, Adds do not force you to perform a callback (which is nice), but Deletes do.

[John_Titor]

Re: Calculation to add sum of child amounts to parent total amount.

Hay DavidK,

You're suggestion works perfectly. Thanks a lot.

Added the
var amtTot = csSummary['AMOUNT'].total;
{dialog.Object}.setValue('RequestSetTotalAmount',amtTot);


Thanks everyone for taking the time to reply on my post.