Security Id (Serial)
Shares Owned: Cal (Sum Line Items: Shares Owned )
Current Price: (Trade Price Updated Daily)
Current Value = Shares Owned * Price
Table: Line Items
Shares Owned: (Shares Owned - Shares Sold)
Account Id (Serial)
Account Balance: Cal (Sum Securities:Current Value)
Above diagram is a simplified version of my solution.
I want to get the Account Balance by totalling value of Securities: Current Value which worked perfect until recently (there are approx 220 records in line items).
What i discovered is recently i purchased shares in Suncor Energy from Account A but already held Suncor Energy shares in Account B.
The problem with my setup is Account Balance for Account A is summing the Securities:Current Value of all the shares i own of Suncor and not just the shares of Suncor held by Account A. And Account B is doing the same
Is it possible to create a new relationship or add fields that will allow Account Balance to only get current balance of shares that relates to that account.
The Account Balance Field is displayed on my Accounts layout
I am running filemaker 14