Qty Problem

[Parts Manager]

I am still designing the tables, fields, relationships, etc...; and ran
across something I don't remember how I have handled in the past.

Ea/Bx/Rl/Dz etc... for the unit of measure.

Item XYZ is billed out as a qty 1 each. They are purchased as Rolls of 100.

Obviously my Each Qty is ok when taking out of inventory as I can have a
field for that, but it seems I need to have a field (or two) to cover
purchase quantity Unit of Measure and it's price per box/roll/dozen if that
is the minimum quantity that can be purchased. I need to be able to bill
out a single unit and take out a single unit from inventory; yet when the
purchase order is made it references full box (roll in this case) size with
the correct price for a full roll; not on a per unit basis that I bill out
at. So when an order of 6 Rolls are ordered, the correct per Roll price
comes up. When the item arrives and is 'received', it goes into inventory
as 600 since we bill out as each and the rolls have 100 attached.

What would be the proper way to handle this?

I have a table called Item List and here is an example of what I think I
need to be looking at, but I am also leaning toward maybe having another
table for Unit Of Measures with corresponding quantity per measure.

Table: Item List
----------------
Iuom: Item Unit of Measure
Iuop: Item Package Size
Imin: Item Minimum Purchase Package Size
Imin: Item Billed Out as What Size?
Icost: Item Cost Per Unit Of Measure
Iunit: Item Unit of Measure Size
Iumin: Item Unit Purchase Minimum
Iupkg: Item Unit Package Size

The above are things I have been mulling over on what I need or don't need;
only posted to show where I was thinking to see if there is a better way or
even a standardized way to deal with billing in Eaches, yet purchasing in
minimum quantities of 100's; while allowing the purchase order to have the
correct purchase quantity and the billing to have their correct billing
quantity.

Any suggestions?

Tim

BTW, I haven't even mentioned that the item may be sold in rolls of 100 from
the vendor and it could mean they have BOX quantity of 6 Rolls for
purchasing. So it very well could be possible to have a bill/sale size of
EACH, purchasing as Rolls to keep on the shelf, and have minimum 6 Rolls to
a BOX from the vendor for purchasing. :-)

[ByteMyzer]

You might try a structure along the lines of:
Item
IPUom: Item Purchase Unit of Measure
IBUom: Item Billed Unit of Measure
ICost: Item Cost
IConv: Item Conversion Factor

Per your example on Item XYZ, you might have an entry like this:
Item: XYZ
IPUom: RL
IBUom: EA
ICost: $10
IConv: 100

In the above example, Item XYZ is purchased and received in as rolls at $10
per roll, at 100 units (EA) per roll, and issued/sold by the unit (EA) at
the cost per purchased unit divided by the Conversion Factor
($10/100=$0.10).

See if this methodology works for you.

"Parts Manager" <pa**********@howardmoe.com> wrote in message
news:11*************@corp.supernews.com...

I am still designing the tables, fields, relationships, etc...; and ran
across something I don't remember how I have handled in the past.

Ea/Bx/Rl/Dz etc... for the unit of measure.

Item XYZ is billed out as a qty 1 each. They are purchased as Rolls of 100.
Obviously my Each Qty is ok when taking out of inventory as I can have a
field for that, but it seems I need to have a field (or two) to cover
purchase quantity Unit of Measure and it's price per box/roll/dozen if that is the minimum quantity that can be purchased. I need to be able to bill
out a single unit and take out a single unit from inventory; yet when the
purchase order is made it references full box (roll in this case) size with the correct price for a full roll; not on a per unit basis that I bill out
at. So when an order of 6 Rolls are ordered, the correct per Roll price
comes up. When the item arrives and is 'received', it goes into inventory
as 600 since we bill out as each and the rolls have 100 attached.

What would be the proper way to handle this?

I have a table called Item List and here is an example of what I think I
need to be looking at, but I am also leaning toward maybe having another
table for Unit Of Measures with corresponding quantity per measure.

Table: Item List
----------------
Iuom: Item Unit of Measure
Iuop: Item Package Size
Imin: Item Minimum Purchase Package Size
Imin: Item Billed Out as What Size?
Icost: Item Cost Per Unit Of Measure
Iunit: Item Unit of Measure Size
Iumin: Item Unit Purchase Minimum
Iupkg: Item Unit Package Size

The above are things I have been mulling over on what I need or don't need; only posted to show where I was thinking to see if there is a better way or even a standardized way to deal with billing in Eaches, yet purchasing in
minimum quantities of 100's; while allowing the purchase order to have the
correct purchase quantity and the billing to have their correct billing
quantity.

Any suggestions?

Tim

BTW, I haven't even mentioned that the item may be sold in rolls of 100 from the vendor and it could mean they have BOX quantity of 6 Rolls for
purchasing. So it very well could be possible to have a bill/sale size of
EACH, purchasing as Rolls to keep on the shelf, and have minimum 6 Rolls to a BOX from the vendor for purchasing. :-)

[Parts Manager]

"ByteMyzer" <sb*@news.chi.sbcglobal.net> wrote

You might try a structure along the lines of:
Item
IPUom: Item Purchase Unit of Measure
IBUom: Item Billed Unit of Measure
ICost: Item Cost
IConv: Item Conversion Factor

In the above example, Item XYZ is purchased and received in as rolls at
$10
per roll, at 100 units (EA) per roll, and issued/sold by the unit (EA) at
the cost per purchased unit divided by the Conversion Factor
($10/100=$0.10).

See if this methodology works for you.

Yes, that does give me a way to review. Your suggestion looks like it can
do what I need. Thank you.

Tim