5 Replies Latest reply on Apr 1, 2014 1:01 PM by jkostenbader

    Table Instances Question

    jkostenbader

      Title

      Table Instances Question

      Post

           Ok...still trying to warp my brain into table instances and when and how to use them. Consider the following...

           ACCOUNTS has a one-to-many relationship with PROJECTS. Each project has a "Responsible" person that is selected from the EMPLOYEE table. "Responsible" is stored as a full name in the projects table. On a layout based on EMPLOYEE, I would like to see data related to the projects they own. I have created a relationship between full name in EMPLOYEE and "responsible" in PROJECTS to accomplish this.

           Ideally I'd like a portal on this layout that lists all the projects this employee is responsible for (which works fine). Unfortunately the data is a little meaningless without the associated ACCOUNT description. Any which way I turn it lists only the first Account name and not subsequent. I know I need another table instance somewhere but I'm a little stumped on which one and how to draw the relationship. Presently the portal in question is based on the PROJECTS table. Consider the following relationship graph to better illustrate.

            

      Screen_Shot_2014-04-01_at_11.18.19_AM.png

        • 1. Re: Table Instances Question
          philmodjunk
               

                    "Responsible" is stored as a full name in the projects table.

               Not a good idea. Use an Employee ID number instead. Names are not unique. Employees change their names and they are vulnerable to data entry errors.

               Note also that your primary keys (such as account_id in accounts) are not set up to be unique--which I can see from the "crows feet" in the relationship graph. An auto-entered serial number is your best choice for a primary key in almost all situations. If you have an account number that is assigned from an outside source, make that a data field in accounts, but don't use it as your primary key.

               If your layout specifies Employees in Layout Setup | Show Records from and your portal on that layout specifies projects in Portal Setup | Show Related Records From, then you should be able to include fields from accounts inside the portal row to show account data for each project given the many to one relationship from projects to accounts.

               And I recommend that you use an Anchor Buoy approach towards organizing your table occurrences into groups, each group set up to support the function of a specific layout or group of similar layouts. See this article for more on the subject: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

          • 2. Re: Table Instances Question
            jkostenbader

                 Yea...kinda knew I'd get called on that one. Admittedly some of this was an after thought added after I thought out the original design. Not an excuse for bad design though and I do know better. I do have account ID as Indexed, Auto-Enter Serial, Can't Modify  Auto, Required Value, Unique. I did make two modifications because of your observation

                 1. Unchecked "Allow user to override during data entry"
                 2. Checked "Prohibit modification of value during data entry"

                 I will make the modifications and use Employee ID instead of Full Name and we'll see where that goes and I'll take a look at the Anchor Buoy reading you recommended. 

                 As always your advice is timely and very useful. I appreciate your responses!

                 John

            • 3. Re: Table Instances Question
              philmodjunk

                   IN my system, I only need define the field as an auto-entered serial number and it will show in the relationship graph as a single connector line instead of the crows feet that I see in yours.

              • 4. Re: Table Instances Question
                jkostenbader

                     Ok...will take a look at that modification as well...thank you again

                • 5. Re: Table Instances Question
                  jkostenbader

                       Just wanted to let you know it worked like a charm. Not to mention a much better design. Thanks again

                       John