AnsweredAssumed Answered

Calculation thru relation or subset of records?

Question asked by liquidcooled on Jan 12, 2010
Latest reply on Jan 13, 2010 by philmodjunk

Title

Calculation thru relation or subset of records?

Post

I'm new to FM and I'm creating a multiple location database. I'm currently working on showing totals for each item in each location.

I have 3 tables that I think the data should relate from.

 

My Inventory list (which shows the general description of my items)

Item ID

Name

cQtyonHand (which ads up the cItemBal from the Item_transaction table) 

 

Item Transaction Table 

fk_ItemID

fk_LocationID

fk_transactionID

QtyAdd

QtyRmvd

cItemBal (QtyAdd-QtyRmvd)

 

Location Table 

_pk_locationID

Location Description 

 

I currently have a lay out based on the Inventory list which shows Item information total on hand and has a portal to the location table and displays where the items are. What I want to add to that portal is how many of each item are in each location. It should be a calculation that totals the cItemBal, but only for the item on that layout and for each location it is in. So I think that its somehow but a subset and relational, but I'm not getting it to work.

 

Thanks 

Outcomes