Portals - multiable fields

[Davy]

I run a Sick Children's Fund where I have a parent table listing the clients (children) and their details including location. In a related table (related by Serial Number)I record disbursements to each client. Each client usually has many individual disbursements over a several month period.

I am using a portal to record each line of disbursement which creates several records (called Amount) for each client. I use a summary field for the total amount given each client.

I need to create a report grouped by location and sub summarized by the amount spend in each location. I can group the location but the best I can get the report to do is list each record of each client and I cannot produce neither a sub-summary of location expenses nor a grand summary.

I have tried using calculation fields instead of summary but still with no luck.

Can anyone help? I have spend five full days trying to resolve the problem.

Dave

[Fenton]

There are really 2 ways to do "summaries." Which you use depends on where you're looking from. But since neither work well without proper relational structure, and each is easy if it that is in place, you may as well have both.

The first method is to use the Sum (relationship::number field) function via relationships to get the total. In 7 you can look "thru" table occurrences to the end table where the amounts are.

So you need a Locations table, with 1 record per location. If there are many locations this can be obtained by exporting the sorted records "summarized" by location. Then import the list of unique locations.

This should be a proper table with an auto-entered serial ID. Children should be the same, unique records with an ID. Anything less is bloating the file unnecessarily and asking for trouble in the long run.

The Locations table is related to the Children table via the LocationID. The Children table is related to the Disbursements table via the ChildID. Therefore the Locations table could reach through to Disbursements to summarize by location.

This however has an implicit assumption that a child cannot move to a different location. If that is possible, then you need a more direct tie between Locations and Disbursements.

The LocationID can be auto-entered into each Disburse record at creation. This is useful in 2 ways. One is that you can then create a TO directly tied to Locations, so it's accurate if the child switches locations.

The other is that you can produce a report in Disbursements. Since you have the ChildID and the LocationID you can use an Amount Summary field, and Subsummary parts to report any way you want to.

I've included the simple version (children can't move) and the more accurate, they can.

SummarizeLocations.zip

[Davy]

Thank you Fenton, your suggestions make sense and I will try them and let you know.

Regards Dave