12 Replies Latest reply on Dec 8, 2009 12:05 PM by CraigFiore

    Summary fields

    CraigFiore

      Title

      Summary fields

      Post

      Hi, 

      Im really stumped. Ive been working on this issue for two days and no luck.

      I've created a LaborLog (keeps track of how long and what job employees are working on) and it works great. From that table, I created a report that summarizes how may total hours each employee has worked at a job. So, I sort by JobID, than EmployeeID, and gives the total hours for each employee.

      Example:

      JobID 100

      John 100
      Tom 126

       

      This is were i ran in to a problem. I created a portal on my WorkOrder table. The portal is suppose to give the owners a quick glance at who worked at each job and for how long. I was able to get each employees name to only appear once in the portal but when i try to create a summary field for the hours, it gives me the total hours for the Job. It does not break it down by employee.

       

      Any help is appreciated. 

        • 1. Re: Summary fields
          philmodjunk
            

          "I was able to get each employees name to only appear once in the portal "

          How exactly did you do that? We need to know what method you used so as to suggest a way to compute the proper sub-total.

           

          You might be able to use the Sum function in a calculation field, you might need to use the GetSummary function, but I can't till if either will work for you without knowing more about how you structured your tables.

          • 2. Re: Summary fields
            CraigFiore
              

            I tried the SUM and GetSummary but with no luck. However, I may be doing something wrong.

            With some help from the people on this forum the method i used it the following:

            The three tables that are involve are: LaborLog, WorkOrder,  and EmployeeInfo

            LaborLog contains the fields:Date, EmployeeID, JobID, Hours, sEmployeeTotalHours(summary of Hours)

            EmployeeInfo contains:EmployeeID, EmployeeID, Rate

            WorkOrder contains: JobID

             

            There are relationships between: LaborLog:EmployeeID=EmployeeInfo:EmployeeID and WorkOrder:JobID=LaborLog:JobID

             

            I created a portal on the EmployeeInfo table so i can enter hours from there by the Employees name. 

             

            The Portal on the WorkOrder page shows records from the EmployeeInfo table. This way it only show each employee one time. When i created the portal from the LaborLog, it showed the employees name for every entry made.

             

            I hope this can help. 

             

            • 3. Re: Summary fields
              philmodjunk
                

              You appear to also have a relationship linking WorkOrder to Employee Info probably with a cross product operator (x) in the relationship.

               

              As you've discovered, using a portal to EmployeeInfo on a layout based on WorkOrder won't work as you've set it up. There's no value in the portal records that Filemaker can use to determine which records in LaborLog are part of the current Work Order.

               

              I just played around with and idea for using a global field in EmployeeInfo for this and got it to work:

               

              From Your last post, you appear to have this relationship or something similar:

               

              WorkOrder::WorkOrderID X AllEmployees::EmployeeID (AllEmployees is a table occurrence with EmployeeInfo as its data source table)

               

              In Employee Info, define the following global number field:

              gWorkOrderID

               

              Create a New relationship:

              AllEmployees::gWorkOrderID = LaborLogbyEmp::WorkOrderID AND

              AllEmployees::EmployeeID = LaborLogbyEmp::EmployeeID  (LaborLogbyEmp is a TO with LaborLog as its data source table)

               

              Define a calculation in EmployeeInfo:

               

              cWorkOrderHours   : Sum(LaborLogbyEmp::Hours)

              Select AllEmployees as the Context Table Occurence in the drop down in the top of the Specify Calculation dialog.

               

              Now, assuming your portal is based on AllEmployees, place cWorkOrderHours in your portal.

               

              Write a script to synchronize the records by updating EmployeeInfo::gWorkOrderID

              Set Field [AllEmployees::gWorkOrderID; Work Order::WorkOrderId ]

              Commit record

               

              In Layout setup... use the Record Load script trigger to perform this script so that the global field update as you switch from Work Order to Work Order.

               

              • 4. Re: Summary fields
                CraigFiore
                  

                I dont think i have a relationship between WorkOrder and Employee. See below, but i am going to try your suggestion now.

                 

                • 5. Re: Summary fields
                  philmodjunk
                    

                  Yeah, you may not need it, now that I think about it. If you only want those employees that actually worked on the work order (as apposed to all Employees in the table), Link LaborLogbyEmp to EmployeeInfo instead of the additional TO, AllEmployees, that I described.

                   

                  In otherwords:

                  EmployeeInfo::gWorkOrderId = LaborLogbyEmp::WorkOrderID AND

                  EmployeeInfo::EmployeeID = LaborLogbyEmp::EmployeeID

                  • 6. Re: Summary fields
                    CraigFiore
                       Should i delete my other relationship that pertain to this before i do what you suggest?
                    • 7. Re: Summary fields
                      philmodjunk
                         You have several relationships. Which one do you want to delete?
                      • 8. Re: Summary fields
                        CraigFiore
                          

                        Forgive me for all the questions, I'm still pretty new to this.

                         

                        These are my current relationships: 

                        LaborLog:EmployeeID=EmployeeInfo:EmployeeID

                        WorkOrder:JobID=LaborLog:JobID

                         

                        Do they stay or do I delete one or both of them.

                         

                        Also, you posted the field WorkOrderID, is this the same field as JobID or a new field? 

                         

                        From you previous post, this is what i am suppose to do.

                         

                        1. Create a table LaborLogbyEmp (base on the LaborLog as its data source)

                        2. Create a table AllEmployees (base on EmployeeInfo as its data source)

                        3. Create a field in EmployeeInfo called gWorkOrderID which is global. Does this field have anything to do with my JobID field?

                        4. Create relationships:

                        AllEmployees::gWorkOrderID = LaborLogbyEmp::WorkOrderID 

                        EmployeeInfo::gWorkOrderId = LaborLogbyEmp::WorkOrderID AND

                        EmployeeInfo::EmployeeID = LaborLogbyEmp::EmployeeID

                        5. Make a new field in Employee called cWorkOrderHours with the calculation Sum(LaborbyEmp::Hours)

                        6. Change the portal to base on AllEmloyees and place cWorkOrderHours in it.

                        7. Write a script 

                        Set Field [AllEmployees::gWorkOrderID; Work Order::WorkOrderId ]

                        Commit record 

                        8.Create a trigger script in for Item #7 

                         

                         

                        Please let me know if these steps are correct.

                         

                        Thank you 

                        • 9. Re: Summary fields
                          philmodjunk
                            

                          Keep your original relationships. You need them.

                           

                          Yes, my mistake in posting WorkOrderID instead of JobID.

                           

                          I've given you two choices in implmentation with similar but not exactly identical results.

                           

                          Using AllEmployees, you get a list of all employees whether they have worked on the given work order or not.

                          If you just link LaborLogbyEmp to EmployeeInfo like I suggested in a follow up post, the laborLog table acts like a filter and you only see those Employees that have logged time on the current work order. It's up to you to decide which method makes more sense for you.

                           

                          Every where you see "WorkOrderId" Substitute "JobID".

                           

                          Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

                          Table vs. Table Occurrence (Tutorial)

                          • 10. Re: Summary fields
                            CraigFiore
                              

                            I guess i am not a very good student because i could not get it to work. After, trying several options i was able to show only the employee's name who worked on the job, in the portal, (which is what i want) But, when i came to the hours, i had several results, 1. blank, 2, <unrelated> showed up in the field, 3. only hours for the  first day showed up, not a total. 4. And at one point all the hours for every job for that employee showed up.

                             

                            I even played with the GetSummary field.

                             

                            I did have a little problem getting the script done correctly. I had to set up as:

                            Set Field[Employee Info::gJobID;=(AllEmployees::gJobID;Work ORder::JobID)]

                             

                            Im not sure what i did but I could not get it to work, but I do appreciate your time.

                            • 11. Re: Summary fields
                              philmodjunk
                                

                              I can't see the images that you've attempted to post. I just see boxes with x's in them.

                               

                              Set Field[Employee Info::gJobID;=(AllEmployees::gJobID;Work ORder::JobID)]

                               

                              That's definitely incorrect syntax.

                               

                              You need

                              Set Field[Employee Info::gJobID; Work ORder::JobID]

                               

                              If you're unfamiliar with setting up a Set field script step:

                               

                              Many new users get confused the first time they try to add Set Field to a script. See the following link for step by step instructions:

                              Selecting an "All" option with checkboxes

                              • 12. Re: Summary fields
                                CraigFiore
                                  

                                I got it to work, thanks to you!!!!!

                                 

                                Craig