Totals of Currency Fields in Sub-Summary Part and in Trailing Grand Summary Part
I have a db with four tables: Events with the key field EventID and TableA, TableB, and TableC with the foreign key EventID.
Some events in the Events table are "primary events" and some are "secondary events". A secondary event is related to one and only one primary event. I do not want to split the events into separate tables because the data is most often viewed by event without regard to whether it is primary or secondary.
For the purposes of one report, however, I want to group the events by primary event. To do that I have a field "PrimaryEventID". For primary events, the EventID field and the PrimaryEventID field have the same value. For secondary events, the PrimaryEventID field has a different value (some other EventID).
I have created a second table occurrence called Event2Event and have joined its EventID to the Events table occurrence's PrimaryEventID. The Events table occurrence is joined by its EventID to each of the Tables A, B, and C.
I have created a report based on Event2Event with a sub-summary using a field in Event2Event (a person's name). I have a portal with the related records from the Events table occurrence.
Tables A, B, and C each contain currency fields that I want to display, but, since I can't put a portal within a portal, I created calculation fields in the Events table that are defined as Sum (CurrrencyField). I have those Sum fields from Events plus a couple other fields from Events in the portal. These fields appear to work fine.
Outside the portal, but within the body, I have summary fields for each of the currency fields. These summary fields are defined in Tables A, B, and C, respectively. These fields appear to work fine.
So far so good (although comments are welcome if I have missed something or messed something up).
I want to put fields in the sub-summary part for the total of each of the currency fields for the person on which the sub-summary is based, and in a trailing grand summary part for the total of each of the currency fields for all persons. The summary fields from Tables A, B, and C do not work, and I cannot figure out what will work. I have tried calculation fields and summary fields in Events and Event2Event, but without success.
I am not a beginner with Filemaker, but I am not advanced either. I am using FIleMaker Pro 11 on a Mac.