5 Replies Latest reply on Jun 9, 2014 9:07 AM by philmodjunk

    Deducting From Inventory



      Deducting From Inventory


           My company makes a medical device.  I have built a database that includes separate inventory entry for every type of screw, wire, computer board, and plastic used in the device.  

           When one of the devices is built, I'd like to be able to create another database of final product including the individual device serial numbers.  When that happens, I'd like to be able to deduct from inventory the parts that went the final device.  I'm not sure how to do this so it automatically deducts the items.  Can anyone point me in the right direction?

        • 1. Re: Deducting From Inventory

               You might want to consider this design approach: Managing Inventory using a Transactions Ledger

               Which avoids the need to update a separate inventory table in order to track current inventory levels. Using it in your situation will likely require some extensive modification as you'll need to include the use of a BOM (bill of materials) table to document all of the parts that make up each item.

               But a key detail to any such discussion of inventory management via database are those part serial numbers.

               Some inventory systems have one record for each type of item with a count for the number of items currently in inventory. But other inventory systems have one record for every individual item--with the item's manufacturer supplied serial number recorded so that when an item is removed from inventory, you don't log the removal of "1 machine screw of type/size ABC", but rather log the removal of: "1 machine screw of type/size ABC, serial number ac3456678".

               Which method do you intend to use?

          • 2. Re: Deducting From Inventory

                 Here are more details:

                 I have built a database that represents our BOM, including every screw, fan, and circuit board.  We build a medical device.  What I'm trying to do is create a field so that the production manager can input in one place that he is going to pull materials for let's say 6 devices.  Assume for example that 2 screws, 1 mother board, and 3 wires are needed.  There is an entry in the database for each of those parts, with individual inventory counts.

                 Once the Production Manager somehow enters the number 6, then a calculation will be automatically run across the database to deduct the needed parts from each inventory record, 12 Screws (6 devices x 2 screws), 6 mother boards (1 Motherboard x 6 devices), and 18 wires (3 Wires x 6 Devices).

                 How do I do this?  I can't use Global entries, because another part of my inventory formulas use stored numbers and Filemaker doesn't seem to like that so much...any ideas?

                 I've included a Screen shot for an explanation

                 Beginning Count = The inital count of the inventory

                 Purchased Units = Subsequent Additions to the Inventory

                 Returned Units = Inventory parts that are being returned or pulled out of inventory

                 Inventory Adjustment = allows for inventory adjustments during periodic counts

                 Cumulative Use Units = Cumulative Use Units + Build Count (this is the running total of builds for the product manager

                 Current Inventory = (Beginning Count + Purchased Units) - (Returned Units + Inventory Adjustment + Cumulative Use Units)

                 Build Count = Number of devices that need to be built.


                 Thanks for any help.  Please forgive any spelling mistakes....I'm THIS close to the loony bin...

            • 3. Re: Deducting From Inventory

                   What I am suggesting is that this process would use a script to loop through the BOM and create transaction records that "log out" those materials.

                   The "inventory ledger" method described in that other thread uses such a transactions table to log adding product to and removing it from inventory. This not only allows you to see current inventory levels, but you can review the transactions log table to see how your inventory levels rise and fall over time as a way to adjust reorder levels for the various materials listed in your BOM. (If you see frequent inventory levels near zero for a given part, you might make an additional order for more or adjust the reorder level higher so that a new order for more is placed sooner in the future. If you see high levels, you might skip a re-order or even adjust reorder points to a lower level...)

              • 4. Re: Deducting From Inventory

                     How might I do that?

                • 5. Re: Deducting From Inventory

                       That's a tall order. There's a lot to the answer to that question.

                       Start with these relationships:


                       Inventory::__pkInventoryID = Transactions::_fkInventoryID
                       Inventory::__pkInventoryID = BOM::_fkInventoryID
                       Inventory|Components::__pkInventoryID =BOM::_fkComponentID

                       Inventory and Inventory}Components are two Tutorial: What are Table Occurrences? with the same data source table.

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       Then a script to log the removal of a single product and all the components listed in its BOM might be patterned after this example:

                       #Script must be run from a layout based on Inventory
                       Set Variable [$IProdID ; value: Inventory::__pkInventoryID ]
                       Set Variable [$CompList ; Value: List ( BOM::_fkComponentID ) ]
                       Set Variable [$QtyList ; Value: List ( BOM::Qty ) ]
                       Go to layout ["Transactions" ( Transactions ) ]
                       New Record/Request
                       Set Field [ Transactions::_fkInventoryID ; $ProdID ]
                       Set Field [ Transactions::QtyOut ; 1 ]
                          Set Variable [$K ; value: $K + 1 ]
                          Exit Loop if [ $K > ValueCount ( $CompList ) ]
                          New Record/Request
                          Set Field [ Transactions::_fkInventoryID ; GetValue ( $CompList ; $K ) ]
                          Set Field [ Transactions::QtyOut ; GetValue ( $QtyList ; $K ) ]
                       End Loop

                       This logs a single product and components out of inventory. To log out multiple items, you can either use a loop that performs the above script multiple times, or you can put the product Qty in a variable and use it in place of 1 in the first Set field [ Transactions::QtyOut... step and multiply it by the Qty returned from the $QtyList in the second such Set field step.