9 Replies Latest reply on Dec 29, 2016 12:04 AM by philmodjunk

# Help with Multiple Unit of Measure Conversion and Application

### 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.

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!

• ###### 1. Re: Help with Multiple Unit of Measure Conversion and Application

It's possible to set up a related table with three fields:

Unit of Measure Start, Unit of Measure converted, Conversion Factor

So you might have a record in this table like this:

UOMstart = dozen, UOMconveted = items, CFactor = 12

You can then use a relationship to look up conversion factors from this table and multiply your original value by it to produce the equivalent quantity in the converted units.

• ###### 2. Re: Help with Multiple Unit of Measure Conversion and Application

HI PhilModJunk,

i i ended up using the Products Table for the products UOM.

i added these fields

uom_base = base unit of the product, smallest unit  example: pcs, each

uom_purchase = purchase unit of the product, this is the unit when you purchase products example: box, case, spool

uom_purchase_cfactor = conversion factor,  uom_base * uom_purchase( unit base multiple by purchase unit)

uom_sales = sales unit of the product, this is the unit when you sell products, mostly same as the base unit example: pcs, each

uom_sales_cfactor = conversion factor,  uom_base * uom_sales ( unit base multiple by sales unit)

You can then use a relationship to look up conversion factors from this table and multiply your original value by it to produce the equivalent quantity in the converted units.

i think i wont be needing a relationship to look up for the conversion factor because i put it on the same table? (DONE! see update below using uom_rate)

i would like to have a look up for all the available uom of a product, the lookup would contain the uom_purchase, uom_sales, and uom_base, how to do this? (DONE! see update below using List)

im going to use the uom_sales_cfactor and uom_purchase_cfactor to change the price and the quantity, is that correct?

Thank you!

• ###### 3. Re: Help with Multiple Unit of Measure Conversion and Application

update:

i added 3 more fields

