Allocating contributions against a specific charitable pledge

[David_Carico]

I’m building a base to track contributions and multi-year pledges to a charity. Am wrestling with how to apply gifts against a specific person’s pledge when someone has more than one pledge. Example: Bob Smith pledges $100,000 to a building project for a new HQ, then 6 months later makes another pledge of $250,000 to endow scholarships. Each pledge might have a separate payment schedule (e.g., $33,333k/year for 3 years … or $25k/quarter for 2.5 years, and I need to be able to track payments made against either pledge so I can track the balance.

Have someone using a Form to enter pledges or payments (separate form for each).

Currently have pledges and payments (whether the payment is a one-time contribution or payment on an existing pledge) together in 1 table. Do I need distinct tables for pledges and payments? Other suggestions?

Thanks …

[Ruben_Burdin]

Hi David,

I had the same situation for invoices and received payments. The logic should be similar for your case.
In your case, I would suggest the following:

Have 2 distinct tables, one for pledges and one table for payments.
In the payments table, you would include the amount of each payment as they could figure in your bank account.
In the table pledges, you will have one row per pledge with a column that links to all the payments made against the relevant pledge (via a linked records field, several linked records can be entered). On top of it, you can have a rollup field that will SUM the payment amounts for each pledge. You can then insert a “balance” column with balance=pledge_amount-total_payment_amount.
If some pledgers pay for several pledges at the same time (e.g. in a single transaction of $45k you have $20k for pledge_A and $25k for pledge_B), feel free to split the payment into 2 rows in the payments table. You can link them by specifying the same bank transaction_id in a dedicated “transaction_id” column (repetition of the same transaction_id for both transactions) or just leave a text in a “comment” column.

You can use forms in order to fill the Pledges and Payments tables.

This approach works perfectly for us, we are now using it for over 6months.
Hope it helps!

[David_Carico]

Thanks much. That all makes sense … my biggest challenge (I think) is the key/autonumber when I create a new pledge. I want for it to be something other than just a number, primarily because the person entering payments doesn’t have AirTable access and will need to pick the correct pledge through the form; if I use a formula field of LastName + Pledge Date (or something similar) as the key that’s more helpful to them – but unfortunately, you can’t add a Formula field to a form! I’m going to play around with the structure you suggest, though, to see what I can come up with. Thanks again …