I use Airtable for my accounting program.
My goal with this post is to discover how I can allocate specific and different values from one payment record to multiple linked invoice records. The intent is to provide an unpaid/open balance which is simply the invoice value less the APPLIED payment.
Currently, I have only two tables set up for this base. I am wondering if having a third linked table to address the application of different amounts going from one payment record to multiple invoice records will solve my problem.
Background:
In my base, I have two linked tables. The Invoice table consists of invoice records. (In the Invoice table, one record = one invoice). The Payment table consists of payment records made against specific invoices or a specific invoice. (In the Payment table, one record = one payment).
I link invoice record(s) to payment record(s). In the invoice record, I have a column that calculates the open/unpaid balance of the invoice with a rollup column via the linked payment record.
When I link one or several payment records to one single invoice record, the system works perfectly.
Problem:
However, I run into problems when I have one payment record linked to more than one invoice record.
For example, this simple situation does not allow me to accurately calculate an open/unpaid balance on the invoice records (with my current setup).
– I enter a payment record with a value of AED 10.00.
– I enter two invoice records with values of AED 6 and AED 4.
– I then link the payment record to the two invoice records.
In the above simulation, my roll-up formula for the unpaid/open balance takes the invoice value and subtracts the full AED 10 from each invoice. In theory, both invoice records “SHOULD” show a balance of AED 0. However, because my formula is based on a rollup value of the full payment, and not an allocated amount, the unpaid/open balance shoes a negative AED 4 and a negative AED 6 respectively.
I understand my formula using the linked roll-up is returning exactly what it is supposed to. But in reality, I should be dictating to apply a specific allocation of the AED 10 against the two linked records.
Will a third table help me with this?
BREAK
I have been using Airtable for three of my small business accounting programs for multiple years with a decent level of success. I have recently upgraded my base to consist of linked Payment and Vendor Bill tables. Previously we only employed a single Vendor Bill table where we posted payment details on the same record (simply by adding columns).
This setup presented several challenges when searching for payments to vendors. As the payment itself didn’t exist as a stand-alone record.
While creating the separate but linked tables have solved a set of problems, but I am wondering if I have only swapped one set of problems for another new set.
Thanks for your time!
David
