Filtering totals through Join Table

[Wickerman]

I am creating a system for an organization to trek $ donations by individuals held in a Contacts Table. Usually, donations are one-time events where someone just sends in a check. Other times, an individual "Pledges" a certain amount and pays it off in a series of monthly or yearly deposits.

So I have a Sub-table called "Donations" off of contacts to record all actual payments of $, whether in individual donation or a Pledge "fulfillment". I also have a third table for Pledges on the far side, making the Donations table a "join table":

Contacts < Donations > Pledges

My questions:

1) I don't see any problem with keeping all the $ payments in the same table, whether or not they are part of a pledge. The "simple" donations just don't have any value in the foreign key field for Pledges. Is there any downside to this that I'm not thinking of? (It's just the first time I've come across this situation).

2) I have a summary field in Donations ("DonationTotal") that totals the $ value of donations, so I can display that for each Contact in a portal showing all Donations, whether simple or related to a Pledge. Fine.

Now, what I want to do is have a portal on this same Contacts layout that shows a record for each Pledge, pulling simple data from that table like Pledge title, Total Pledge commitment -- but also want to show the Total paid in "fulfillment" of that pledge, and the amount yet remaining to be paid.

So, the relevant Donation records I need to tool are those records in the join table that connect the Contact to the Pledge. This seems like it should be simple, but I can seem to figure it out. I've tried creating a Calculation field in Pledges that simply pulls "DonationTotal" from the related join table, but that doesn't work. I tried putting a further TO of Donations on the far side of Pledges, thinking maybe of I pulled the DonationsTotal field "through" the Pledges table, maybe that would do it, but no . . .

Can someone point out where I'm going wrong? I have to admit that performing arithmetic functions involving related tables has been a persistent puzzle for me.

[Vaughan]

I'd try to generalise: people make a "donation" (or "pledge" your choice) then make one or more payments towards it.

People -> Donations -> Payments

For many people the "donation" will only have one "payment" record and it's all done at the same time. For others, the donation may have several payment records each with a due date and a due amount, and a received date and received amount. The database can then track whether payments are over-due (ie not marked received) and issue reminders as required.

[Wickerman]

Thanks Vaughn - this seems likely to be a workable and simpler approach, but I'm needing to sift through the implications. One is I'll need to have a field in the Donations table you propose that identifies each records as a "Pledge" or a . . . "Gift" or something that suggests immediacy . . . because the users need to be able to look at the two kinds of situations discretely, so I guess I could make filtered portals using that sort of field to differentiate...

Though I think I'm likely to go that way, it might still be useful for me to understand the 'Totaling' question as I've asked it -- or whether the difficulty of doing such math is in itself a strong argument for taking your > > > approach?

[Wickerman]

Vaughn -- after further consideration I decided to separate the two systems, so my issues are a little simpler now, butI'm still stuck on a Calculation problem. So, I have the structure you recommended ok:

People -> Donations -> Payments

On a People Layout, I have two portals. One is to Pledges. The other is a filtered portal I've set up so that when the user clicks on a Pledge record in the first portal, only the related Payments for that pledge are displayed in the second portal. So far so good.

In the Payments portal, the routine is that the user will enter the projected payments when the Pledge is made, with a Payment Amount, a DUE date and a RECD date. Kind of a payment plan.

In the Pledge portal, I would like to display a "Paid to Date" field which would display the Total of all child Payment records that have a value entered in the RECD field. Then I would have a "Remaining" field that would subtract that amount from the original big Pledge Amount.

This seems like it should be easy, but I'm just not having success. I'm not sure if I should be in the Relationship graph, or using a Summary function in Payments somehow, or what. Could you (or someone) point the right way here? THanks