1 Reply Latest reply on Oct 1, 2013 9:56 AM by pmconaway

    Best Practice: Main-Account & Sub-Accounts

    ecoast30

      Hello:

       

      Not sure the best way to skin this cat: Here is my set-up: I have a table of entities: (clients, trusts,, decedents), which will have there own unique value. Ultimately i want to be able to create a hierarchy within the table so that trusts and decendents can show up as portal when looking at the clients record, but keeping all entities as a seperate record in the same table.

       

      Example:

       

      A1 Client 1

      A2 Client 2

      A3 Trust 1

      A4 Client Decendent 1

       

      When looking at record A1 i want the portal sub-accounts to show record A3 Trust 1 and A4 Client Decendent 1.

       

      I think the solution is a self-join table, but havent been able to get there. In each record i will have a "master" field which will allow you to select the master. But in the master record what would i pick in that drop down?

       

      Let me know what else i need to flesh out to help solve this one.

       

      Thanks,

       

      Jason

        • 1. Re: Best Practice: Main-Account & Sub-Accounts
          pmconaway

          Jason, my first question is: Why does this all need to be in the same table? This probably will be simpler if you have a related table to handle trusts and decedents. If you are still going to use one table then at a minimum you will need to add a record type field to the table and also at least two other foreign key fields. I will use your example above to illustrate.

           

           

          A1 Client 1 ID =01234 RecType = Master IDF_Trust = empty, IDF_Decedent = empty

          A2 Client 2 ID =02345 RecType = Master IDF_Trust = empty, IDF_Decedent = empty

          A3 Trust 1 ID = 23456 RecType = Trust IDF_Trust = 01234, IDF_Decedent = empty

          A4 Client Decedent 1 ID = 45667 RecType = Decedent IDF_Trust = empty, IDF_Decedent = 01234

           

          Your layout would display only Master record type and the portal in that layout would display Trust and Decedent records.With the foreign keys you have the ability to relate (even if it is a self relation) back to the master file. You could probably make this work with just one foreign key instead of one for each type of record. Also make sure your keys are arbitrary. Not based on any type of data. Some of the solutions I have created I've started using the Get(uuid) function as a key generator.

           

          I hope this helps.

           

          Paul