Unit of measure and Quantity in Purchase Orders

Question

I have written an application that my clients use to keep inventory. The inventory system includes a purchase order module. Each purchase order has a list of line items including:

• Description of item
• Quantity to order
• Unit of measure
• Unit price

among others. When asked about the "unit of measure" field, my response is always to use a unit label that reflects the manner in which you consume or resell the items. Here are a couple examples of what I mean:

• "cc" for liquid medication stored in a bottle
• "Bar" for a candy bar
• "Bag" for a bag of dog food

This is clear when the unit of measure being consumed or resold matches the unit of measure on a received invoice. My problem is when it does not match. For example, medication is usually sold by the bottle, but dispensed by the cc or pill, candy bars are purchased by the box or case and sold individually, and dog food can be purchased by the pallet and sold by the bag.

In my experience in the past few months, I have realized that's it's unrealistic for a user to translate boxes into bars or bottles into cc, but I have not yet found a good way to remove the need for the mental translation.

Should I set up a table for equivalencies (1 box = 12 candy bars, 1 bottle = 1000cc, etc) and ask the user which they are ordering? This seems like it would add a lot of complexity behind the scenes when a vendor changes their packaging or offers new options. I'd like to keep the whole process as easy as possible.

Am I thinking about this incorrectly? Is there a simpler more elegant solution?

Update: Here are some line items I'm getting that don't work:

Cephalexin        1     Bottle     $100.00
Snickers         10     Box         $12.97
Dog food          1     Pallet     $122.50

These are almost completely useless to me because the units and quantities aren't useful when updating the inventory. People don't resell dog food by the pallet or antibiotics by the bottle. This is what I need:

Cephalexin      100     Pill         $1.00
Snickers        120     Bar         $1.297
Dog food         20     Bag         $6.125

Those units, quantities, and prices directly correlate to the units that are consumed or resold. The first set are what the OEM vendor expects.

Answer 1

From my understanding you have an application which thinks of products in terms of:

1. Inventory (and)
2. Products

The owner buys a box of chocolate bars, enters the new acquisition into the system.

The system need to be able to know how many bars (units) the new inventory contains in order to update the (customer facing) stock levels.

I don't think you can get away from the fact that the owner will need to provide information about the number of units their new inventory purchase without access to manufacturer supplied line data.

However, even so, I don't think the problem is insurmountable .. maybe you're over-thinking the problem?

---

This is the way I'd approach the problem:

• When entering information about a new inventory purchase, the owner needs to provide the number of generic units that each new acquisition of inventory contains.
• These (customer facing) units can either be fixed / or variable (weight / volume)

FIXED UNIT (can be sold individually)

• The total number of units contained in the new inventory acquisition needs to be entered.
• It doesn't matter what these fixed units are - the owner can enter a text string representing them on entry of the inventory (eg. bar, pill, necklace, brush).
• A simple parser can be developed to deal with singular/plural versions of the string.
• This label can be used on the customer facing form.

VARIABLE UNIT (sold by measured amount)

• If the inventory is to be sold as variable units, the owner needs to decide whether they'll be sold using weight or volume.
• The total weight / volume needs to be defined.
• The granularity of measurement unit needs to be defined.
• The cost per variable measurement unit needs to be defined.

Hope this helps.

---

Re. The unit problem mentioned below

If the site owner is having a problem understanding to concept of a saleable unit, maybe the answer is to add the unit input fields into part of a natural language sentence, and to use the answer to provide an example of how the unit label would be used.

VARIABLE UNIT

The customer can purchase this product by the [_variable_unit_selectbox_input_].

Ajax feedback -> "Example: Joe Bloggs bought 5 [_variable_unit_selectbox_input_] of [_product_type_]"

FIXED UNIT

The customer can purchase this product by the [_unit_label_text_input_].

Ajax feedback -> "Example: Joe Bloggs bought 5 [_variable_unit_input_]s of [_product_type_]"

Answer 2

You should be selling in the unit that your customers expect, and then interpret that appropriately. Or in the unit you sell in - do you really sell BOTH boxes of bars and individual ones? If so, you might want to consider having them as separate products or variants.

When your customer has ordered 2 bar boxes, you indicate that 2 boxes of 12 each = 24 bars. Or if they order 2 bars - a separate item - you indicate 2 bars.

So start from the point that your customer expects to see, and clarify exactly what this means, where appropriate.

Answer 3

As far as I can see, the problem is with the inventory system rather than the software since you did if based on your clients' requirements. The standard of warehouse management is to assign SKUs (stock-keeping units) to every variation of an item carried, which includes colors, packaging, and storage sizes. Your software is suffering because there's no way to connect 1 candy bar sold in retail to the pallet of cases of boxes of candy bars that the warehouse receives from the supplier.

What you should do is go back to the drawing board with your clients and design SKU systems that work for them. SKUs are custom, occasionally long and descriptive internal identifiers. They should be tied to UPCs (universal product codes) to allow fast look-up in the warehouse & at the check-out. The complexity & detail level of an SKU system is entirely up to the inventory managers. Some people prefer them long and descriptive and others like them short.

In the database, one retail SKU can belong to multiple display, storage, and ordering units so that whenever the bulk container size changes inventory managers can easily order the right quantities.

For example, candy bars are sold individually and assigned a retail SKU of 12345. It is placed on the store shelf in a box that contains 36 of them, so the display unit (box) SKU is 12345-D36. In the warehouse, the boxes are stored in cases of 12, so the storage unit (case) is assigned SKU 12345-D36-S12. The cases are shipped & ordered in pallets of 50, so the ordering SKU will be 12345-D36-S12-O50.

For another example, let's consider a TV that is sold, displayed, and store individually but is shipped & ordered in pallets of 10. The base SKU in my system will be 54321 but since it's the same unit across multiple stages it'll be 54321-RDS1 and the pallet's SKU will be 54321-RDS1-O10.

Answer 4

It seems like the translation is surfacing due to a violation of your first principle...

...use a unit label that reflects the manner in which you consume or resell the items.

It appears the issue is how they purchase the goods to then resell, am I correct in this assumption or am I missing something?

• The bottle is the container, the pills are the product.

• The box is the container, the candy bar is the product.

Stay away from defining the container as the unit of measurement since that is nothing more than an abstraction. The abstraction can change, the product most likely won't. While dog food comes in a bag, the unit of measurement is lbs, since you can have varying bag sizes (abstraction) which contain different amounts.

This may be a simple case of tight coupling throughout the product and your application needs to provide varying levels of abstraction to deal with this. Raw units of measure (lbs, count, etc...) up to abstracted units of measure (bag sizes, bottle sizes, etc...).

Answer 5

There must be convert factor formula working behind scene. So If there is certain equation like 1 Pallet = 12 Bag 1 Bag = 10 Item Then it's easy to display in UOM drop-down,by just identifying item you can give these convertible UOM as option to them to semlect the UOM.