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

    Help with Multiple Unit of Measure Conversion and Application

    starstuff

      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!

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

          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
            starstuff

            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
              starstuff

              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
                philmodjunk

                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
                  starstuff

                  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
                    philmodjunk

                    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
                      starstuff

                      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
                        user17577

                        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
                          philmodjunk

                          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.