Hierarchy roll up of amount

wings

We want to create a multi-level hierarchy with each level containing a Label and Amount.

This was implemented by us creating a business object called Budget, with attributes Label(PlainText) and Amount(Number). The hierarchy was implemented by adding an attribute called Children(multiple allowed, Peer to Budget) to the Budget object.

Each level Amount is a summation of the lower levels. When we change the Amount on the lowest level our attempt to roll up the Amount does not appear to function.

Our rule to roll up is:

If Budget FROM Budget.Children WAS CHANGED Then
Budget.Amount=SUM ThisBudget.AmountWHERE (ThisBudget IN Budget.Children)

We also tried the same rule without (This) however this didn't do anything.

Any assistance will be greatly appreciated.

tford

Budget.Amount=SUM Child.Amount WHERE (Child IN Budget.Children)

Depending on your situation, you might want to also make it a Dynamic rule.

wings

Thanks Tom

this is what does with your example

SELECT SUM(Budget.Amount) FROM BASTESTDOMAINHORIZON_BUDGET AS Budget WHERE (200310=Budget.ID)

It dosen't join to the budget_ref table and therefore returns 0 (zero)

What I need it to do is the following which when run manually returns 4 rows with a total of $50.00

SELECT SUM(Budget_Children.Amount)
FROM BASTESTDOMAINHORIZON_BUDGET AS Budget
LEFT JOIN BASTESTDOMAINHORIZON_BUDGET_REF AS ER_Budget_Budget_Children ON Budget.ID=ER_Budget_Budget_Children.ID
LEFT JOIN BASTESTDOMAINHORIZON_BUDGET AS Budget_Children ON ER_Budget_Budget_Children.RID=Budget_Children.ID AND ER_Budget_Budget_Children.FIELD_NAME='Children'
WHERE ((200310=ER_Budget_Budget_Children.ID))

I pull the sql's out of the MySQL logs to try and work out what was happening