Quotation and invoicing DB design question

IronLiver · Mar 30, 2011

Hello,

Having done a bit of work on the structure of this database and lot of hunting around on various forums I am yet to find a practical solution to the quotation to invoice part of the database.

It is for a service based business where its primary use will be to create and track invoices to customers for various jobs most of which have some element of labour and some element of material costs associated. I have created a structure that I am happy with for this aspect, including the customer/people table structure from Allen Browne:
http://allenbrowne.com/AppHuman.html

What I can't quite put my finger on is the quotation side. We generate relatively few quotations compared with invoices and I toyed with the idea of keeping this element separate, however, when we do create quotations they often lead directly to an invoice that is more or less identically worded to the quote.

If this were always the case then the design might be more straightforward as line items from the quotation could then also be assigned to the invoice by including a QuotationID(FK) field in the line item table as well as the existing InvoiceID(FK). However, very often the descriptions of services provided and prices in the invoice vary from the original quote as unforeseen circumstances arise once work starts.

Note, the line items also tend to be quite wordy descriptions of services to be provided and currently we include scant mention of material to be used.

Sometimes and invoice will break the cost down into material and labour and sometimes it will be a single amount. This can also vary between quote and Invoice, with the client deciding that they would like the material cost to be stated separately in the final invoice.

I am therefore wondering if there is a neat way to retain the detail of the original quote (beyond saving a PDF copy somewhere) whilst amending certain details at the invoice stage. The solution needs to be fairly easy to implement as my knowledge of VB is quite rusty, but does extend to writing stored procedures and macros in the VB editor (albeit cribbing heavily from online sources). Given the number of quotes to invoices it is not worth holding the development of the database up, but it would save the hassle of retyping duplicate information into the invoice item detail.

Any thoughts or suggestions would be much appreciated

I can post details of the tables as they stand if needed.

revans · Mar 31, 2011

It seems to me you'd have two tables, one for quotes another for invoices. For the line items keep those in a separate table for a lookup but let the field in the quote/invoice be not Limited To List (I think the property is called that). This way you can pull up your line items but customize them. I suppose it's possible to keep this all in one table with a new column to denote invoice vs quote, but logically two tables works to get things working anyhow.

Then on your quote form you'd have a Make This An Invoice button. This would update the invoice table with the data from the current quote. Since they're mostly the same data this should be straight foward, perhaps with a QuoteID as you've mentioned to link to the previous quote. Again the line items on the invoice should be not Limited To List so you can change them as necessary.

No doubt I've oversimplified and missed the point.

hth,

Rich

Quotation and invoicing DB design question

IronLiver

New Member

Excel Facts

revans

Well-known Member

Similar threads

Forum statistics

Share this page

Quotation and invoicing DB design question

IronLiver

New Member

Excel Facts

revans

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock