Help with Formula to Automatically Apply Payments to Multiple Orders in Coda

[Cecilia_Martinez]

Hi, I’m relatively new to Coda (I’ve been using it, learning, and building for a couple of months now), and I’m trying to build an interconnected system for Orders, Payments, and Income.

I currently have the following setup:

• A Orders table connected to both my Clients and Inventory tables.
• I need to link this to Payments, so I can track whether an Order is fully paid, partially paid, or not paid yet.

Here’s the challenge: Some clients make payments that are not tied to a specific order. Instead, I want to record the payment in the Income table and then apply it across multiple Orders with outstanding balances. The goal is to:

1. Apply the payment to the oldest unpaid Orders first, until the payment is fully distributed.
2. If the payment amount exceeds the first order, the remaining balance should go towards the next due order, and so on.

I thought of creating a button in the Income table that, when clicked, automatically generates rows in the Payments table and applies payments to the due Orders. However, I’m having trouble creating a formula that works for this.

Can anyone help me write a formula or provide a solution for automating this process?

P.S. I understand that sharing an example doc can help, but I’m not sure how to do that. Do I need to create a copy of my doc or something else?

[Pablo_DV]

Hola Cecilia,

Bienvenida a la Comunidad!

It is indeed very helpful if you post a sample doc, but in your case everything was very clearly explained and I’ve also stumped against this issue a few times, so here you have a little example with the solution I came up with. It doesn’t rely on any automation or button.

Formula for the pending amount per order

[DB Orders].Filter(
  Date<thisRow.Date and Client=thisRow.Client
).Total.Sum().Let(Accumulated, 
Payments.Filter(
  Client=thisRow.Client
).Amount.Sum().Let(totalPayments,
[DB Orders].Filter(
  Client=thisRow.Client AND Date=thisRow.Date
).Let(SameDateOrders, 
  //Actual formula starts
  switchIf(
    totalPayments>=(Accumulated+SameDateOrders.Total.Sum() ),
    0 ,
    totalPayments>=Accumulated,
    (Accumulated+SameDateOrders.Total.Sum()-totalPayments)/SameDateOrders.Count(),
    thisRow.Total
  )
)
)
)

Hope this helps,

Pablo

[Cecilia_Martinez]

Thank you so much!
That solution worked perfectly. I really appreciate your help. I’m learning a lot about Coda, and this was a huge step forward for my system.
Thanks again!