Related Calculation Field in subsummary report

[emncwundy]

I have created a WIP report which lists and sorts jobs in progress based on their scheduled installation month.

 

The report is sorted by month and shows total values for each project.

 

I have also included a related field in the report - the hours allowed for each job (which comes from a related table - costed time in estimates).

 

I cant work out how to subtotal the costed or allowed time for the jobs when the report sorts by month.

 

The value in the total field just seems to be the last value in the list for that month.

 

The calculation at present is:

 

Sum (Project_ESTIMATE::TotalItemCostHours)

 

is it possible to have a calculation field (or a summary field for that matter) displaying totals for the sum of values in a found set from a related table?

 

I hope this makes sense!  Any suggestions would be much appreciated. I am really stuck on this one.

 

A sample of how the report is printing is also attached in PDF format.

 

WIP report showing totals.pdf

[comment]

I am missing a clearer description of your tables and their relationships. I am guessing that you need another summary field in the Projects (or is it Jobs?) table, to total up the calculation field that sums the related items.

[emncwundy]

Each estimate can have one or more items costed. They are related by _pkEstID.

 

Once an estimate is approved a project number is created. They are also related by the _pkEstID.  So the project number then accesses the total hours for Items costed for that estimate which is Project_ESTIMATE::c.TotalItemCostedHours.

 

I tried setting up a summary field in Estimates total the hours for the items costed. I then created a summary field in the Projects database to try to pull this info through via a calculation using the get summary function;

 

GetSummary ( Project_ESTIMATE::s.TotalItemCostedHours ; InstallMonth )

 

but on the report the field is blank, so i am not sure if its doing anything!  I also checked to see that the sort on the report was correct.

[emncwundy]

I just realised what you meant about creating a summary field in projects. Using my calculation field in projects,

 

Sum(Project_ESTIMATE::c.TotalItemCostedHours)

 

I then created a summary field based on this field.  So simple. Can't believe it took me this long to work out what you meant! Anyway, thank you for your help!  It's now working. Very happy camper.  Can't believe how excited i get when i can make some thing in filemaker work.....

[comment]

Can't believe how excited i get when i can make some thing in filemaker work.....

 

LOL, you're not the only one...