Allocate Payment To Multiple Transaction


I built a Purchasing base, which keeps track of our inventory purchases.
The hierarchy is as follow:

Inbound Shipments > Purchase Orders > Purchase Orders Line Items

I would like to start keeping track of payments made against Purchase Orders.

For example: Payment reference ABC123 for $100, is to be split as follow:
$30 Deposit for PO1, $30 Deposit for PO2, and $40 Balance for PO3.

Each payment is comprised of deposits, and balances to allocate between multiple PO’s.

I would like to then find a Deposit field and a Payment field in the Purchase Orders base to be able to do some reporting.

Id appreciate any good ideas!

You need a Payments table, and add the needed fields, like a Rollup in Purchase Orders to be able to know the total paid.

I have similar setup in my Events table: first pay for scheduling the service, sometimes a middle payment if the customer want to split in 3 the payment, and a final payment after the event.

1 Like

Thank you! I figured that much, but the issue is one single payment can be a split between 4-5 different orders, and a single order requires up to 3 payments to be paid in full?

Maybe look at 3 tables. Payments, “Line Items” (or whatever name makes sense to you), and POs.

  • Payments can hold your Payment ref number, and payment total.
  • Line Items would be a bridge between Payment and PO with each line item being linked to a payment and PO with a the details for that. You could categorize each line item (deposit, payment, etc)
  • PO table as you had it.

You could rollup in PO table as mentioned, but also Rollup in Payments table to make sure payment total in Payments matches payment total of the line items rollup.

1 Like