AnsweredAssumed Answered

Summary of Sub-Summary Summaries

Question asked by jregalia on Sep 2, 2009
Latest reply on Sep 2, 2009 by etripoli


Summary of Sub-Summary Summaries


First, I'd like to introduce myself.  My name is Jeff and I'm a programmer/analyst at a community college.  I'm an experienced programmer but have very little experience with FileMaker.  I will try to be as thorough as possible without getting into unnecessary information.  My problem:


Human Resources has asked me to create a report of payroll data for the staff.  The main report is a detailed report of each employee, their itemized pay (by paycode), their total pay and at the very end a grand summary of all payroll.  For the purpose of disability insurance, there is a ceiling of $11,994 on any one employee (ie. allowable total = min (total, 11994)).  Calculating this value is easy for each subsumary but since each employee has multiple records (one per pay code) the grand summary is grossly inflated because it is including each total multiple times for each employee.  To make matters worse, some employees even have multiple records of the same pay code because of funding sources.  I can get a total based on records but this does not take into account the per employee ceiling.


My current value fields are:

Amount: Number (input from file)

Total Amount: Summary; =Total of Amount

Total Amount Allowed: Calculation; Unstored, =Min(GetSummary(Total Amount; Employee Sort Key); Max Amount Allowed)

Total Allowed: Summary; =Total of Total Amount Allowed

Max Amount Allowed:  Number; Global, Auto-enter Data (11994)


My report is structured as:

  1. Header
  2. Leading Sub-Summary sorted-by Employee Sort Key (employee info)
    1. Name
    2. Employee ID
  3. Sub-Summary sorted-by Pay Code (pay code list)
    1. Pay Code
    2. Pay Code Description
    3. Total Amount
  4. Trailing Sub-Summary sorted-by Employee Sort Key (employee summary)
    1. Total Amount (Actual total of all pay codes for employee)
    2. Total Amount Allowed (Total after price ceiling adjustment)
  5. Trailing Grand Summary
    1. Total Amount (Total of amount for all records)
    2. Total Allowed (BROKEN; Supposed to be total of all adjusted employee totals but actually turns out as adjusted total of employee total once for every record)
  6. I have very basic knowledge of FileMaker but I'm a fast learner.  If anyone has any tips to point me in the right direction, I'd greatly appreciate it.  I am using FileMaker Pro 9 so anything available in 9 I have at my disposal.  Let me know if you need more information and I'll gladly provide whatever is needed.  Thanks for looking.