2 Replies Latest reply on Jul 6, 2009 10:37 AM by philmodjunk

    Subsummary Totals Calculated in Related Table

    gidgetgirl81

      Title

      Subsummary Totals Calculated in Related Table

      Post

      Hello,

       

      I have a database that keeps records of time on company projects.  First of all, each task is an entry in a table called "Tasks", where a task type is selected from a defined value list, and the hours for that particular task is manually entered.  

       

      Then, there is a form that will summarize the total time for each project (TotalHours) by the subsummary field (TaskType).  So it could tell me that on Job XYZ, there was a subsummary of 20 hours of TaskType1, and a subsummary of 36 hours of TaskType2, with a grand summary of 56 hours for the entire job.

       

      All of this is working just fine, but I am having trouble calculating these subsummary values in the browse mode.  For the related table "Invoices", I need to break down the subsummary values.  Right now I manually enter that the TaskType1 for Job XYZ was 20, and TaskType2 for Job XYZ was 36... so that this can enter into my Invoice Layout.

       

      Is there a way to automatically calculate those subsummary values in the related table Invoices, in browse mode?  

       

      I came up with the calculation:

      TaskType1Total =  If ( Tasks::TaskType="TaskType1" ; GetSummary ( Tasks::TotalHours ; Tasks::Task Type ) ; "" )

       

      but it's not working!!  Can anyone help?  Thanks!

        • 1. Re: Subsummary Totals Calculated in Related Table
          gidgetgirl81
             I forgot to mention that it is imperative to break down the subsummary totals by TaskType for invoicing purposes, because some tasks cost more than others.  So it doesn't help me to just invoice based on the grand summary.
          • 2. Re: Subsummary Totals Calculated in Related Table
            philmodjunk
              

            I'm guessing you aren't using FMP 10. One of the nice new features of this release is that sub-summary parts are now visible in browse mode.

             

            I don't see how your if statement is going to accomplish anything.

             

            If you use just:

             

            GetSummary ( Tasks::TotalHours ; Tasks::Task Type )

             

            And sort the Tasks table by Task Type, do you get the values you need?

             

            The alternative is to set up one or more relationships that allow you to use something like Sum(TaskType1Tasks::Hours) to compute your subtotals.

             

            Much depends on exactly what you want to see on the screen and how you need to work with your data.