AnsweredAssumed Answered

Aggregate functions not working?

Question asked by on May 2, 2014
Latest reply on May 2, 2014 by erolst

I'm using aggregate functions to calculate the Sum, Average, Standard deviation etc. of some items in a related table.


I have two tables, Items and Summaries


The Items table has 5 fields: Key_ProcessYear, Input_Item1, Input_Item2, Input_Item3 and Input_Item4

The summaries table has 11 fields: Key_ProcessYear, Sum_Item1, StDev_Item1 ... Sum_ItemTotal, StDev_ItemTotal.

I then have a relationship based on Key_ProcessYear




Calculating the Summaries for each item through the relations works fine.

Calculating the StDev for each item through the relation also works fine.


As I suspect you all know, doing something like Sum( Items::Input_Item1 ; Items::Input_Item2 ; Items::Input_Item3 ; Items::Input_Item4 ) does not give me the sum of Summaries::Sum_Item1 + Summaries::Sum_Item2 + Summaries::Sum_Item3 + Summaries::Sum_Item4.

It actually returns Items::Input_Item1 + Items::Input_Item2 + Items::Input_Item3 + Items::Input_Item4.




I know I can write a script to perform the calculation and run it everytime needed.

That is everytime I display the layout containing the aggregate results and everytime I change a value in the items table if a layout showing calculations based on the edited value is being displayed...


But is there really no simpler solution?