3 Replies Latest reply on Jan 27, 2017 2:14 PM by philmodjunk

    Cross Reference Table Issue

    JulioSandoval

      I currently have a problem with trying to assign two employees to one record.

       

      I have an employees table, and I also have an employee type table.

      These two tables are part of a cross reference table.

      The cross reference table is linked to a scheduling table.

       

      Screen Shot 2017-01-27 at 12.23.44 PM.png

       

      Lets say I have an Inspector Type and Outreach Type of employees.

      Each does a different thing.

      They are assigned to tasks and each one does something differently in said task.

       

      Assigning an inspector is no problem. One inspector to each task.

      I created a separate cross reference table for the Outreach Type employees.

       

      Screen Shot 2017-01-27 at 12.26.16 PM.png

       

      The employee table and employee type tables are the same for both.

      The only difference is the cross reference table.

       

      My main issue is, when I assign an outreach personnel to records related to each other, a few of them assign said employee to the others...

      Screen Shot 2017-01-27 at 12.27.50 PM.png

      Example, if Employee 3, Test is switched to Employee 1, Test, a group of them also change.

      Screen Shot 2017-01-27 at 12.28.32 PM.png

       

      Thoughts on where I went wrong and better alternatives all welcome.

       

      Thank you!

        • 1. Re: Cross Reference Table Issue
          philmodjunk

          What is the purpose of the two different join (Cross reference) tables?

           

          In terms of your work flow, (not in terms of data base relationships) do records in the one relate to records in the other?

          • 2. Re: Cross Reference Table Issue
            JulioSandoval

            Yes. An employee is assigned to a task. But another employee is assigned to same task and they have a different job description.

             

            I figured out my problem though. The outreach_Property_CR table was supposed to have nothing but foreign keys.

            I then was also supposed to assign employee types to a second group of employees in the outreach_EMPLOYEE table using the proper relating field.

             

            Once I did that, everything works normal. Each field I select an employee is now only changing for its respective record it relates to.

             

            I apologize for any fuss.

             

            Screen Shot 2017-01-27 at 2.08.07 PM.png

            This is what the working relationship looks like.

             

            Thanks Phil!

             

            oh, p.s. MirrorSync is pretty fun. ^.^

            • 3. Re: Cross Reference Table Issue
              philmodjunk

              Yes. An employee is assigned to a task. But another employee is assigned to same task and they have a different job description.

               

              Hmmm, that does not require two different join tables--which was something that I suspected might be at issue. A commonly used approach for this would look like this:

               

              Employees----<Employee_Task>------Tasks

               

              Employee "Types" or "roles" would be documented by entering/selecting data in the Employee_Task table, the join table. If you have additional info documenting each role that is standardized (you need to show the same info for every case where an employee is assigned as an inspector, for example) you can link in another table to Employee_Task that links to that role field.