6 Replies Latest reply on Jun 16, 2016 1:31 AM by HammerOz

    Relationship Setup Wrong (Need another relationship??)

    DennisFlight

      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

       

      Thanks

        • 1. Re: Relationship Setup Wrong (Need another relationship??)
          fitch

          Looks like you should have a join table between accounts and securities. This table would store the security ID, the account ID, the purchase date, price as of that date, quantity, etc.

          • 2. Re: Relationship Setup Wrong (Need another relationship??)
            DennisFlight

            Thanks for response. There is a join table called line items with all the fields you mentioned plus a few more. And everything worked fine with the cal for each account bal until I bought some of the same stock (Suncor energy) in multiple accounts.

             

            thanks

            • 3. Re: Relationship Setup Wrong (Need another relationship??)
              keywords

              The whole purpose of a join table is to break down such many-to-many connections into a series of one-to-many joins. It seems to me you will have to break down the stock purchase you refer to into a series of separate line items, each joined to a separate account. Then it should continue to work—assuming it was actually working before and not just appearing to because you hadn't really pushed this boundary before. By that I mean that your line item table may actually not be a proper join table, but a many-to-many between purchases and accounts which looks right because hitherto every purchase has been for a single account.

              • 4. Re: Relationship Setup Wrong (Need another relationship??)
                DennisFlight

                Thanks for your reply.

                 

                so instead of relationship being Accounts ---- Line Items---- Securities

                 

                it should be   Accounts ---- Purchases ----- Line Items ----- Securities??

                 

                • 5. Re: Relationship Setup Wrong (Need another relationship??)
                  fitch

                  I see now that there is a join table. You may need an additional join table, which would store the current value of each security for a given account. Kind of like a typical Account<Invoice<Line Item-Product scenario, substituting Security for Product, and this new table for Invoice.

                   

                  Edit: our replies crossed. Yes, I think you have the idea.

                  • 6. Re: Relationship Setup Wrong (Need another relationship??)
                    HammerOz

                    Hi Dennis

                     

                    The above looks over complicated to me. Instead have

                     

                    Accounts < Transactions < Securities (table occurrence order is going vary depending on report requirements)

                     

                    Transactions volume field gets - for sold and + for buy. (or make a more human friendly with buy / sell check field that then updates a system field to -/+ of transaction volume)

                     

                    So no Line items ( holdings ) table. It would be a calculated summary derived from transactions.

                     

                    So the occurrences to get a summary of the holdings:   Accounts < Securities < Transactions

                     

                    With calculations summing the net value of the transactions for each stock and then at an account level the total value. Can then filter if net holdings is null etc

                     

                    I think that is how I would approach it with my old commodities trading hat on. I may test it tonight with a bottle of wine and some fanciful share purchases that look better than my own portfolio