Invoice Partial Payment & Job Costing

[Holli_Younger]

Can someone please help - I’m not sure if a formula, rollup, lookup or another option is best for this scenario. In order to job cost correctly, I do not need the duplicates of the payment amount, as it will show more was paid. I see a way around this by adjusting the amount due, however, with multiple people entering data, and checks being cut differently, based on 3rd party financing department details of payment are not known until the check is cut, typically 30-days after the invoice is received.

When a contractor submits an invoice and it is paid as a partial payment - I am currently entering the information twice to keep up with the accounting end, however, as you can see from the example below it looks as if there is still a balance due, when in fact the invoice is closed out with no balance.

Thank you.

[Aron]

Hi Holly,

It’s tough to say whether this is a formula issue or a base design issue without more context! Could you share a read only link to the base (redacted of any information you wouldn’t want to share)?

With that hopefully folks in the community could give you a hand!

[Holli_Younger]

@Aron Here is a better visual of my base, focusing on my question.
As I said, I’m not sure what option would be best to configure a field to get the results needed.

Amount Due is the amount due to the contractor.
Amt. Pd. is the amount paid out (reflecting the date/check #)
Bal Due is the formula currently using to show the remaining balance due.

As invoices come in from the contractor the amount due from contractors’ invoice is submitted. The record highlighted inside the “blue box” is a partial payment made.

The amount due highlighted in the “red circle” in the next record is inputted as the full amount due (invoices come in on different dates and inputted by those who do not have access to see/know if a previous payment is made. This affects the Sum, Balance Due, and Amt. Pd.

It seems obvious to have the Amt. Due easily inputted as the Balance Due, but with over 100 invoices coming in weekly, and several people inputting data, on top of the filtered views after payment is made there is no way to tell if and how much of a balance remains.

I hope this explains it better.

[Jeremy_Oglesby]

Hi @Holli_Younger - I think what you need is a new table to represent Payments.

These Payment records would hold the information like {Amount Paid}, {Check #} and {Date Received}, etc, and each Payment record would be linked to a single Invoice record.

Then in your Invoices table, you’ll have multiple Payments linked to the same Invoice, and you can use a Rollup field that rolls up your Payments -> {Amount Paid} with a SUM(values) for the {Total Paid}.

Hopefully that makes sense – I can explain in more detail with examples if needed.

[Justin_Barrett]

I concur with @Jeremy_Oglesby. I built my own invoicing system in Airtable last year, and have a [Payments] table that operates pretty much how he describes.

[Holli_Younger]

Thank you @Jeremy_Oglesby. I see where you’re going with this thought, but I’m a little confused about the separate, yet duplicate, tables to track. I didn’t think it was relevant before, but I also have a table for job costing that has a roll up per Record # as this number is used to track all projects. Could I use this instead?

[devynoshea]

Can you elaborate on this with some examples? I have an invoices table and a payment table but my concern is when the payment is a partial payment and does not cover the entirety of one invoice - how do i still show a remainder on that order?