AnsweredAssumed Answered

How to display running total by parts number in other layouts?

Question asked by DaWood on Mar 26, 2012
Latest reply on Mar 26, 2012 by DaWood


How to display running total by parts number in other layouts?



I'm totally a newb in this and I have just started using filemaker for two weeks now. I tried searching in the forum to see if there's any anwsers, but I can't find any, and I hope someone can help me out.

I want to create a parts/inventory DB where it shows the current stock level of each parts via using two tables. I have developed one layout/table for showing records of the types of part we have (ID by parts number), and another table for inventory control using the check registry concept. The parts layout/table will be the GUI and the inventory table will be the "programming." The goal I'm trying to acheive is showing the running total in the parts layout. Here's the field names and function for each layout/table:

Parts Layout/Table

PartsNo = Text

PartsName = Text

::sBal = summary 

Inventory Layout/Table

PartsNo = Text

PartsName = Text

InQty = Number

OutQty = Number

cBal = InQty - OutQty

sBal = summary of cBal (sorted by PartsNo., Date)

DateEntered = Date (automatic, on creation)

I have sorted the inventory table by parts number so I can get a running total of each parts number. Then I added the "::sBal" field in the parts layout to display the running total from the inventory table. The problem I'm having is that I keep getting the running total of the first record (after sorting). For example, I have three inventory record entries of PartNo 1, with InQty of 10, OutQty of 2 and 4, respectively, to get a running total of 4. I want to show in the parts layout that PartNo 1 now has 4 in stock. But the field keeps displaying 10. At this point, this would only work if you don't plan on keeping track of inventory history, but you would sacrifice the visual on supply/demand cycle. My initial thought was to sort decending by sBal to get the running total at the top, but it doesn't give me that option. Tried 'Reordered by summary field' but that only applies if you are showing all records of different parts. There could be a set of scripts that may give me that option, but I'm too much of a newb at that level.