You can summarize calculation fields allright, but the problem is that you only need one result per group.
You could do that by making the calc fields that use GetSummary conditional on position; see attached PDF and the calculation on the layout.
I didn't quite understand your diagram. Can you provide a clearer picture?
Even though you were responding to yourself, I assume you meant my post.
I didn't quite understand your diagram. Can you provide a clearer picture?
You mean: in a higher resolution? I found it quite legible …
Anyway, you need to add a calculation field that calculates a value for one record only per sorted group.
If you had a found set of line items sorted by the break field theDate (e.g. all time card entries for a member), you would define a calc field as
theDate ≠ GetNthRecord ( theDate ; Get ( RecordNumber ) + 1 ) ;
Max ( GetSummary ( sQty ; thedate ) - 8 ; 0 ) * 100 )
In English: if the date of a given record is not equal to the date of the next record, (then) calculate the count of hours for that date by using GetSummary(), subtract the (arbitrarily chosen) limit of 8, take the maximum value of the remainder and zero, and multiply that by 100.
In a found set sorted by day there will be only be a single record within every group where the date is not equal to the following one, so this calculation will only be performed once for a group.
Summarize this field, and you have the sum of all overtime hours costs in the found set. (Or calculate the overtime hours using the same method, summarize that field, and multiply the result by your overtime rate; or … you get the picture now, I take it.)
Thanks for the GetSummaryDEMO file but do not currently have FileMaker 12 installed so wasn't able to open it. Would you happen to have it in FileMaker 11 or earlier format. I'm still trying to summarize summary fields in subtotal parts down in the Trailing Grand Summary part.
I'm still trying to summarize summary fields in subtotal parts down in the Trailing Grand Summary part.
Instead of trying, why don't you implement the solution outlined in the above explanation (which you asked for)?
OK, here is an fp7 version of the demo file, but DO take note of erolst's posts. My demo shows you that a summary field gives a different result depending on its context, and can give the same result as a GetSummmary calc; this might help you grasp the basics, but erolst's logic will take you further though.
GetSummaryDEMO.fp7.zip 7.3 K
Thanks very much for your help but I think my problem with Summarizing Summary fields in my Trailing Grand Summary are complicated by the fact they are referring to Calc fields in the above Summary parts the are using the GetSummary function. I'd like to forward a couple of PDF's that could illustrate better than I'm explaining.
Try using a simple Summary field rather than calcs. Summary fields return the correct subsummaries and grand summaries in the same field based on the context of the layout part. You don't need the calc-subsummary fields at all for a subsummary report, only if you need the calc result in a non-subsum location.
I updated my comments in the FM Dev Forum regarding my problem with Summary
fields in the Trailing Grand Summary. I also uploaded 3 PDF's that may
illustrate my problem better than words.
Thanks for all your help.
I would love to have used only Summary fields in the Summary parts but some
Summary parts are requiring a Calc field using GetSummary values to
determine Reg Time hrs, O.T. hrs, Reg Time pay rates, O.T. pay rates and
various other values utilizing the values in the Summary fields already in
the same Summary parts.
Many of these fields require the results in the Summary fields. The only
way is to use the GetSummary function. Maybe there's a better way.
How about a calc which simply gathers those values for the specific record, and then a simple summary field of that calc?
If that is still duplicating some values, consider scripting the collection/summation of the data the section levels into global variables, which can be added in a script and placed on the report's grand summary in another global variable.
I also uploaded 3 PDF's that may illustrate my problem better than words.
Indeed; your fields very clearly indicate that you have a structural problem (field names ending on 1,2, 3 etc are always a dead giveaway!). This is why you weren't able to translate my suggested approach into your system.
Plan/Project --< Work (fk_projectID, fk_staffID, fk_categoryID, date, hrs; sHrs, cOvertimeForStaff, cOvertimeForWorkType (?), sOvertimeForStaff, sOvertimeForWorkType (?)) >-- Staff
A selfjoin-relationship for Work where
Work::fk_staffID = Work_self::fk_staffID AND Work:date = Work_self::date
lets you define cOvertimeForStaff = Max ( Sum ( Work_self::hrs ) - 8 ; 0 ) * yourOvertimeRate
By the same token, a selfjoin-relationship for workType where
Work::fk_categoryID = Work_self::fk_categoryID AND Work:date = Work_self::date
lets you do the same for each worktype – in one field!
What you will/want to see now simply depends on your find requests and the placement of your summary fields.
I really appreciate your help in trying to understand my Summary problem. This might help clarify my problem.
An Installation Work Order ( 1 record ) has 3 potential installers ( 1 set of fields for each ). I need to evaluate each installer's Reg Time & OT on a Daily Basis (anything over 8 hrs in 1 day becomes OT).
A typical day may have Installer 1, Installer 2 and/or Installer 3, working on 1 or more projects (in a single day). That's why I need to evaluate Reg Time & OT for each installer for each day worked.
Even if it's only 1 installer, doing 3 separate installations in 1 day, I need to summarize the hours worked, by Appt. Date in the Summary Part. There are a few calculations needing the GetSummary function to calculate the Total Hrs for one day. It's these Calc fields using the GetSummary funtion that I can't summaize in the Trailing Grand Summary. Here's the fields involved:
c_Task Duration 1 = (Task Stop 1 - Task Start 1)/3600
s_Duration 1 Summary = Total of: Task Duration 1
c_Reg Time 1 = If (GetSummary ( Duration 1 Summary ; Appt. Date) ≥ 0 ; Min ( GetSummary ( Duration 1 Summary ; Appt. Date) ; 8) ;0)
c_Installer 1 Lunch = If (GetSummary ( Duration 1 Summary ; Appt. Date ) ≥ 6 ; -.5 ; "" )
c_Installer 1 Wrk Hrs = GetSummary ( Duration 1 Summary ; Appt. Date ) + Installer 1 Wrk Lunch
??s_Installer 1 Wrk Hrs Summary = Total of: Installer 1 Wrk Hrs
c_Overtime 1 =If ( Installer 1 Wrk Hrs ≥ 8 ; Max (Installer 1 Wrk Hrs) - 8 ; "" )
c_Installer 1 RT Rate = Round (( GetSummary ( Installer 1 PcWrk Wage Summary ; Appt. Date ) + ( GetSummary ( CS 1 Wage Summary ; Appt. Date )))/ Installer 1 Wrk Hrs ; 2 )
c_Installer 1 RT Wage = Round ( Reg Time 1 * Installer 1 RT Rate ; 2 )
??s_Installer 1 RT Wage Summary = Total of: Installer 1 RT Wage
c_Installer 1 OT Rate = If ( Overtime 1 > 0 ; Round ((GetSummary ( Installer 1 PcWrk Wage Summary ; Appt. Date ) + ( GetSummary ( CS 1 Wage Summary ; Appt. Date )))/ Installer 1 Wrk Hrs * 1.5 ; 2 ) ; "" )
c_Installer 1 OT Wage = If ( Overtime 1 > 0 ; Round ( Overtime 1 * Installer 1 OT Rate ; 2 ) ; "" )
??s_Installer 1 OT Wage Summary = Total of: Installer 1 OT Wage
n_Installer 1 Vac Hrs = a Number field
c_Installer 1 Vac Wage = If ( Installer 1 Vac Hrs > 0 ; Round(Installer 1 Vac Hrs * Min Wage 1 Rate;2) ; "" )
s_Installer 1 Vac Wage Summary = Total of: Installer 1 Vac Wage
c_Installer 1 Gross Wage = Installer 1 RT Wage + Installer 1 OT Wage + GetSummary ( Installer 1 Vac Wage Summary ; Appt. Date )
??s_Installer 1 Gross Wage Summary = Total of: Installer 1 Gross Wage
The Summary Fields beginning with "??c_" are the ones I can't summarize correctly due to reliance on GetSummary calcs in the SubSummary parts.
Any ideas or work around to summarize these summary fields dependant on GetSummary Calcs above?