6 Replies Latest reply on Jul 26, 2009 1:32 PM by TPlemons

    Can't figure out how to make a certain relationship work

    TPlemons

      Title

      Can't figure out how to make a certain relationship work

      Post

      I'm new to FileMaker Pro, and trying to create a basic job costing system for my business. I've made most everything work, but this piece is alluding me.

       

      I want to cost the time spent by different employees doing each task on a given job at their specific hourly rates. To do this, I created an Employee table listing their names, positions, hourly rates. Then, in my job costing table I have it set so I can select the employee who did each task through a pop-up... there are six distinct tasks in this process. The cost for that task is a calculation of the hours spent times the employee's rate. In order to create these different task relationships in the relationships graph, I had to duplicate the job costing table for each new task relationship... it would only let me relate the job cost table to the employee table once.

       

      The result I am getting is that the employee listed for the first task (which is related from the original job cost table to the employee table) properly calculates a cost for the task. If that employee does any other task, the cost is calculated. But if I choose another employee, the cost does not calculate for that employee. My guess is that I've messed up the table relationships somehow.

       

      Can anyone offer help?

        • 1. Re: Can't figure out how to make a certain relationship work
          comment_1
            

          I don't quite follow this part:


          TPlemons wrote:

          In order to create these different task relationships in the relationships graph, I had to duplicate the job costing table for each new task relationship... it would only let me relate the job cost table to the employee table once.


          If I understand correctly, you should have ONE table for tasks (with 6 records?). The job costing table should be related to both - so for each record you would choose both the employee and the type of task performed.

           


          • 2. Re: Can't figure out how to make a certain relationship work
            TPlemons
               I hadn't considered having a table for tasks... I currently have all the tasks in the job costing table. To clarify, each record (job) in the job costing table will require all 6 tasks. Each task may be completed by any of my employees, but each employee costs the company a slightly different rate. So, if I make a table for tasks with 6 records, I would relate job costing table to task table and to employee table as you suggest. Then, I would select the employee who did each task on a given job in job costing table. That would then allow me to correctly calculate the actual labor cost for each task in the job. Is this how you see it working?
            • 3. Re: Can't figure out how to make a certain relationship work
              comment_1
                

              I still don't understand this:

               


              TPlemons wrote:
              To clarify, each record (job) in the job costing table will require all 6 tasks. 

              A record in the job costing table is not the job - it's a component of a job, and it should relate to a specific employee doing a specific task (for a specific job). There should be another table listing the Jobs.

               

              Normally, the Tasks table would be also related to Jobs (one job has many tasks). It seems you are saying every one of your jobs has a permanent set of 6 tasks?


              • 4. Re: Can't figure out how to make a certain relationship work
                TPlemons
                  

                Of course, being new, I may not have the theory correct. But I built the job costing table as one record = one job. I am painting and installing doors, so each job will have multiple tasks:

                - check the incoming door for correct measurements

                - prep the door

                - paint the door

                - load the door(s) and pull the hardware out of inventor for delivery

                - deliver the door

                - install the door

                 

                ALL jobs will have all tasks.  Currently it works if all tasks are performed by the same person, but not if there are multiple employees involved. I believe that's because the job costing table is relating to one record in the employee table, and if another employee is chosen for one or more tasks, it returns a null response, and hence a zero in the cost for that task.

                 

                How do I change the design to get the desired information?

                 

                Thanks for your input.

                 

                • 5. Re: Can't figure out how to make a certain relationship work
                  comment_1
                    

                  I believe you should have 4 tables: Jobs, Tasks, JobTasks and Employees.

                   

                   

                  The Jobs table contains JobID and fields that describe the job (customer, date, etc) but no fields that describe the tasks or employees.

                  The Tasks table holds 6 permanent records for the 6 tasks you have described.

                  The JobTasks table has fields for:
                  • JobID
                  • TaskID
                  • EmployeeID
                  • Hours
                  • EmployeeRate (lookup from Employees)
                  • cCost = Hours * EmployeeRate


                  The relationships required:

                  1. Jobs::JobID = JobTasks::JobID

                  2. Tasks::TaskID = JobTasks::TaskID

                  3. Employees::EmployeeID = JobTasks::EmployeeID


                  For each new job, import the 6 records from Tasks into JobTasks (mapping TaskID to TaskID) and set the JobID value of the imported records to the JobID of the newly created job record (this should be all scripted together under Create New Job).*

                  Place a 6-rows portal to JobTasks on a layout of Jobs. In this portal, you will select the employee for each one of the 6 tasks.



                  ---
                  (*) There is another method that can create the 6 tasks records automatically as needed - but it's somewhat complex and I wouldn't be able to describe it in a forum post.














                  • 6. Re: Can't figure out how to make a certain relationship work
                    TPlemons
                       I'll digest this and give it a try. Will let you know if I have other questions... thanks.