5 Replies Latest reply on Mar 8, 2017 2:35 AM by mikerevans

    Best way to assign tasks to a Work Order record

    mikerevans

      Hi all,

       

      Apologies if you already read yesterdays post, but i'm really struggling here.

       

      I have two tables, "Work Orders" which will contain the Work Order records. Each work order record will need to contain multiple tasks, which will need to be referenced from a list of set Tasks and associated descriptions and costs. This table is called "SOR". The "SOR" table has a primary key which is a set number per task listed. The work order records primary key is the actual work order number.

       

      I just need to know the best way to get multiple tasks into the work order. I originally did it by making drop down lists, but I had to create a duplicate SOR table for each choice and reference that separate one for each task i needed to select, as you imagine that can become very unwieldy but it worked.

       

      I have tried a portal to select from the list of 846 separate tasks, it also is unweildy.

       

      Please can you advise?

       

      Mike

        • 1. Re: Best way to assign tasks to a Work Order record
          coherentkris

          if tasks are static and stored in their own table and works orders can have many tasks you have a classic many to many relationship between work orders and tasks. Many to many relationships are generally solved with a join table or associative entity

          • 2. Re: Best way to assign tasks to a Work Order record
            mikerevans

            So basically, this is how it is set up at the moment.

             

            WorkOrderDetails :- Everything related to work order details, Create Date, Work Order number, status, etc

            SOR :- List of tasks, task description, taskID, cost etc

            LocationDetails :- Asset number, address, postcode, area etc

             

            Would WorkOrderDetails function as the join table, or should I create another table to combine the aspects, and use this as the backbone?

             

            Mike

            • 3. Re: Best way to assign tasks to a Work Order record
              philmodjunk

              Would WorkOrderDetails function as the join table, or should I create another table to combine the aspects, and use this as the backbone?

              Yes, if each record in WorkOrderDetails can represent one and only one task. If a "detail" record may need to refer to multiple tasks, then no, you need to add a join between details and tasks.

               

              Assuming yes, it sounds like you would do this:

               

              WorkOrders----<WorkOrderDetails>------Tasks

               

              where ----< stands for "one to many".

               

              The foreign key field in details can be formatted with a value list of tasks as one way to select a task for a given detail record.

              • 4. Re: Best way to assign tasks to a Work Order record
                mikerevans

                Thanks Phil,

                 

                I have revised my model thus far :-

                 

                3 entities

                 

                Location Details ------- Instruction >----------- Tasks

                 

                I have the relationships as Location and Instruction as a one-to-one as each instruction will only have one location, and Instruction and Tasks as a one-to-many, as each instruction will have many tasks.

                 

                My problem in selecting tasks is that there are over 800 different ones. I have narrowed them down into categories. The onsite engineers will have to be able to select via category, then via task.

                 

                I have changed my way of thinking, after I looked through the sample start solutions provided with FileMaker Pro, I was in the mindset that there could only be one record per work order in the Instruction entity, and that would have to contain all of the tasks for that work order. Now i see that i can have many records as long as it has the same PrimaryKey. Please correct me if i'm wrong lol

                 

                Thanks,

                Mike

                • 5. Re: Best way to assign tasks to a Work Order record
                  mikerevans

                  I think it's finally clicked! Everything does as I want now.

                   

                  Thanks for your help.

                   

                  Mike