Inventory report of Drugs purchased in Cartons

Question

I'm working on a pharmacy systems which will give inventory report on how drugs were bought (e. g in cartons) and how it was sold in smaller units (e. g in packets, cards or rolls).
For example;
* 5 cartons of Ampeclox is added to the system.
* Each carton contains 20 packets.
* Each packet contains 5 cards.
* Each card contains 5 rolls
When adding this drug to the system, the storekeeper will insert quantity of Packets per Carton, quantity of Cards per Packet and quantity of Rolls per Card and the system will save each unit with quantity.
Now the seller can sell this drug in Cartons, Packets, Cards or Rolls. I'm looking for a way that if 4 CARDS are sold, the system will report that the quantity remaining is;
* 4 Cartons
* 19 Packets
* 1 Card
Notice that the sale in Card affects Packets and Cartons quantity which means that any sale in smaller unit MUST affect the quantities of upper units.
Please guide me on how to accomplish this or if you have a better method, help me please.

Answer 1

Hello,
can you make the assumption, that once a package is opened, the seller will always try to pick from the opened container until it is exhausted?
So you won't have two opened 'packets' that contain only 3 or 4 'rolls' each.
If this assumption is true, then you could internally in your database store the quantity in the smallest unit of measure. So even if 5 cartons are received, you would save it as 5*20*20*5 rolls. Then you could make a function that calculates, based on the packaging unit structure associated with a specific product, that e.g. 328 rolls on stock, equal to how many of the bigger units.
If this assumption is wrong, then you may have to track each package individually.. Even track the content of partially opened packages. That is much more complicated.

Answer 2

Yes an opened package must be exhausted before opening another package