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!
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!
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 …
