Invoicing: Best Practice? (1 Viewer)

[sv89]

Hi,

I have a table 'Orders' and another 'OrderDetails' with a 1:M relationship. I want to know what would be the best way to handle invoicing.

Usually payments are made when an order is placed. But there are cases when a payment is made after the order is fulfilled. Usulally, an Order would be invoiced as a whole. But there would be cases where the Order Details would have to be invoiced separately.

I'll have to keep track of the payment amount, date, outstanding balance, set reminders etc.

1) Do I just have a control on the 'Orders' form that generates the 'Invoice' report and keep track of payments in the Orders table?

2) Have a separate 'Payments' table?

3) Have separate 'Payments' and 'Invoices' table?

Any other ideas? Thanks.

 

[Simon_MT]

1) Payments should be treated separately.
2) Invoices should be raised irrespective of payments received.
3) Receving a payment doesn't affect the invoice only the Balance due.
4) There should be an Invoice for each billing (tax) date.

Simon

 

[sv89]

Okay, so "Payments" would have to be a separate table.
Would "Invoices" have to be another table or just a form and/or report based on the "Order" and "OrderDetails" tables?

 

[Simon_MT]

You combine the Invoices providing each Invoice was held as a unique entity.

Simon

 

[sv89]

Excuse my ignorance, but what do you mean by 'combine' the invoices?

 

[Simon_MT]

Combine: have in one table. Invoices numbers must be unique and raised on the date sent to the customer. Part Orders represent should be represented as a single invoice and not consolidated upon completion.

Simon

 

[sv89]

Okay, so I have a separate table 'Invoice' with each record corresponding to a record in the 'Orders' table?

 

[gemma-the-husky]

this is a matter of data analysis

orders and invoices aren't (necessarily) the same thing.

orders often may be delivered against in stages.

invoices often may combine a number of deliveries

so you probably need

a) orders table
b) order lines table
c) deliveries table (linked to order lines table)
d) invoices table (linked to delivery lines table)

if you have a simpler situation than this, you may be able to get away with a "lighter" model - but it's the 1% of exceptional situations you need to be able to manage.

 

[accessfleet]

Processing a lot of invoices (500-700 per month from multipul vendors) has dogged me for a long time. partial deliverys, delivery slips (BOLs). The system that evolved is working well.

actiondate is ()now
description field contains the iv# and any desc note,
posted y/n field
posted date is populated when the y/n field is checked

you may want to consider other y/n fields if tracking the steps is part of what is giving you trouble.

 

[sv89]

Thanks everyone. This has been helpful so far.

So I'll be generating an invoice for each order line (detail) than a single invoice for the order as a whole? It's only exceptional cases where I'd have to invoice each order detail of the same order separately. That would be a lot of invoices. Is there a way to invoice Orders as a whole and Order Details separately as required. Or would that get too messy?

All the prices would be in the tblOrder/tblOrderDetails. So, tblInvoice would only have to hold the InvoiceID, OrderID/OrderDetailID, InvoiceDate, InvoiceClosed fields I guess?

Also, in terms of navigation, I'm thinking of a button 'btnGenerateInvoice' on the 'frmOrder'. This would open up 'frmInvoice' that would prepopulate fields based on fields in 'frmOrder' and then a button 'btnPrintInvoice' that generates 'rptInvoice'

I'm not sure how easy/difficult this is gonna be to achieve.

Any better ideas?

Thanks

 

[accessfleet]

I do a similar thing with workorders. works fine for me.


Private Sub Command247_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "r_workorder old", acViewPreview
End Sub
Private Sub RadioUnit_AfterUpdate()
If IsNull([Unit]) Or [Unit] = 0 Then
'[Unit] = Val([RadioUnit].Column(0))
[Unit].SetFocus
SendKeys [RadioUnit].Column(0), True
'SendKeys "{TAB}"
'[Assigned by].SetFocus

End If
If IsNull([RADIO_#]) Then
[RADIO_#] = [RadioUnit].Column(1)
End If
'Refresh
End Sub
Private Sub Unit_BeforeUpdate(Cancel As Integer)
Me.RADIO__ = Me.Unit.Column(1)

End Sub
Private Sub unitbutton_Click()
DoCmd.OpenForm "unit master", acNormal, , "[unit_#] =" & [radiono]
End Sub

 

[Simon_MT]

You can have as many Order lines per Invoice, the key is if you have a delivery note again with multiple Order Lines, your company may want to recognise the revenue. It is then an invoice should be created, this is irrespective of whether or not the Order has been completely fulfilled.

Simon

 

[sv89]

The thing is the client wants to group a bunch of order lines and invoice it. So if there are 10 Order lines in an order, she may want to invoice it as 3 invoices (6 Orderlines in 1, 4 in the next, 1 in the last etc.)

I'm really confused as to what to do.

I know invoicing the order as a whole would be the easiest.

But, I'm thinking if the user can select (using a tickbox) from the Orders form which of the OrderLines to invoice then and then comeback later on and invoice the rest? Not too sure how to implement this though.

 

[Simon_MT]

Maybe it would be better to flag the Order Details lines:

U - Unbilled (Available to be selected)
P - Picked (Available to be Invoiced)
I - Invoice (already Invoiced can't select or pick)
C - Confirmed ( Can now be Printed)
U - Updated (Invoice printed)

Simon

 

[sv89]

Thanks Simon. That might work.

 

[accessfleet]

Me thinks Simon is on to something..

John.

 

[sv89]

I've created a button 'btnCreateInvoice' on the 'frmOrders' that opens 'frmInvoice'. I want to pre-populate relevant fields in 'frmInvoice' based on 'frmOrders'. So 'frmInvoice' would have to be unbound, right?

So, would I have to create another bound form 'frmInvoice1' if the user wants to scroll through all records?

 

[accessfleet]

The method I use is a closed wo y/n field and then run a view report with "Y" in the criterion.

 

[jdraw]

You can have as many Order lines per Invoice, the key is if you have a delivery note again with multiple Order Lines, your company may want to recognise the revenue. It is then an invoice should be created, this is irrespective of whether or not the Order has been completely fulfilled.

Simon

Simon,

You have a lot of knowledge and info related to Inventory Control. Do you have a general Data model or database structure you could share with the rest of us?
Perhaps some thing for general sharing or reference.
Thank you.

 

[sv89]

The method I use is a closed wo y/n field and then run a view report with "Y" in the criterion.

Could you explain a bit more in detail. I'm fairly new to Access forms and reports. Thanks