AnsweredAssumed Answered

Help with Multiple Unit of Measure Conversion and Application

Question asked by starstuff on May 28, 2015
Latest reply on Dec 29, 2016 by philmodjunk

Title

Help with Multiple Unit of Measure Conversion and Application

Post

Hi All!

 

I like to gather some idea with Unit of Measure for each product

The plan is to add 1 or more unit of measure (U/M)  depending on the product.

 

Example: Special Homemade Doughnuts

Purchase U/M from Supplier

1 box = 12 dozen

12 dozen  = 144 pieces (sometimes problem arise like you order 2 boxes, but 4 dozen are mishandled, you only received the 20 dozen)

 

Customer Purchases U/M

dozen = 12 pieces

half-dozen = 6 pieces

pieces

 

Current Idea:

Product Table

Product_ID

Name

Unit (base unit)

SellingPrice

PurchasePrice

SellingUnit (selling unit default unit)

SellingUnitConv1 (SellingUnitConv1 and SellingUnitConv2 will be the ratio to use for the conversion say 1 dozen = 12 pieces )

SellingUnitConv2

PurchaseUnit (purchase unit default unit)

PurchaseUnitConv1  (PurchaseUnitConv1 and PurchaseUnitConv2 will be the ratio to use for the conversion say 1 box = 12 dozen )

PurchaseUnitConv2

 

LineItem

fk_Product

fk_Invoice

QtyIn

QtyOut

Balance

 

Invoice

Invoice_ID

ThirdParty (Customer/Supplier)

Qty

Amount

 

Concerns:

1. The table above could work for 1 conversion of U/M, for multiple conversion im having a hard time thinking how to do. i found this article from an anpplication called quickbooks but looks hard to do in filemaker.

http://www.quick-training.com/2013/11/27/setting-up-and-using-multiple-unit-of-measure-in-quickbooks/ ,

i could have 3 records of the same item for sales, and another 2 records of the same item for purchase to have all the units, but i think this is more complicated

what is the best approach for this?

2. If multiple unit of measure is possible, i like the user to be able to have an option to select it. like instead of typing 12 pieces, the user can select unit of dozen, and it will be converted to 12 pieces in the background, How to do this?

3. Do i need another table just for conversion of units?

4. Are there any example solutions available for this problem?

 

Thanks All!

Outcomes