1 Reply Latest reply on Jul 23, 2014 2:48 PM by philmodjunk

    Need to create a sum field that will summarize several categories in a related table

    amberm

      Title

      Need to create a sum field that will summarize several categories in a related table

      Post

           I have been reading every post I can find on sum fields but am still having trouble.  I am using Filemaker for employee time entry. I have the time entries dropping into a portal in the Project module (they have to choose the project when they enter the time entry and that is where the relationship lives). From the Project module I want to show the type of work done or the Service Classification (drafter, engineer, clerical, etc.) and the retrospective sum total time. So there would be numerous time entry records for each project, I'd like to have a tab set up to show the Service Classification and it's total time (Drafter-20 hours total, Engineer-40 hours total, etc.).  Preferably I would like this to be a running total, and only have the Service Classification to show up in the portal when there has been a time entry associated. The point of this is so that the Project Manager can quickly compare budgeted project hours with current project hours on each project.  I am running FileMaker Pro 11. Any direction you could provide would be very helpful. I have attached some screen shots in the hopes it clarifies what I want to do.  Thanks!

      Screen_Shots-page0001.jpg

        • 1. Re: Need to create a sum field that will summarize several categories in a related table
          philmodjunk

               There is no such thing as a "sum" field. There are fields of type summary and there are fields of type calculation that use the sum() function to get totals and subtotals in FileMaker. And we can now get the same type of totals using calculation fields that use ExecuteSQL with a sum function as part of the SQL query.

               As best I can determine, you have this relationship (though with your names in place of mine):

               Projects-----<WorkLog

               Projects::__pkProjectID = WorkLog::_fkProjectID

               Summary fields and sum functions aren't going to work here without a bit of careful design work. ExecuteSQL, if you have FileMaker 12 or newer and want to tackle SQL queries, can actually be a much more straight forward approach: FMP 12 Tip: Summary Recaps (Portal Subtotals)

               Using FileMaker relationships, you can  link in a table of ServiceClassification Records and use a global field defined in the same table to filter this down to just the records for the current project:

               Projects>----x----<ServiceClassifications------<WorkLog|ServiceClass

               Projects::anyField X ServiceClassifications::anyField

               ServiceClassifications::ServiceClassification = WorkLog|ServiceClass AND
               ServiceClassifications::gProjectID = WorkLog|ServiceClass::_fkProjectID

               WorkLog|ServiceClass would be a new table occurrence of WorkLog and gProjectID would be a global field of the same data type as _fkProjectID.

               A calculation field defined in ServiceClassifications can be defined as:

               Sum ( WorkLog|ServiceClass::Actual Time )

               Then you can place a portal to ServiceClassifcations on your layout and use a portal filter to omit any records where the above calculation field is zero or empty to show just the subtotals for each service Classification for the current project record.

               Final detail:

               You would set up the OnRecordLoad trigger to perform this script in order to make sure that gProjectID always has the correct value:

               Set Field [ ServiceClassifications::gProjectID ; Projects::__pkProjectID ]