AnsweredAssumed Answered

Stock Balance

Question asked by joolzm on May 1, 2012
Latest reply on May 1, 2012 by cowtrax


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