2 Replies Latest reply on Jan 21, 2013 10:59 AM by CharlesDestrempes

    Multiple portals/relationships to same table off different key fields...how to display related...

    CharlesDestrempes

      Title

      Multiple portals/relationships to same table off different key fields...how to display related records?

      Post

           I've tried a couple of different things and not found how I'm suppose to set up so that the following will display appropriate related records.

           Table 1: Applications

           Table 2: Employees

            

           Applications have an employee perform an interview and reference check.  I created two fields (InterviewerID and RefCheckerID) in Applications and related them to EmployeeID in the employee table.

           I want to be able to do the following:

             In applications display additional information about the employee (e.g. a portal/related fields) for *both* the Interviewer and the Reference Checker.  Within the employee table I want to have two portals; one lists records where the employee was the interviewer (Application::InterviewerID = EmployeeID) and one where the employee was the Reference Checker (ApplicationID::RefCheckerID = EmployeeID).

           I've tried a couple of different things (difference instances in the relationship graph, applying a filter on to the portal, etc.) however I have not found a way to make the related records display when InterviewerID and RefCheckerID are both related over to EmployeeID - individual each works fine, together I get blank displays.

           How do I "tell" the portal or related field to relate based on a specific "starting" field for that particular view?

        • 1. Re: Multiple portals/relationships to same table off different key fields...how to display related...
          philmodjunk

               The secret is in using mutiple occurrences of your data source tables Employees and Applications.

               InterviewingEMPLOYEES-----<Applications>------CheckingEMPLOYEES

               InterviewingEMPLOYEES::__pkEmployeeID = Applications::InterviewerID AND
               CheckingEMPLOYEES::__pkEmployeeID = Applications::RefCheckerID

               Will allow you to put fields from InterviewingEMPLOYEES on your Applications layout to show info abou the interviewing Employee. And you can place fields from CheckingEMPLOYEES on your Applications layout to show details about the employee assigned to check references.

               The "boxes" found in manage | Database | Relationships are called "Table Occurrences" they represent specific tables defined on the Tables tab. To create InterviewingEMPLOYEES, you click the Employees box, then click the duplicate button (two green plus signs). You can then double click this new "occurrence" to open a dialog where you can rename it like I have.

               This does not create a new Table. It creates a new reference to an existing table.

               Note that this only permits you to assign one interviewing employee to a given application and only one reference checking employee to a given application. If you need to make multiple assignments for either of these tasks for a given application record, a different structure is needed.

               To see the two lists all Applications for a given Employee in a portal, you can start with Employees as your layout's table occurrence, and link in two more occurrences of Applications, each linking by a different match field in applications.

               If "Table Occurrence" is a new concept, you may find this tutorial helpful: Tutorial: What are Table Occurrences?

          • 2. Re: Multiple portals/relationships to same table off different key fields...how to display related...
            CharlesDestrempes

                 Ah...okay.  I had it reversed.

                 I tried creating two additional references in the graph for Applications...not Employee  (ApplicationsInterviewer - which defines InterviewerID->Employee::EmployeeID and ApplicationsRefChecker - which defines RefCheckerID->Employee::EmployeeID).  Both of these went to the same instance of "Employee"

                 So there was no direct relationship between  "Applications" and 'Employee" in the table graph...which meant I couldn't select anything in the portals when I tried this since there was no relationship.

                 I tweaked it and the above worked as I'd wanted.

                 Thanx for the quick response.

                 CharlieD