2 Replies Latest reply on Dec 18, 2013 11:33 AM by DerekCosta

    Using Portals to display related records from another table

    DerekCosta

      Title

      Using Portals to display related records from another table

      Post

           Hello again,

           I have run into another roadblock in building my database, and would greatly appreciate some assistance.  My database is being used for the purpose of logging cases.  The goal is to not log more than one case on the same individual for the same reason.  

           First, know that there is an "Employee Profiles" table where all employee data is stored, including name, employee id number, email, and so on.  There is a relationship set up between this table and an Active Cases table, set up via the Employee Name.  Duplicate employee names are prevented when creating employee profiles.  

           I have created a relationship between the "Active Cases" table (where cases are initiated and logged), and the "Case Storage" table (cases are moved to this table and deleted from Active Cases when marked as closed).  The relationship between these tables is based on the field EmployeeName.  

           When clicking "New Case", a new form linked to the Active Cases table appears with a drop-down list.  The list values in this list are pulled from a "Employee Profile" table.  What I would like is for a portal displaying records from related table "Case Storage" to show the 3 most recent cases for the employee selected, including the fields "Case Number", "Offending State", "Status", and "Modified Date" (all fields within the Active Cases and Case Storage tables).  

           I have created the portal and set it to display related records from Case Storage, and have it set to sort the records ascending based on date modified.  The issue is, nothing appears in the portal at all when selecting a name from the EmployeeName drop down.  

            

           This might not be related, but it also may be:  I have fields on the Active Case and Case Storage tables, including employee email, manager name, manager email, and role that auto-load from the employee profiles table after selecting an Employee Name in the drop down.   These fields all work perfectly.  

            

           Any ideas?  Thanks!!!

        • 1. Re: Using Portals to display related records from another table
          philmodjunk
               

                    (cases are moved to this table and deleted from Active Cases when marked as closed).

               This is not necessary in most situations and complicates your solution. What problem are you solving by doing this?

               

                    The relationship between these tables is based on the field EmployeeName.

               This sets you up for possibly serious problems with your database should you ever get two employees with the same name, if an employee changes their name, or an employee name is accidentally entered with incorrect spelling and related records are created before the error is identified and corrected. Using an employee number avoids those issues.

               I would guess that your portal is empty because you are on a layout for Active cases and have not yet committed the new record with an employee name. But that's a guess as you don't describe the layout on which you are placing your portal nor whether you link Case Storage to Active Cases or to Employees.

          • 2. Re: Using Portals to display related records from another table
            DerekCosta

                 Thank you for your reply!  It was enough information to get me thinking in the right direction, and I am pleased to say I got it going.   Thank you!!