AnsweredAssumed Answered

Calculating the Sum of a field with a date range selection

Question asked by phanrahan on Apr 12, 2012
Latest reply on Apr 16, 2012 by phanrahan


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