uom_list = contains the list of available uom for the product (calculated to get List ( uom_base; uom_purchase; uom_sales), made a drop down value list

uom_rate = contains the conversion rate using a Case statement

Case (

PRODUCTS::uom = PRODUCTS::unit_base ; qty * 1;

PRODUCTS::uom = PRODUCTS::unit_purchase; uom_purchase_cfactor * qty ;

PRODUCTS::uom = PRODUCTS::unit_sales ; uom_sales_cfactor * qty;

)

uom = contains the selected uom from the uom_list value list

NOTE: the qty field is temporary in the PRODUCTS table, it will be moved to the LINE_ITEMS table.

its working, it changes the qty when i select a different uom in the drop down list,

can anyone give advice on this? is the solution here good or bad?

Thanks All!

• ###### 4. Re: Help with Multiple Unit of Measure Conversion and Application

It limits you to a single unit conversion. If this is always the case and will always be the case, no problem, but if, for example, the boss decides to offer the same product in to different packing configurations (or three or more....), then this method will not work for you without creating multiple product records for the same product but with different packing units.

• ###### 5. Re: Help with Multiple Unit of Measure Conversion and Application

Thanks PhilModJunk for the advice! will re-evaluate which of the 2 is better.

im considering your solution also, because its more flexible.

can you kindly explain this further?

You can then use a relationship to look up conversion factors from this table and multiply your original value by it to produce the equivalent quantity in the converted units.

did you mean i have to create a look up field? or a value list?

and how will the product know which of the units is related to?  im thinking i need to create a join table for this.

products = can have 1 or more units

units = can be related to 1 or more products

products ---------<product units>------------ units

Thank you again!

• ###### 6. Re: Help with Multiple Unit of Measure Conversion and Application

My original assumption was different from what you actually have in place. I assumed, that like a recipe data base, a field in each record identifies the desired units. So you could specify "Cups" and a value stored in Tablespoons could be converted to the equivalent number of Cups.

You'd use a relationship that uses such a separate Units field as a match field in the relationship in order to look up the correct conversion factor by which you then multiple to compute your quantities adjusted for the specified units.

• ###### 7. Re: Help with Multiple Unit of Measure Conversion and Application

your assumption is right on, i choose a unit and unit converts the qty based on the other unit.

maybe i need to step back and rethink what i really need.

Thanks for the help PhilModJunk!

• ###### 8. Re: Help with Multiple Unit of Measure Conversion and Application

We rigged up a custom function that we can just push the variable to and return. If you abstract this as a script param you can just get field name  of origin, target, and unit and then update dynamically. Im also learning that it may be best to put all units of measure in their own table and then relate. This way multiple tables can use the units.

Here is the CF that Charles Ross helped me develop. Just set the params in the CF and this is pretty dynamic.

// nump.ConvertUnits ( _orig ; _target ; _number )

Let (

[

_factor = Case (

_orig = "mm" ; Case (_target = "cm"; 1 / 10 ;

_target = "mm"; 1;

_target = "m"; 1 / 1000 ;

_target = "in"; 1 / 25.4 ;

_target = "ft"; 1 / 304.8 ;

_target = "mi"; 1 / 1609344

) ;

_orig = "cm" ; Case (_target = "mm"; 10 ;

_target = "cm"; 1 ;

_target = "m"; 1 / 100 ;

_target = "in"; 1 / 2.54 ;

_target = "ft"; 1 / 30.48 ;

_target = "mi"; 1 / 160934.4

) ;

_orig = "m" ; Case (_target = "mm"; 1000 ;

_target = "m"; 1 ;

_target = "cm"; 100 ;

_target = "in"; 39.370079 ;

_target = "ft"; 3.2808399 ;

_target = "mi"; 1 / 1609.344

) ;

_orig = "in" ; Case (_target = "mm"; 25.4 ;

_target = "in"; 1 ;

_target = "cm"; 2.54 ;

_target = "m"; 1 / 39.370079 ;

_target = "ft"; 1 / 12 ;

_target = "mi"; 1 / 63360

) ;

_orig = "ft" ; Case (_target = "mm"; 304.8 ;

_target = "ft"; 1 ;

_target = "cm"; 30.48 ;

_target = "m"; 1 / 3.2808399 ;

_target = "in"; 12 ;

_target = "mi"; 1 / 5280

) ;

_orig = "mi" ; Case (_target = "mm"; 1609344 ;

_target = "mi"; 1 ;

_target = "cm"; 160934.4 ;

_target = "m"; 1609.344 ;

_target = "in"; 63360 ;

_target = "ft"; 5280

) ;

_orig = "g" ; Case (_target = "kg"; 1 / 1000 ;

_target = "g"; 1 ;

_target = "lb"; 1 / 453.59237 ;

_target = "oz"; 1 / 28.349523

) ;

_orig = "kg" ; Case (_target = "g"; 1000 ;

_target = "kg"; 1 ;

_target = "lb"; 2.2046226 ;

_target = "oz"; 35.273962

) ;

_orig = "lb" ; Case (_target = "g"; 453.59237 ;

_target = "lb"; 1 ;

_target = "kg"; 1 / 2.2046226 ;

_target = "oz"; 16

) ;

_orig = "oz" ; Case (_target = "g"; 28.349523 ;

_target = "oz"; 1 ;

_target = "kg"; 1 / 35.273962 ;

_target = "lb"; 1 / 16

) ;

_orig = "m3" ; Case (_target = "m3"; 1 ;

_target = "cm3"; 1000000;

_target = "mm3"; 1000000000 ;

_target = "ft3"; 35.314667 ;

_target = "in3"; 61023.7

) ;

_orig = "cm3" ; Case (_target = "cm3"; 1 ;

_target = "m3"; .000001 ;

_target = "mm3"; 1000;

_target = "ft3"; .000035315 ;

_target = "in3"; .0610237

) ;

_orig = "mm3" ; Case (_target = "mm3"; 1 ;

_target = "m3"; .000000001 ;

_target = "cm3"; .001;

_target = "ft3"; .000000035315;

_target = "in3"; .0000610237

) ;

_orig = "ft3" ; Case (_target = "ft3"; 1 ;

_target = "m3"; .028316847 ;

_target = "cm3"; 28316.8;

_target = "mm3"; 28320000;

_target = "in3"; 1728

) ;

_orig = "in3" ; Case (_target = "in3"; 1 ;

_target = "m3"; .000016387 ;

_target = "cm3"; 16.3871;

_target = "mm3"; 16387.1;

_target = "ft3"; .000578704

) ;

)

] ;

_number * _factor

)

• ###### 9. Re: Help with Multiple Unit of Measure Conversion and Application

This is not as flexibile as setting up a conversion table. If a different conversion is needed, you have to redefine the function--which requires a developer. If, on the other hand, you use a conversion table, you simply add anew record to the table and you do not need a developer for that.