1 Reply Latest reply on Apr 10, 2013 1:59 PM by philmodjunk

    Can Job Table hold more than one Task foreign key



      Can Job Table hold more than one Task foreign key


           I have a Job Table in which a new record equals each job. There is a long list of Tasks (in a Task table) from which the User can select any number to be part of the Job.

           Production needs the Job with only the Tasks selected by the User. Production will make notes on each Task as they work them.

           Some how I have confused myself on this should work.



        • 1. Re: Can Job Table hold more than one Task foreign key

               I think you mean that for any given record in the Job table you need to link to more than one record in Tasks, but that it is also true that any given task may need to be performed for more than one job.

               Is that correct?

               If so, you have a many to many relationship and you need a third table, a "join" table that facilitates the links between records in the two tables:


               Jobs::__pkJobID = Job_Task::_fkJobID
               Tasks::__pkTaskID = Job_Task::_fkTaskID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               A Portal to Job_Task can be placed on the Jobs layout to list all Tasks specified for that Job. A portal to Job_Task on the Tasks table would list all Jobs that specify that task. When you place a portal to Job_Task on your Jobs layout, you can include fields from Tasks in the portal row to provide information about that specified task.

               With those relationships a text field (or a 4th related table) can be used to record notes about a task linked to a specific Job.