FMP 9: I have an address - order - line item - invoice database where
the line items get invoiced and get the invoice number as foreign key. I
can add line items to an order already invoiced, the next invoice will
take the new line items only.

Just trying to stress it's line items that get invoiced, not the order.
Though in most cases, there's only one invoice per order, an order
could get a new invoice for every line item.

Now I have to add down payments to the system. In some cases, the
customer has to pay one third of the sum when placing the order and one
third before the stuff ships (and the rest 30 days after that).

I can easily create the invoices for the down payments within the order
table, but what happens if we actually get some money for those? Trouble
is I don't have an invoice yet.

a) If I add a line item "down payment received" to the order, it will
correctly lower the amount, but the sum of the line item won't reflect
what has been sold.

b) I could connect the payments table with the order table, have the
invoice script find existing down payments and connect the to the
invoice.

c) Or we could create the invoice the very moment the order has been
placed and work on its appearance (what has to be paid at what time) and
calculate the due date. This will get messy if anything changes in the
order afterwards because, for obvious reasons, I don't allow line items
to be edited once they are invoiced. OTOH, this looks solid because I
can treat all payments the same way; there's always an invoice waiting.

Comments?

--
http://clk.ch

On 2009-10-24 09:14:36 -0700, clk (AT) tele2 (DOT) ch (Christoph Kaufmann) said:

What works really well is a Payments table. It is separate from the
line items, because a customer could well write a payment against more
than one item or invoice. Or for a partial payment.

When you create an order/invoice (which should be a scripted process)
you can pop up a dialog asking if a down payment has been received,
capture the amount & the check number, and then create a Payment record
against either an order, a series of items, or a single item.

Actually, though, if you keep a balance for each customer, a Payment
doesn't have to be against any particular invoice. You just total
everything they've ever ordered, everything they've ever paid, and what
they presently owe is the difference between the two. It's not really
significant when the payment is entered, either on creation of the
order, or later. Or even before, if a customer overpays and is carrying
a credit balance.

Ageing the due balance and generating statements for collecting same is
left as an exercise for the student.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer