The simplest is to have a field that reads "quote" that you change to read "invoice".
Other options are also possible.
Typically you would do this with a relationship between QUOTES and INVOICES (by id_quote and allow record creation), go create an invoice and write id_quote onto it, then stash the id_invoice and id_quote into variables, drop down into the QUOTE LINE ITEMS table, do a find on the id_quote then write each Quote detail item into Invoice Details using id_quote and/or id_invoice.
Later you can deal with Quote versions etc.
Note: you could IMPORT, then UPDATE rather than write data but I like the options when writing data - import can be rather rigid.
This is, for me, a common practice. Others may wildly differ.
"There are some ideas so wrong that only a very intelligent person could believe in them." — George Orwell
could be a bit more complicated...
- do You need fixed entries for Your fiscal?
we have separate tables for quotes and invoices. If a quote becomes an invoice, we create a separate entry in the invoice table, the line-items table serves both, quotes and invoices - but the lineitems are duplicated.
So, the invoice can be separated (not one single entry is *not* an invoice), one can add an item for express shipping or special customer wishes that were not in the quote, etc. After 'duplicating a quote to an invoice', that quote becomes locked
All goes via scripts, fast. No problems over years. The 'invoice' table has information about invoice and payments. Quotes, order confirmation, shippment, etc. are in the table called 'orders'
Note that your business rules require for quotes will drive what you need to do with the database. If, like Markus, you need to keep track of what was selected during the quote process separate from what is recorded at the time of sale, you need to duplicate the data to capture a snapshot of this info at the time the transaction transitions from quote to invoice. In other businesses, should this not be necessary, you can literally re-label the quote as an invoice.
If you need both quote and invoices, I would carefully consider the possibility of keeping both Quote and invoice parent records in the same table. This can make certain reporting tasks easier to manage than can be done when the two types are in separate tables records.
This is the single most vital advice:
Note that your business rules require for quotes will drive what you need to do with the database.
"how to" can probably be done as many ways as there are developers. OP needs to decide the business rules first.
Thanks for the answers, could you help me with the script to develop this process?
What decision have you made?
1. Quote & QuoteItems ==> (copy all to) Invoice & Invoice Items
NOTE: this is 4 tables
2. Quote_Inv & Items ==> (add fields) "flag" as Invoice, Invoice number and Invoice date
NOTE: this is 2 tables, requires "lock down" so items cannot be changed
3. Quote & Items ==> (new record) Invoice, but tie to Quote items
NOTE: this is 3 tables, requires "lock down" so items cannot be changed.
4. Variations on 1, 2, & 3?
I have used the three different methods for various clients if these fit their needs. Regardless, I don't believe any Quote should be altered once it becomes an Invoice. Additional quotes and invoices can be created to "append" items should that be needed. If there is a great variation between items in a quote and items in an invoice (what was suggested vs. what was used, for example), then #1 is the best method.