Calculations & Summarizing Data

[Raschelle]

Hi,

I'm trying to figure out the correct calculation/summary setup for a report I'm working on. I have three related database files (separate files, since they've been in service since FM3): Work Orders, New Projects, and Timesheets. Work Orders is a db of tasks; New Projects is a db of projects, which has a portal for the Work Orders (tasks for each project); and Timesheets is where employees enter in the time spent on their projects. I'm currently using FM 9.

In the New Projects db there is a calculation field "Total Hours" which is the total time for all tasks (Work Order records) in that project. The calculation is: Sum(${Work Orders (2001 up)*}::Hours)

In the Timesheets db there is a summary report that I'm trying to add more information to. Currently it shows the project number, the allotted hours (which is the total hours in the New Projects db) and applied hours (total time spent on the project so far). I would like to set up a calculation so that it will subtract the applied hours from the allotted hours to show how much the project is over/under hours.

The calculation used for the applied hours (GrTotalBillableHours) is a summary field of Sub Total hours calculation field, which uses the following calculation: Project Time + (Driving Time / 2).

I tried making an OverUnder calculation field with this calculation: NewProjects::Total Hours - GrTotalBillableHours. But this only works if I look up one project at a time. If I have multiple projects on the report, it takes the applied time of all the projects and subtracts from the allotted hours of each project.

How can I get it to correctly work with each project listed and not have to run the report several times?

Also, I would like to total, at the bottom of the report, the total allotted hours and total applied hours. I can use that same GrTotalBillableHours in a Trailing Grand Summary part and it adds up correctly. However, when I try to make a calculation or summary field for the New Projects::Total Hours, it just gives me the total hours for whichever project happens to have a field selected (and apparently I can't make a summary part for external data sources?).

Sorry if this is a lot of information and requests in one post.

Any help is appreciated, as I am a self-taught "developer" (if you could call me that) for my company. Thank you!