Doug: How could you update the Order table with the invoice# if there's
the possibility of >1 invoice per order? Wouldn't you rather put the order#
in the invoice table?
Karen
> Jim, if I understand it, you've got header and detail tables for the
> orders. Do you also do invoicing?
> I had a similar setup that included invoicing and when items were shipped,
> I'd create an invoice (header and details) for those items that were
> shipped. The invoice header would include the order number and the order
> table
> would be updated with the invoice number; given any invoice, I could find
> the order, given any order, I could find all invoices.
> If you aren't doing invoicing, you could use those two table as ShipHeader
> and ShipRows. Granted, that's two more tables, but, hey, disk is cheap
> (e.g. ~$100/Tb) and R:Base is fast.
> Or maybe one table with OrderRowID (unique autonumber ID for each row in
> OrderRows), OrderNumber, ShipDate QtyShipped &whatever else might be
> necessary. When you ship, add rows to this table. With prudent indexing,
> use SQL
> to find what's been shipped and what hasn't.
> I thought about adding ShipQty, ShipDate etc. columns to your OrderRows
> table but that gets messy for multiple shipments of an item, unless you don't
> do partial shipments of an item. ("Never say Never")
> Any of these would be a significant change to the db and your reports -
> maybe more than you wanting to do or are budgeted for.
> It was a number of years ago I did this, so the description is a little
> sketchy and I haven't thought the above through in great detail, but maybe
> it'll give you some ideas.
> Doug
