getting an average time spent on a project

[danfoshee]

This seems like a simple thing, but I've been hitting my head against the wall for over two weeks. I'm a FileMaker dummy. I've read some books, but quite honestly don't "get" all but the most simple aspects. Unfortunately, I've inherited this db set, and now the users want to do more with it. Eeeek!!

Background:

We use JobCapture by Gluon to track our artist's time, which exports into a FileMaker db, plus some other db's I've cobbled together. We use FileMaker 5.0 w/ FM Server 5.

I've got two files. One, Jobcapture.fp5, keeps track of time spent on how much artists work on a particular project, the cost, the hourly rate, etc. The other, called jobtrackCPDR.fp5, keeps track of the "other" project info (what dept. to bill to, contact info, whether it's a creative brief or an actual job, etc.) They are joined by a relationship, by Job #, both ways; that is, both have a relationship to each other.

B/c of the way JobCapture tracks the jobs, there are many "sessions" that total up for one particular project; that is, one particular job# might have hundreds of 5-minute sessions.

Problem:

I need to get the average time spent on both jobs and creative briefs for a particular dept. I tried putting an Average summary field in a sub-summary sorted by dept, but that divides the total amount of time spent by the total number of sessions for that dept. I need the total time divided by the total number of projects for that dept. So then I thought, fine, I'll do a calculation doing that (Ubillsumm / projecttotal), but that didn't work.

Note that I'm doing the reports in Jobcapture.fp5, more by necessity and panic than by planning. Whenever I try to pull the time and cost information from Jobcapture to jobtrack.fp5, I don't get the right info. Conversely, whenever I try to pull the job and brief count summaries from jobtrack.fp5 into Jobcapture.fp5, I just get the totals, not the total by dept.

I'm in way over my head, and no clue how to proceed. Any pointers would be great.

[AudioFreak]

I recently set something like this up. What I did was have a calculated field that was the jobnumber combined with the depatment. Created new self relationship based on the jobnumber and department field. Then base your calulation on this new relationship. There may be a better way of this and if there is I'm interested. HTH

[danfoshee]

Hmmmm...guess I'll be looking into self-joins. I'm shaky on the concept as well as its application, but I'll futz around with it.

In the meantime, I finally got it to work, and is my face red! Turns out I had all the calculations right, I just needed to sort it by an additional field.

Guess that's what a lack of knowledge and sleep combined will do. I apologize for wasting anyone's time and bandwidth.