Show the summary field of many transactions by cost centre in one table, and the annual budget of that cost centre (from another table) alongside it and the variance between these amounts

Hi Michael,

Looking at your file, it seems that all you need is a simple Sum() calculation in TCostCentre to calculate the sum of each related Cost Centre, e.g.:

SumExpenses = Sum( TMoney::ActualAmount )

Because the relationship between TCostCentre and TMoney is based on the name of the Cost Centre (CCid), that calculation will give you the total, in the CostCentre table for each of the 7 records.

To calculate your variance, you would create an additional calculation field in CostCentre called Variance that calculates the difference between the "Budget" field value for that Cost Centre and the Sum of Expenses (in the calculation above).

Variance = Budget - SumExpenses


It's important to understand the difference between using a Calculation such as Sum( TMoney::ActualAmount ) - for the total - or Count( TMoney::ActualAmount ) - for the number of related TMoney records, and a Summary Type field. Many similar calculation fields like Sum(), Count(), Avg(), List(), etc. are most often used to give the aggregate amount of a group of RELATED records in another table.  A Summary field is generally used to provide similar information  within the SAME table and in particular on reports that summary totals and sub-totals.

If you want ONLY the totals for each Cost Centre, then the Calculation field based in TCostCentre will give you that number. However, if you want to sort and display not just the totals but also then individual TMoney records that make up the total for each CostCentre, then you would create that report in TMoney and use Summary Type fields to sub-total and total those amounts, creating your report to include Body, Sub-Summary and Grand Summary "Parts" with the Summary fields appearing in the Sub-Summary and Grand Summary areas and the regular fields appearing in the Body Part.

Two tips:

1. For clarity, it's best to have your key fields use the same or very similar names. This allows you to quickly determine what the field is and whether they relationship is setup correctly. e.g. You have CostCentre::CCid related to TMoney::MoneyCC. I would use CCid for both tables.

2. If you are going to use text values such as Admin, Books, etc. for your Cost Centre key fields, it's best to lock down the selection value. You currently have a dropdown menu in TMoney for the MoneyCC/CCid which would allow someone to enter something different. Change that to a popup menu and also limit value entries for that field to your Cost Centre value list by using the Validation tab in the Field definition and limiting entries to values in the Value list.

Hi Will

Thanks for the advice on limited to pop ups. That protects my data.

I cannot get totals or sums of the ACTUAL amounts to appear alongside the BUDGET amounts.
I would have thought that adding the SUM(TMONEY::ACTUALAMOUNT) to the CostCentre table would show it but it shows nothing.
When adding a field for totals to a table I get to choose which field I want the totals on, but this doesn't give the choice when wanting to choose from another table

I do get one total for the actuals overall but not for each cost centre.

What am I missing?

Thanks

Send me a copy of your file; I can probably tell very quickly. If you want send it privately, click on my name and then use the Send option under my profile.

Here it is Will

Thanks
khub-30-jul.fmp12

Thank you Will
I appreciate your patience and commitment to providing the solution.
All your comments about the design etc have been very helpful with this project and for the future.

Thanks again
Michael