5 Replies Latest reply on May 1, 2012 9:41 AM by cowtrax

    Stock Balance



      Stock Balance


      Hi - I am looking at moving our sales system from MS Access to Filemaker, but have come up against a show stopper.  I hope there is an easy solution but just cant see it.

      I have a Stock table with a number of boxes and weight for each box, identified by a StockID for eah record.  My sales team then place orders into a OrderDetails table related by StockID.  The complication is that the can sell by different weight boxes

      Eg Stock Table shows 10 boxes by 6k available to sell.  OrderDetails table may have 3 records adding up to 5 boxes at 6 kilos and another record for 2 boxes at 3 kilos each.  Therefore total sold is 36 kilos (equivalnet to 6x6).  I need to be able to show on a product/stock screen and a sales screen that there are still 4x6 to be sold.  I can do this fairly easily in Access with select queries and grouping calculations by StockID ((Total Weight Available - Total Weight Sold)/Box Weight), but am a little lost how to do this in Filemaker

      I hope that makes sense..  Please Help


        • 1. Re: Stock Balance

          Is the standard weight of that product 6k?  If so, they must be breaking open the boxes to package them as 3k.  If you treated the product of having a U/M of 6k you could sell in fractional units.  Such as selling 2 boxes and .5 quantity.  The weight would be computed at 6K so the remaining qty in stock would be 4.  

          Does that make sense?  What our really doing is selling it by the weight rather than box.  Anyway, I think the standard unit of measure could help with a solution.  Hope it helps.

          • 2. Re: Stock Balance

            I think that a bit more detail will help here. You indicate that 3 records can "add up to" 5 boxes. It seems you are selling product which must then be packed in boxes--not a simple operation in any system as the weight of an item doesn't normally tell you how many can be packed in a given box--size and shape are also a factor--thus I'd like to see more clearly how the information on your order screen determines what boxes are used to pack the product.

            • 3. Re: Stock Balance

              Hi guys, thanks for the speedy replies.  I may have over complicated this for you.  Think of ot more a a total weight of something to sell.  So we may have 60k to sell in any given day.  How we sell that is set on each sale.  So it could be in 10 lots of 6k, or it could be in 9x6 and 2x3.  What i am tryiing to work out is how to add the sales records up for this priduct and then deduct it from the total weight to sell.  I can always diivide the anser by the 6k if i want to get an amount of "bioxes' afterwards.

              In Ms Access I would use a select query to sum the weight for all products grouped by StockID and then subtract this from the weight to sell in the Stock table again stored againsr a StockID.  Its fairly basic, but in the absense of queries in Filemaker I am a but lost.

              Sorry if i coinfused the issue and made it more complicated that needed.

              • 4. Re: Stock Balance

                Do you have this relationship design?


                Where a portal (Think sub form in Access) to OrderItems is used to list the items sold on a given order.

                Then a calculation field, ItemWgt, in OrderItems can be defined as:

                Qty * UnitWeight //of course if you are measuring Quantity sold by weight, you can just make this a number field.

                To get the total weight for that one line item.

                A calculation in Orders can then be defined as:

                Sum ( OrderItems::ItemWgt )

                To compute the total weight of that order. This is exactly how one computes lineitem and order total cost as well.

                You can also define a summary field in OrderItems as the Total of ItemWgt. This field can be used to get the totals for each StockID sold on a given date as well as the total for a given order.

                If total weight in inventory for each item is recorded in Products, you can calculate inventory Quantities remaining this way:

                In Manage | Database | relationships, make a new table occurrence of OrderItems by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as SameItemOrderItems .

                We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                Add it to your relationships like this:

                OrderItems::StockID = SameItemOrderItems::StockID

                Then a calculation defined to be evaluated from the context of OrderItems can be set up like this:

                Products::QtyONHand - Sum ( SameItemOrderItems::ItemWgt )

                This is just one approach, there are other options possible. Including one method where your OrderItems table is converted into an InventoryLedger where records in the same table can either add to the amount in inventory ( product recieved or product manufactured ) or they can deduct from the inventory total ( Product sold, product shipped, shrinkage, etc. )

                You can do a forum search for "inventory ledger" here in the forum to find several threads that discuss that approach.

                • 5. Re: Stock Balance

                  Can you just sell it by the weight.  It seems a customer could order any weight the need and it would be packaged up accordingly.  so if the price and quantity in stock is based on weight, it seems that would accomplish.  Am I making it too simple?  If it was sold in a fixed amount within  bag, then you could sell it by the bag/box.  Since the sales are based on weight, the unit of measure (stocking unit) and selling unit of measure would also be weight.