3 Replies Latest reply on Feb 9, 2011 8:54 AM by philmodjunk

    Stock balance and finished products !!!



      Stock balance and finished products !!!


      Hi everyone

      I have a products table, join table and finished products table and products in table 

      I am produced valves. for example  in order to produced one valve i am using 6 treads, 1 valve body and 2 o-rings.

      when the sub products come to my factory i entered them with portal (treads, valve body, o rings exc)

      And also at the end of the day i want to enter finished products and automaticly subtract related treads bodys ext.

      I mean when i entered 2 finished valves. Filemaker understand that 12 treads 2 body and 2 orings used from stock. How can i do that

      So i should see stock balance clearly.

      Sorry for my limited English. 

        • 1. Re: Stock balance and finished products !!!

          You'll need to set up a "components" table related to your products table where you document the Bill of Materials needed to manufacture that product. That table can list both components and the quantities.

          Then you can use a script that uses this related table for a given product to compute the materials used. (You can even define a field to factor in a percent "wastage" field if you have statistics that help you predict the frequency in which a component is discarded due to damage during manufacture or rejectin due to it being defective and factor it into your calculations.)

          • 2. Re: Stock balance and finished products !!!

            How can i use a script. Could you please give me an example ?

            • 3. Re: Stock balance and finished products !!!

              Much depends on how you set up your table to monitor inventory levels.

              I prefer to use an InventoryLog table with fields such as these:

              TransDate  ComponentID Added Removed Description cBal sBalanceOnHand

              This table tracks each change in inventory as it occurs.

              ComponentID is the ID number of the component being added to or removed from inventory.

              Added and Removed fields record the quantity added (production, returns), or removed (shipped, shrinkage, etc.)

              cBal is a calulation field: Added - Removed

              sBalanceOnHand would be a summary computing the Total of cBal as a running total that restarts with the ComponentID field.

              Presumably, you'd also have a Production table where you log the production quantities for each product you produce.

              ProdDate ProductID Qty (you may also want a shift ID field as most manufacturers log production for each work shift.)

              You should have these relationships:

              Production::ProductID = Products::ProductID

              Products::ProductID = Components::ProductID

              InventoryLog::ComponentID = Components::ComponentID

              From a layout based on Production, this script could generate matching entries in the InventoryLog table:

              Set Variable [$ProdID ; value: Production:ProductID ]
              Set Variable [$Qty ; Value: Production::Qty]
              Freeze Window
              Go To Layout [Components]
              Enter Find Mode[]
              Set Field [Components::ProductID ; $ProdID]
              Set Error Capture [on]
              Perform Find[]
              Go To Record/Request/Page [First]
                 Exit Loop If [ Get ( FoundCount ) = 0 ]
                 Set Variable[$ComponentQty ; Components::Qty ] //Components::Qty would be the number of this component need to make one product.
                 Go To Layout [InventoryLog]
                 New Record/Request
                 Set Field [InventoryLog::TransDate ; Get ( CurrentDate ) ]
                 Set Field [INventoryLog::Removed ; $Qty * $ComponentQty]
                 Go To Layout [Components]
                 Go To Record [ next ; exit after last ]
              End Loop
              Go To Layout [InventoryLog]
              Sort [No Dialog ; restore] //sort records by ComponentID to group them correctly

              This is just to get you started. There are many variations possible. In fact, by adding some more script steps, the same InventoryLog table can be used to log your Products into inventory even as it logs consumed components out of inventory. You can also set up a layout to InventoryLog to log other changes in inventory such as shipping out products or receiving shipments of components.