3 Replies Latest reply on Sep 2, 2011 9:14 AM by philmodjunk

    Inventory - How to?



      Inventory - How to?


      I have three different tables on my database related to inventory:

      • The products table -where stock amounts are stored
      • A Line Items table -where quantity of each order is stored
      • An the invoice table -where a portal shows product info and quantity ordered
      However, I just can't figure out how to subtract the quantity the user inputs in the invoice portal (line items table) from the stock field for each particular item (a field that is stored in the products table). Can somebody please shed some light on how is this achievable?

        • 1. Re: Inventory - How to?

          The typical relationship for such tables is:


          A calculation field in Products can use the sum function to compute the total ordered for each product. The sum function would look somthing like this:   Sum ( LineItems::Qty ).

          You can also define a summary field in LineItems that computes the total of Qty. If you place this summary field on your Products layout, it will compute the same total.

          But how do you intend to add to your inventory totals when new product is received and/or manufactured?

          • 2. Re: Inventory - How to?

            Yes, the current relationship is defined just like that. However, I got a question regarding that Sum calc field. If I use this method, wouldn't it calculate the quantity total for the entire life of the database? Say, if it's been operating for 4 months and clients have placed 200 orders for 1 piece each and my current stock is 20 pieces, it will be put off by some 180 units, is this appreciation correct? 

            As for how do I intend to add new products, I currently have a field in products that the user uses to capture new product qty. as soon as it gets delivered on a reorder; this field is added to the current stock and total is reflected on the stock field. I know it's pretty crude, but this is the first time I deal with inventory.

            • 3. Re: Inventory - How to?

              A question mark means that FileMaker was unable to display the value in the field. In this context, best guess is that your field needs to be resized wider to display the entire number. (You also get this with math errors such as dividing by zero, but we aren't doing that kind of calculation here.)

              Sum ( RelatedTable::Field )

              Computes the total of the specified field for all related records. Thus, If you had two records in your Products table: Widgits and Thingamajigs with unique product IDs, this calculation returns the total of all ordered Widgits on the widgits product record and all ordered Thingamajigs for the Thingamajigs record. Yes, this will be over all the records in your LineItems table, but then that's what you'd need to compute an inventory level by subtracting this value from your total of all items received.

              A more sophisticated approach that you can take is to turn your LineItems table into an Inventory Ledger where you log in both the sale and reception of your products. A combination of Calculation and summary fields can then provide not only your current inventory level, but a running total that you can examine on a layout based on the line items table to see how your inventory levels fluctuate over time--which can help you adjust re-order points to reduce tying up as much capital in inventory.

              If you search this forum for "Inventory Ledger", I think you'll be able to pull up several threads that discuss this approach if you are interested.