1 Reply Latest reply on Feb 3, 2011 8:10 AM by philmodjunk

    Help and question about using Sum function



      Help and question about using Sum function


      FMP 10 on Mac and PC

      I have a Table that we use to keep track of hours worked and personal hours.  This one table tracks six people.  A new record is made every week for each person.  So six new records are made each week.  How can I keep a running total of the personal hours for each person, but limit what it totals to just this year, 2011?  I really want to make sure it only totals each person's personal hours and not just sum all six people's personal hours.  Does this make sense?  Is this possible?

        • 1. Re: Help and question about using Sum function

          You can use either a summary field or a calculation with the sum function for this.

          In a summary report, you could perform a find for all 2011 records and sort by person to get your totals.

          From a portal, you can include the year in the portal's relationship. Then the same summary field will report the total for the portal. A sum function defined in the layout's table can report the total of the portal also. Unless there is some FileMaker 11 portal filtering going on which isn't the case here, I prefer sum to compute a portal total as it refreshes more smoothly should you edit a portal record.

          Here's one sample relationship that could work for your portal:

          MainTable::gYear = PortalTable::Year AND
          MainTable::PersonID = POrtalTable::PersonID

          gYear can be a global field where you enter the year you want for the portal. PortalTable::Year can be a calculation field that returns Year (dateField) to produce a number to match with gYear.