I have a FM solution for the haulage company I work for. Each job is booked into a "Loads" table, and the price and any additional charges for each load is created in a "Charges" table (although charges can also be created that are not related to Loads e.g. storage charges).

When ready to be invoiced a new record is created in the "Invoices" table and the invoice is allocated to a particular company. At which point all available charges for that company are displayed and can be pulled onto the invoice (by simply adding the invoice's serial number to the charge selected).

I'm having some issues, however, managing payments. At the moment any payment is logged in a "Payments" table. On leaving this table a script imports all invoices and payments into a "Transactions" table so that statements can be displayed. However, this is cumbersome and I still have to manually mark specific invoices as paid (as one payment may pay for multiple invoices)

I was hoping to use the "Invoices" table to record payments as a type of invoice with a negative total, which would allow me to pull invoices onto the payments and therefore simultaneously mark them as paid, but then I have the issue of viewing payments when searching for invoices as I'm using the one table for two purposes.

Does anyone have any experience of managing invoices and payments who could offer any advice please?

Payments definitely should be its own table and not mingled in with Invoices.

This part of the description is not very clear.  Why import both invoices and payments into a transaction table?  If you want to preserve historical statements in the database then you'll need a statements table - is that what the 'transactions' table is for?

59 minutes ago, Ninja said:

At the moment any payment is logged in a "Payments" table. On leaving this table a script imports all invoices and payments into a "Transactions" table so that statements can be displayed.

You defnitely need the 'Payments' table.  In addition to that you can use a 'Payment allocation' table that is a join between the Payment and the invoice line items.  That way any one payment can be set up to cover one or more full invoices or just as a partial payment for one invoice.

Running a statement is then just doing a search on all invoice line items (charges) that are not marked as paid.

 

You're right, I think I've just had a vision of providing statements that show invoices and payments, however I guess this is unnecessary. (Yes this was the transactions table)

 

So how would you suggest allocating payments to invoices? I can imagine a number of ways, but it becomes complicated when considering partial payments.

 

Thanks for your help

4 hours ago, Ninja said:

So how would you suggest allocating payments to invoices? I can imagine a number of ways, but it becomes complicated when considering partial payments.

 

Partial payments happen in real life so you have to allow the system to handle it. 

- for each payment received, create a payment record (from who, amount, method - cheque / wire transfer / credit card / ..., date)

- for each payment record create a workflow where you show open invoices with the individual charges for that customer

- let the user select a whole invoice or individual charges, creating 'payment allocation records for each 'charge' record that gets fully or partially paid.

If you haven't used it before: this is a typical workflow that you'd want to use transactions for so that when something goes wrong in committing the payment allocation, that you can back out completely and revert all changes.

 

Hmm that's an interesting workflow I hadn't considered.

It would be unusual for one of our customers to not pay for a specific charge, more likely that they would simply make an error and pay £1,032.23 when they meant to pay £1,023.32.

Do you forsee an issue whereby the database simply offers the customer's unpaid invoices at the point of recording a payment, and the user can tick those that should relate to the payment?

 

If a customer overpays then that should become 'cash on account' and be registered somewhere to be used in future allocations.  Unless you always immediately refund the difference.

I don't know your business well enough, but in general I would build the system for what could happen, including the unusual events - like a customer contending a charge and refusing to pay it.  Otherwise the system does not reflect reality and reports will be off, users will ignore the system,... 

Great topic. The "somewhere" I use is a billing account. Each client has one. That's where I apply deposit payments, such as retainers, before the order is available. So, my payment_allocations table has id_billing account, id_order, id_invoice and id_payment.