Automated record generation

[mlozano]

Hi
I am developing a db for quotes, orders, delivery noted and invoices.



Each type of document (quotes, orders, delivery notes, invoices) is divided in two tables, one for the headers and another one for the details (lines)

I would like to allow my users to generate an order from an existing quote, a delivery note from an existing orsder, and invoices from existing delivery notes. The details (lines) of each document should be transferred from one type of document to the next, and all the header detyails should be copied too.
What is the simplest way to go around this?

Thanks a lot

[rpeare]

You don't need a separate table for orders vs quotes. You can have one table that is the quote, just have a 'yes/no' field if that quote becomes an order. Then have a order sub table for any additional items that are DIFFERENT from your quote system (i.e. delivery date etc). You also do not need to copy information from orders to invoices. You can create an invoice based on the order likely involving a cost per item table so that your shipping label would just have quantities but your invoice would have the cost. Copying the same information from table to table is a very, very bad idea, you will have all kinds of trouble synchronizing your data and making sure there are no errors, not to mention you will be storing WAY more information than you need.

[mlozano]

Thanks rpeare!
Do you think it would be workable to havethen only one "headers" tableand one "details" table, then add successively the necessary information. For exple
1.- Quote is created
2.- Order is confirmed, so Order Nr. is added
3.- Order is ready forshipment, so Delivery Note Nr is added
4.- Shipment is delivered, so Invoice Nr is added

Adding the Invoice nr weould imply locking the data of both the header record and the associated lines records, is this feasible without too much coding?

Many thanks

[rpeare]

Well, I tend to prefer designing data entry on unbound forms so I use a lot of code, I find it a lot easier to control what happens to the data that way. But realistically you can do the same sort of function on both bound and unbound forms, the bound forms are much easier for newer users to contend with but everything you mentioned is possible how you proceed is really determined on your data structure and whether or not you're going to use a bound/unbound form for data entry.