4 Replies Latest reply on Jan 13, 2011 10:15 AM by DanielClark

    Related Records and Table Occurences

    DanielClark

      Title

      Related Records and Table Occurences

      Post

      I have four tables - a Projects table, a Jobs table, a Contacts table, and a Timesheets table. In the Jobs table I have a _ProjectID key field which is my related field to the primary key field ProjectID from my Projects table. This works great so I can use the ProjectID relationship to fill in the ProjectName, ProjectDescription, etc fields in my Job table via this relationship.

      In my Timesheets table, I have a _JobID key field which is related to the primary key field JobID from my Jobs table. This is also great as I can pull JobName, JobDescription, and even ProjectName, and ProjectDescription from the Jobs and Projects table via this relationship (and the Jobs-Projects relationship).

      However, I also have a key field in my Timesheets table called _ContactID, that I want to pull a Contact's ID and have it fill in the Contact's name and other info in other tables. I created a Value List that has the ContactID's in it, and I set up my other fields to pull further data from the Contacts table. However, even though the Value list worked, and I can have it display my ContactID, I can't get the other fields to fill in.

      I tried solving this by creating a second table occurrence called Timsheets2 and a field _ContactID. I then related this field to the primary key field ContactID from the Contacts table. However, this didn't work either (it didn't fill in additional contact information in related fields when I selected the ContactID from the Value List pop-up field). Basically I want to have two tables that aren't related to each other - relate to my Timesheets table.

      So am I doing this wrong? Do I not understand how table occurrences work? Is there a solution to my problem? Thanks.

        • 1. Re: Related Records and Table Occurences
          philmodjunk

          Unless I've missed something, you've described this relationship:

          Projects-----<Jobs----<TimeSheets   (---< means one to many )

          Where did you relate contacts?

          Instead of creating a new occurence of TimeSheets, just link an occurrence of contacts directly to TimeSheets. (It needs to link to the same table occurrence you selected in Show Records From for your TimeSheets layout.)

          For more on Table Occurrences: Tutorial: What are Table Occurrences?

          • 2. Re: Related Records and Table Occurences
            DanielClark

            Yes that relationship is correct. However, I was planning on having the Timesheets table layout to be able to select multiple Jobs. (ex. Monday - Job1 for 4 hrs and Job2 for 4 hrs for EmployeeID 1 John Smith). I was having second thoughts if maybe I should just use a portal for the Jobs table?

            Contacts is not part of the Projects--<Jobs--<Timesheets relationship. If I try to link my Contacts table to Timesheets (drag ContactID from Contacts to _ContactID in Timsheets), it says it needs to create another table occurrence because "There cannot be more than one relational path between any two tables two tables in the graph. Another table occurrence of one of the tables must be added to the graph."

            • 3. Re: Related Records and Table Occurences
              philmodjunk

              Contacts is not part of the Projects--<Jobs--<Timesheets relationship. If I try to link my Contacts table to Timesheets (drag ContactID from Contacts to _ContactID in Timsheets), it says it needs to create another table occurrence because "There cannot be more than one relational path between any two tables two tables in the graph. Another table occurrence of one of the tables must be added to the graph."

              If you are getting that message, then Contacts IS part of that relationship. You've got it linked in somewheres or you wouldn't get that message popping up. No matter, go ahead and have it create the new table occurrence for contacts (not timesheets). Then use this new table occurrence for displaying related contact info on the timesheets layout.

              Timesheets table layout to be able to select multiple Jobs. (ex. Monday - Job1 for 4 hrs and Job2 for 4 hrs for EmployeeID 1 John Smith).

              If you log those hours on separate records in TimeSheets, that relationship should work for you as each record may be linked to a different Job record.

              • 4. Re: Related Records and Table Occurences
                DanielClark

                Ah ok gotchya, yeah my Contacts was related to it - but it wasn't in that chain - it was on its own related on a separate chain, if you will. Thanks again!