1 Reply Latest reply on Apr 16, 2013 7:05 AM by philmodjunk

    Creating 'manual' subsummary calculations



      Creating 'manual' subsummary calculations



           I have a 'timesheet' table where users log their task, and how many hours they have been working on it. i.e. design, research, drawings etc. and this is linked to a job number.

           I want to show total hours spent doing drawings (for example) on a particular job.

           Im thinking Sum(hours if(Code = Drawings))               (Code is the field containing task value list)

           I can do this on a list view using subsummary when sorted by....but want to know how to do this by calculation field.



        • 1. Re: Creating 'manual' subsummary calculations

               Your sum expression won't work. Sum cannot sum selectively. Sum ( RelatedTable::Hours ) will total all related records regardless of the value in Code.

               Why do you need it by calculation field? (Knowing that may enable someone to suggest an alternative approach.)

               In order for sum to compute a total of just those employee hours where Code = "Drawings", 'Code = "Drawings" ' has to become part of the relationship. You can do this with multiple calculation fields that return a single code value and multiple relationships/multiple table occurrences, but there are other ways to selectively sum your data.

               You haven't described the relationship you intended to use to compute the total hours so I can't start with that relationship and show you how to match only to "Drawing" hours.

               It's also possible to do this with multiple calculation fields, one for each code:

               If ( Code = "Drawings" ; Hours )

               Sum that calculation field with a summary field or with sum from a related table and you'll get a selective sum.

               And if you are using FileMaker 12, there are ways to get these totals selectively by Code using ExecuteSQL.