Invoicing Database Structure Questions

[caveat1]

Hello,

I work for a small publishing company. I have a few questions about a system we are creating to mamge our invoices and inventory.

We have a few distributors and customers who order books from us on a consignment basis. It is simple to set up invoices for the customers who buy outright, and we have an invoicing database started. I am wondering the best way to manage the consignments.

This is how the process generally works:

The distibutor or customer places an order for books to have on consignment. We need to remove these from our inventory and put them in the distibutor's inventory, but they are not actually sold at this point. On a monthly basis, we receive a report and payment for books that were sold that month. At this point, we need to remove them from the distibutor's inventory and mark them as sold, as royalties are paid only on books that are sold and paid for. The books that are sold in a month don't match a particular order, so we cannot simply mark an entire order sold.

Some possibilities are to use the invoice database, but have invoice types of "sale" or "consignment." Another possibility is to create a new set of tables for consignment orders. I don't yet have a big picture in my mind of the structure of this kind of system. I would welcome any ideas of how to get started.

Also I am wondering the best way to handle multiple ship-to addresses. We only have 2 or 3 customers who are larger companies, with potentially hundreds of ship-to addresses for each. The invoices are generally sent to the ship-to address, but payment comes from the main company and needs to be tracked that way. Is it best to put each one in as a separate customer, or a separate table just for the ship-to's on those few customers. Again I'm not quite sure of what to be looking out for to make it simplest for our process.

My last question is regarding lookup fields vs related fields from other tables. On an invoice, the item name and price are lookup fields (in the line items file, looking up from the inventory file) That makes sense to me, otherwise any price changes in the inventory file would change the invoice total. However, right now the customer information is on the invoice, only through fields from the customer table, NOT lookups. This is fine and is a more efficient use of memory. The only problem I have is in printing an invoice. The invoice currently displays a portal with 10 lines. If the order goes over 10 lines, the only way I have found to fix that is to create the invoice as a report in the line items file. The problem is that I can't get the customer information to appear in the line items file at the top of the invoice report, since the customer data is related to the invoice table, not directly to the line items table.

OK, this last question actually has a few parts to it. When is it best to use lookups or related fields on a form/report? Is it a waste to make the customer info fields lookups in the invoice table? Is there a better way to print an invoice that goes over 10 lines?

Thank you for any help to all or part of this email.

KC

Edited July 17, 2006 by Guest

[caveat1]

Well, I haven't gotten any replies. Please let me know if there is anything I can do to get some replies.

Here is what I have come up with so far...

I can keep one invoice/line items database, but use "types" of entries - either an Invoice or a Consignment. I can add a field to the line items file for Quantity on Consignment, which would only have data if it is a Consignment. For Invoices, the Quantity would be entered into the Quantity Sold field in line items.

It seems like I would also need a new table to store Distributor-Book information, meaning inventory on hand of each book for each distributor. The only way I can see to generate the records in this table is through a script, when a customer is set up as a distibutor or when a new book is entered. Does this seem correct?

Does anyone see any potential problems with this type of setup? It seems like I will be able to make the necessary inventory calculations this way. Any thoughts?

Thanks,

KC

[bruceR]

Regarding lookups, they are usually used where you want original information to continue unchanged over time. You wouldn't want to look at an old customer invoice, for example, and see that it has new prices on it. And you probably also would not want to look at an old customer invoice and see that it has their new address. For many purposes you are looking for a "snapshot in time" and it is important that the snapshot be accurate.

There are other things when you're on the phone with a customer for instance and when you look at your product prices you really should be seeing the most up to date price.

[Drew Sanderson]

KC,

Did you fingure out how to do consignment. I too have a similar problem to figure out.

Thanks,

Drew