Inventory in Multiple Locations
I'm building an inventory system for my workplace that includes (I hope) the ability to see how much product is in each of our locations. I'm working from a table that lists each location (the Move Table) and then a related ledger table which records Move From ID, Move To ID, Item ID, and then either Quantity Moved or Quantity Removed (depending on whether the item was just shifted from one location to another or sold).
I've got the system up and running in most areas but how exactly to track how much of a given item is in each location is stumping me. I've messed around with portals and summary fields and calculations but I can't seem to get even a start, which is doubly frustrating given that I feel like all the correct data is sitting there in the ledger table waiting for me, if only I could figure out which angle I needed to sink my teeth into it.
Does anyone have any suggestions or insights that might get me going?