AnsweredAssumed Answered

Relationship Setup Wrong (Need another relationship??)

Question asked by DennisFlight on Jun 14, 2016
Latest reply on Jun 16, 2016 by HammerOz

Table: Securities                                                                                                   

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

Security Id

Account Id

Security Name

Account Name

Shares Bought

Shares Sold

Shares Owned: (Shares Owned - Shares Sold)


Table: Accounts                                                                                                                       

Account Id (Serial)

Account Name

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