Sum repetitions from Child

[gaby78]

The attached file is Edoshin’s crosstab demo that I have modified to suit my needs as follows: I have added a Parent table (Category) with 2 fields: Category Name, Amount (Calc Repeat field with 12 reps) and linked it to Expenses by category

I set Amount = to Sum( ) of Expenses::Month field (which is a repeat field) with the hope that repet1 in parent would store the total of repet1 in child, repet2 in parent store the total of repet2 in child etc....

The results are telling me that I am doing something wrong. Could someone please take a look a the file an tell me how to store in each repetition in parent the sum of the equivalent repetition number from child.

SumRepeat.ZIP

[gaby78]

I tried using Get(CalcRepNbr)with GetRepetition() and Sum() with no result.

Any suggestions/hints/comments ?

[The Shadow]

You need to use summary fields to get that behavior, and choose "sum repetitions individually" for the summary.

[Søren Dyhr]

I'm not quite getting why you choose to show it over a relation why not make a found set before making the cross-tab. However if you are shouldn't Sum(expenses... be used, but instead grap the Expense:∑Totals without any aggregate function, and you get the summaries shown ... but it's still not obvious why you would need to use relations here.

--sd

[gaby78]

Thanks The Shadow, the summary field did the trick.

I never thought of using a summary field because I read that the proper way to add child records is to use the Sum () function.

Is referencing a summary field from the Parent perspective a reliable way to aggregate child records ?

And by extention would it be equality OK to use a summary field in formula in parent in place of Sum(of child records)?

Thanks again

[gaby78]

Hello Soren:

Because there are other pieces of info (from parent) that I want to show on the layout and also I do not need a printed report. I prefer a list View from the parent side.

As mentioned in my reply to Shadow I did not know that one could use a summary field from the parent side. I always though that the rule was to use Sum().

[The Shadow]

Is referencing a summary field from the Parent perspective a reliable way to aggregate child records ?

Yes, this is supported and reliable. Related summary fields operate over all related records.

And by extention would it be equality OK to use a summary field in formula in parent in place of Sum(of child records)?

Yes, but one issue is that FileMaker automatically caches summaries, and they are sometimes left stale if the field is not shown in a report (preview mode is fine). This isn't an issue unless you are changing values and need the sum to immediately adjust - then you need to use Sum() instead.

Also, be aware to use a summary field in a calculation you need to use the GetSummary() function in most cases, otherwise it will return the grand-total (I think).

[gaby78]

Thanks The Shadow; greatly appreciated