you didnt post your solution JB! or at least the script.
1 of 1 people found this helpful
In standard mission-critical databases that I've dealt with. The payments area always stored separate from the invoices, allowing for multiple payments (or credits) to be applied to an invoice, or to the client independent of the invoices.
So you could easily make a running total by having three calc fields through the relation from companies:
Field 1 = Sum ( invoices::total )
Field 2 = Sum ( payments::amount )
Field 3 (running total) = Field 1 - Field 2
This is the simplest form. There is also an easier way to calculate this in one field using ExecuteSQL in Filemaker 12:
Running total =
Let ( [
id = company::id;
invoices = ExecuteSQL("SELECT SUM(total) FROM invoices WHERE companyid = ?" ; "" ; "" ; id );
payments = ExecuteSQL("SELECT SUM(amount) FROM payments WHERE companyid = ?" ; "" ; "" ; id );
invoices - payments
Thanks, this is my plan of action. I'm going to test thisto see if it works the way I wasnt it to.
My file has two tables: Register (Invoices) and LineItems. The LineItems record payments made to the Balance (Register). What I want to do is have the +/- amount be deducted from the next invoice. I'll check back in when I've tested this.
All the best,
That wording could be confusing. Most developers I think would consider "LineItems" as line items of an invoice. Using nomenclature that is descriptive of the records functions usually works best for me (why I noted "payments" in my samples above).
Depending on how your invoicing generation scripts work, it should be easy to use the ExecuteSQL calculation to add a "balance forward" value to an invoice when it is created.
Here is what I did (FileMaker 11):
Field 1 = TotalInvoices
Field 2 = TotalPayments
Field 3 (TotalOS) = TotalInvoices - TotalPayments
The invoicing script compares the current amoutn due and calculates the O/S balance from TotalOS.