3 Replies Latest reply on Apr 16, 2012 6:15 PM by phanrahan

    Calculating the Sum of a field with a date range selection



      I am new to file maker and would like to figure out how I can get the sum of a field from a selection of records based on a start and end date. I want to create a time sheet for an employe from the hours that was input on an invoice within a specific pay period April 1,2012 to April 14, 2012 for example. The employee can have REG Hours, TRAVEL Hours or OT Hours on a specifc invoice. I would like to get the total for each of the three categories on my time sheet, REG HRS = 80, TRAV HRS = 10, OT HRS= 20. I have set up a portal to view and filter the invoices that are related to that employee for a date range that I select. I set up three portals for each of the catehgories REG, TRAV an OT. I also set up 3 other table occurances to provide only records that were related to the REG, TRAV and OT categories. I can get a total of each of these three categories over the entire record range but when I go to filter it with a date range calculation it doesnt work. Any suggestions or an easier way of doing this would be appreciated. My calculation for the REG time date selection is as follows:



      If (timesheet_employees_reg_employeelineitems_INVOICES::Date ≥ PayPeriodEnd

      and timesheet_employees_reg_employeelineitems_INVOICES::Date ≤ PayPeriodStart;

      Sum ( timesheet_employees_REG_EMPLOYEELINEITEMS::Qty ) ; 0 )



      Thanks for your help


        • 1. Re: Calculating the Sum of a field with a date range selection
          Stephen Huston

          Portal Filtering only limits what is visible in the portal -- not the underlying  related records. Thus the calculation will return exacly the same results whether or not the portal is filtered.


          You need to set up actual relationships which use the data range as part of the relationship match criteria. You this will mean that if the global date fields are not entered, that set of relationships probabaly won't return anything.


          For that reason, your calculation will need to be revised as a Case statement to pull the current relationship Sum if one or both dates for filtering are blank, but to use the sum across the date-limited relationships if both dates are filled in.


          You cannot rely on portal filtering alone to control a sum via the relationship.

          • 2. Re: Calculating the Sum of a field with a date range selection

            Thanks Stephen I will give thata try.

            • 3. Re: Calculating the Sum of a field with a date range selection

              Another question. I cant seem to filter the relationship by using a global date range. I am using a start date and an end date and comparing it to a creation date of the Employee line item. No matter how I set this up it will not show any records in the portal when the date filter is in place in the relationship. Once I remove the date filters the REG Time relationship filter works fine.  If any one could provide some advice i would appreciate it. I learned about this in the Filemaker trainin module but cant seem to get it to work in this application.  See the screen shots below of my setup.


              Edit Relationship.jpg. Timesheet.jpg