Hey guys, i'm pretty new to Filemaker, so I'm sure there is an easy solution to this problem.

 

Each invoice record has a "total" field, where it calculated by "Invoice Total" - "Payments". This "total" field is not always zero, so I want the amount of this total to appear in the next month's invoice. So on the Invoice table I created a calculation field named "Previous Total", but how do i retrieve the previous amount? I have to call back the previous invoice for the specific customer, i.e. if the new record is Invoice Number 50, I may have to refer back to invoice number 40 because the other 10 invoices in between is related to a different customer.

 

Thanks!

You could collect the sum of outstanding amounts with the customer: if you have

Customer --< Invoice

then a field in Customer defined as Sum ( Invoice::total ) sums that total across all related (to that customer) invoices – and this field can be displayed on the next (or any, for that matter) invoice for a customer.

Note that in the long run it would be better to not use (par force) unstored calculations, but to use a regular number field and set these values via script whenever a pertinent value changes (new invoice finalized, payments entered etc.) For the start, with a low number of invoices, a calc field should do, though.