AnsweredAssumed Answered

Inventory by Account

Question asked by AlexR on Aug 16, 2011


Inventory by Account


I have 3 tables: Accounts, Transactions, Products.

Accounts has one field: AccountNumber

Products has 2 fields: ProductName and a calculation field= sum(Transactions::Qtty)

Transactions has 4 fields: Tr_acct, Tr_prod, Tr_Date, Tr_qtty (Tr_qtty can be negative or positive depending on whether it's a buy or a sell). The three tables are linked with Accounts::AccountNumber=Transactions::Tr_acct and Tr_prod=Products_ProductName.

I would like to have a layout on which I can select an account number, select a date and have a Portal display the "inventory" for that particular account on that date. I do not want to see a product in the portal for which the balance is zero.

I know this must be very simple, but I just can't seem to get it to work.