1 2 Previous Next 27 Replies Latest reply on Aug 28, 2012 11:39 PM by iceknight

    Sumif based on 2 fields

    pdegior

      Title

      Sumif based on 2 fields

      Post

      I have two tables that are linked by ProjNum field.  The second table is a time log that users can enter hours and select a department from a drop down.  The intent is to track hours on a project.  I have a field in my main Project table called ActLabHrs.  I want to populate that field based on the sum of all the hours worked for a specific project for Labor only.  I will have a field in my main project table for each discipline ie: ActLabHrs, ActEngHrs etc.

      I am having trouble figuring out how to sum the records in the TimeLog table based on Department for a project.  My project table is also linked to many other tables to track cost for materials etc. I may have to write script to index through all the TimeLog records and test for the department, but that seems way too complicated.  I'm sure there is an easy way to do this, but I am missing it. 

      I guess I'm looking for some sort of "sumif" function that will sum each record in the timelog if Department = "Labor" for example.  Of course because of the relationship with ProjNum, the sum function adds all hours for the project regardless of the Department.

      Any help would be greatly appreciated.

        • 1. Re: Sumif based on 2 fields
          philmodjunk

          It appears that you have this relationship:

          Projects::projNum =  TimeLog::projNum

          If you have filemaker 11 or 12, you can use a summary field in TimeLog and filtered portals to display department specific totals for the hours worked on a given project, but if you then need to use these values in calculations, you'll need to use a relationship based method of computing these totals as the filtered portals are pretty much "display only". (If you have FileMaker 10 or older, you'll need to use a relationship based method anyway.)

          The filtered portal method:

          Define a summary filed, sTotalHours as the total of your Hours field in TimeLog

          To see the total Labor hours for your project, put a one row portal to TimeLog on your Projects Layout. Define this portal filter expression:

          TimeLog::Department = "Labor"

          Put sTotalHours in this single portal row.

          Now duplicate this portal but change the portal filter expression to filter on different departments or it's possible to set up a field with a value list of departments and then a single portal can be used, but you first select the desired department from the value list. (This takes a bit of extra fiddling to get the portal to update with each selected department, but saves screen space for other uses.)

          The filtered relationship method: (Good for calculations and versions older than Filemaker 11)

          Define a calculation field, constLabor as a calculation field. Put "Labor", the department name in quotes as the sole term of it's calculation and select "text" as the calculation field's return type.

          In Manage | Database | relationships, make a new table occurrence of TimeLog by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as LaborTimeLog.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add it to your relationships like this:

          Projects::projNum = LaborTimeLog::projNum AND
          Projects::constLabor = LaborTimeLog::department

          You can use LaborTimeLog::sTotalHours to get the total labor hours or you can use Sum ( LaborTimeLog::Hours ) to compute the total Labor hours for this project.

          You'd repeat this process for each deparment, or, much like the filtered portals, you can use a field with a value list of departments in place of the const... fields and use just one relationship, but can get each total by selecting a department in this field's value list.

          • 2. Re: Sumif based on 2 fields
            philmodjunk

            PS. there's also a way to set up a summary report on a layout based on the TimeLog table that shows a breakdown of total hours for each department for either just one project or multiple projects. This approach is much simpler to set up but isn't a method you can use with a layout that is set up as a "dashboard" or some such--it works from a layout dedicated to just showing the summary report.

            • 3. Re: Sumif based on 2 fields
              pdegior

              Thanks...the filtered relationship seems to do what I want.  The only issue I am having is more of a refresh thing.  When I make a change to the time log (which is in a portal), my Sum() field in my main Project table does not update unless I click inside that field on screen.  I have other sum fields in this table that seem to update without issue.  The only difference with this one is that I'm referencing a self-join table. Could this have something to do with it?

              • 4. Re: Sumif based on 2 fields
                philmodjunk

                I'd guess that the record was not yet committed, otherwise, it should update like the others. What happens if you click a blank area of the layout after edting in the portal?

                (How did you come up with a self join here? What I posted sets this up without self joins...)

                • 5. Re: Sumif based on 2 fields
                  pdegior

                  The field does not update if I click outside the object on a blank area.  It will only update if I click inside the field or if I change records and come back to that record in my Projects table.  The other thing I failed to mention is that all of this is happenning inside a tab  control.  Not sure if that would make a difference.

                  I guess I must be confused as to what a self-join is then.  I thought by definition a self-join is a relationship where both match fields are in the same table???

                  UPDATE:

                  I went back and changed the sum in my Project field to look at total hours in my TimeLog table.  Now obviously it gives me total hours regardless of department, but the field updates instantly.  It seems to have something to do with me trying to calculate hours from the filtered relationship set. 

                  • 6. Re: Sumif based on 2 fields
                    philmodjunk

                    THe portal filter is the issue.

                    A self join is a relationship between two occurrences of the same table--which would be the case if both match fields are defined in the same table, but we don't have that here as we have a link between two different tables: Projects and LaborTimeLog.

                    The filtered portal would be a factor here. You indicated that you were using the filtered Relationship method so I did not consider that possibility. The filtered relationship does not use a portal filter and your sum would be returning the correct values if you were using that method.

                    Also, make sure that your sum function is defined in a calculation field--not a number field with an auto-entered calculation.

                    • 7. Re: Sumif based on 2 fields
                      pdegior

                      But, if I use the filtered portal method, How do I get the resulting data into a field in my projects table?  I need to used that result in calculations.

                      My sum field in the Projects database is a calculation field and is set to unstored.

                      • 8. Re: Sumif based on 2 fields
                        philmodjunk

                        Apologies. I sumhow read your last post as referring to a filtered portal instead of the relationship and was trying to figure out which method you used. Ignore that post please.

                        The sum function calculations should update automatically. The only thing I can think of that you haven't checked is what return type is specified. Please make sure that number is selected as the calculation field's return type.

                         

                        • 9. Re: Sumif based on 2 fields
                          pdegior

                          No problem.  I checked and in the calculation the type is set to number.  It is strange because the calculation will update also if I change layouts and come back to the orignal one.

                          I'm sure I've followed your instructions to a the letter.  I created a duplicate of the timelog table and set up the relationships as you mentioned in your original post.  I then created the constant field in the Projects table with a calculation field with "labor" in it.  I just can't seem to get the formula to recalculate.

                          I'm not sure why adding this duplicate table causes issues.  I even duplicated the timelog table, and only added the

                          Project::ProjNum = TimeLog2::ProjNum relationship to try to get it to behave the same as my original

                          Project::ProjNum = TimeLog::ProjNum relationship.

                          My formula is simply :

                          sum(timelog2::hours) (does not refresh immediately)

                          but,

                          sum(timelog::hours) (the original table) updates immediately. 

                           

                          It seems as though filemaker doesn't like duplicate table.

                          • 10. Re: Sumif based on 2 fields
                            philmodjunk

                            What version of FileMaker are you using?

                            • 11. Re: Sumif based on 2 fields
                              pdegior

                              version 12 (12.0v1).

                              • 12. Re: Sumif based on 2 fields
                                pdegior

                                I have discovered a work around which I really hate to do unless I can figure out a better way of doing this.

                                Under the TimeLog portal on my layout, I created a script trigger for "OnObjectExit".  Below is the script

                                RefreshWindow[Flush cached join results].

                                I have no idea why I should have to do this, but it seems to work. Any thoughts on this?

                                • 13. Re: Sumif based on 2 fields
                                  philmodjunk

                                  I created a demo file and noted the following behavior:

                                  If I added a new record in the portal and entered data. Clicking the background (which commits the record) updated my total. If I changed the value in an existing record, it did not.

                                  I then played around with different combinations of commit record and refresh window until I determined that this script would consistently update my totals:

                                  Commit Record
                                  Refresh Window

                                  I suggest using the OnObjectSave trigger to perform this script any time you edit a field in the portal that will change one or more of these totals.

                                  • 14. Re: Sumif based on 2 fields
                                    pdegior

                                    Thanks for all the help!

                                    1 2 Previous Next