4 返信 最新の回答 日時: Jun 11, 2015 6:27 PM ユーザー:starstuff

    How to track inventory with multiple unit of measure?

    starstuff

      タイトル

      How to track inventory with multiple unit of measure?

      フォーラムに投稿

      Hi All!

       

      The products i need to track are in different / multiple unit of measure from purchase to sale.

      example:

      you purchase the product by "Box"

      and you sell it per "Piece"

      sometime you sell it package like per "Dozen", "Half-Dozen"

       

      ive been thinking how to handle this, im afraid that the inventory can result in wrong inventory records.

       

      my idea is to have separate unit of measure to track each inventory

      product: Eggs, i have to track dozen, half-dozen, pieces

      say, purchasing a

      2 dozen eggs = 24 pieces of eggs = 4 half-dozen

      i would have a calculated record for each units

      dozen = 2

      pieces = 24

      half-dozen = 4

       

      if then i sold 2 half-dozen eggs, the record will be

      dozen = 1

      pieces = 12

      half-dozen = 2

       

      then i sold another 5 pieces of Eggs, the record will be

      dozen = 0.58333 (not sure if its correct)

      pieces = 7

      half-dozen = 1.1666666 (not sure if its ocrrect)

       

      the problem i see here is that because i have several units of inventory to track, means there is a greater chance that a unit inventory can go wrong?

       

      i would like to ask some advice on what to do? anyone had encounter this before?

       

      Thanks All!

       

       

        • 1. Re: How to track inventory with multiple unit of measure?
          Shin

          The solution is using function Div() and Mod()

          Example :

          n pieces

          = Div ( n ; 12 ) & "dozen" & Mod ( n ; 12 ) & "pieces"

          = Div ( n ; 6 ) & "half-dozen" & Mod ( n ; 6 ) & "pieces"

          • 2. Re: How to track inventory with multiple unit of measure?
            starstuff

            Thank you Shin!

            i can see that if i put in n pieces it will divide it , its amazing!!

            i like to ask you some more question,

            just curious, what is the difference of using the function Div and the operator " / "?

            and

            can you advice me with adding and removing inventory with multiple units using the Divs and Mods?

            Table : Products

            Fields :

            inventory_count = number =  current inventory count based on base unit of the item (pieces)

            inventory_count_purchase = current inventory based on purchase unit of the item (dozen) 1 dozen = 12 pieces

            inventory_count_sales = current inventory based on sales unit of the item (half-dozen)

            unit_base = base unit of the item (pieces) 12 pieces

            unit_purchase = purchase unit of the item (dozen) 1 dozen = 12 pieces

            unit_sales = sales unit of the item (half-dozen) 1 dozen = 2 half dozen = 12 pieces

            unit_list = list of available units = List( unit_base; unit_purchase; unit_sales)

            qty = number qty added or removed

            unit_selected = unit selected from the unit_list as value list

            inventory_solver = solves the inventory count

             

            which is better to use a Case statement or an IF statement?

            Example. calculated field - inventory_solver

            Case ( Products::unit_selected = "dozen" ; Div ( inventory_count ; 12 ) ;

            Products::unit_selected = "half-dozen" ; Div ( inventory_count ; 6 ) ;

            Products::unit_selected = "pieces" ; Mod ( inventory_count ; 1 ) )

             

            or (not sure about below)

            IF (Products::unit_selected = "dozen" ; Div ( inventory_count ; 12 ) ;

            Else

            Products::unit_selected = "half-dozen" ; Div ( inventory_count ; 6 ) ;

            Else If

            Products::unit_selected = "pieces" ; Mod ( inventory_count ; 1 ) );

            End If

             

            Once i got the "inventory_count" based on the "unit_selected", i can then now proceed to removing or adding inventory

            using field qty

            if i put n qty

            inventory_count substract or add n qty

             

            I think my process is not good, can you advice here to better the process?

             

            Thank You!

            • 3. Re: How to track inventory with multiple unit of measure?
              Shin

              The difference of using the function Div and the operator " / " is below :

               Div ( 13 ; 12 ) = 1 , returned nmber is integers

              13 / 12 = 1.083.... , returns decimals

               

              which to use a Case statement or an IF statement is case by case. If using in calculation field, case is better, but in script statement, both method are usable.

               

              Thanks

              • 4. Re: How to track inventory with multiple unit of measure?
                starstuff

                Thanks Shin!