Transfer the unpaid payment, to be paid in the next month (along with the current one)!

[Manos_Karkonis]

Hi Airtable community!

I need your help please.

I try to organize and keep track of monthly payments.
Customers have contracts with monthly payments. Sometimes they do partial payments for the SAME Month. Sometimes they do not pay a month (!).

1. When I have partial payments within the SAME month and their total is equal to the contract → i need to return the status “Complete (Partial)” and the “Difference” sould be $0.00
2. I need to transfer the unpaid payment OR the difference of a payment, so that it can be paid NEXT month (plus the current monthly contract payment)!

1)

So, i have the field “Sum Payments” which calculates the total of Payment A & Payment B. (Credit Card & Cash):

SUM({Payment A' 💳},{Payment Β' 💰})

the field “Status” which showing:

IF({SUM Payments} < {🎯 Contract},"🔔 Pending", "✔ Complete")

The field “Difference” :

SUM({Payment A' 💳},{Payment Β' 💰})- {🎯 Contract}

I need,

• when I have partial payments within the SAME month and their total is equal to the contract to return the status “ :heavy_check_mark: Complete (Partial)”
• when this happened → “Difference” sould be $0.00

2)

No payment was made in March (3). In April the customer paid the current month (4) and the debt of the previous month (3).
I need the status of month 3 to return to “ :heavy_check_mark: Complete (on Month 4)” and the “Difference” of Month 3 sould be $0.00

That is, to calculate the debt of the previous months, in the next month!

You will help me a lot if you find some time to see my two problems.
Thank you very much in advance!

[Justin_Barrett]

Welcome to the community, @Manos_Karkonis! :grinning_face_with_big_eyes: Doing what you want will be tricky if you continue with everything in a single table, mainly because formulas can’t access data from other records. My suggestion is to split things across multiple tables.

As an example, I built my own invoicing system in Airtable a few years ago, and ran into a similar problem. I create invoices (sort of equivalent to your contracts), and sometimes the amounts paid cover multiple invoices. To address that, I have two tables: one where I only record the incoming payments, and another that’s a junction table where I create records connecting payments to invoices. That allows me to take a single incoming $500 payment in the [Payments] table and connect it to two invoices with amounts of $175 and $325. Thanks to rollup and formula fields, each invoice shows as being paid in full, and the payment record shows as being fully applied. It’s a little more work to create and link various records for each incoming payment, but this type of setup would solve all of the problems that you listed.

[kuovonne]

Welcome to the Airtable community!

Justin has given you good advice about redesigning your base to use multiple tables. This way rollup fields can calculate what you need.

Another option is to use a script. Take a look at the running total example script to see if it would fit your use case. Note that using this script would also require changes to your field.

[Manos_Karkonis]

Thank you @Justin_Barrett and @kuovonne ! I really appreciate your help!