Tips for showing summary of records that don't exist

[David Jondreau]

I've got a Class Registration database.

Tables

Students

Classes

Registrations

Payments

Registrations is a join between Students and Classes and a one-to-many with Payments.

I'm trying to create a balance sheet for a class that will show all the Payments made towards a Class, sorted by Student with a summary of those Payments.

Right now, I've got a layout based on Payments. I GTRR from Class to Registrations to Payments and sort by student name. The total is in a summary field showing amount paid. That's almost fine. Students can register for a class without making a payment. So the "Balance Sheet" won't show any of those students.

I could create a ghost payment of $0 for every registered student, but that messes up the logic and data validation and seems non-normalized.

I could also create a text calc in Payments concatenating the fields I want (Date, Amount, Payment Method) and then another text calc in Registrations that pulls a List() of related Payments and base my layout on Registrations using calc fields to summarize rather than layout summaries, but that seems very inelegant.

Any ideas? Thanks.

[comment]

I don't know of a good solution for this. If you need to include all registered students, then obviously your report should be based on Registrations. Using List() or a custom function to enumerate the individual payments seems like a decent solution to me - but I understand the printing of sliding portals has improved a lot since v.7, so I would look into that first, since it requires practically no additional resources.

[David Jondreau]

Print portals? Feels like you're advising me to use repeating fields or field a left-handed shortstop.

I'll give it a go.

[comment]

Feels like you're advising me to use repeating fields

It wouldn't be the first time.