start month, end month, $$ per month

[BarnettFrummer]

I have a problem constructing a database to generate a certain kind of report. I am running FM Server 9. These are the bare bones of the process I'm trying to figure out:

The database uses a record for each job. Jobs start on the first day of one month and end the first day of a later month and each date has its own field. Each job has an estimated value, which gets frequently updated. Job length varies, and in fact, start dates and end dates change all the time. Start dates and end dates range up to 24 months or as brief as 3 months. We have many users logging in to update all these fields every day.

For estimating purposes, we simply divide the TotalAmount by the number of months the job runs. Thus a $900 three-month job will see money in March, April, and May for $300 each. A $1200 six-month job would see money in May through October for $200 a month.

So I have these fields: Job#, StartMonth, EndMonth, TotalAmount. How do I go about setting up a report or a table view that adds up what each month's revenue is going to be (and has been)?

I've tried building a spreadsheet-like grid of fields and scripted a routine that deletes and recalculates all the sums, but it grinds the entire database to a halt while it works (there are thousands of jobs). Plus, if the dates change, the fields don't recalculate automatically, and as time goes on I have to increase the number of months covered.

I feel like I'm overlooking something fundamental. Just from those four fields, is it possible to generate such a report?

Thanks in advance for any insight that can be offered.

[comment]

It cannot be done the way you describe, because you don't have enough records. To produce a standard Filemaker report, you'd need to split off each job's monthly revenue into individual records in a child table (and regenerate these each time the parent job's data changes).

Another way is to view the projected revenues through a calendar table, say 12 months at a time. This can be accomplished by calculating a multi-key in the Jobs table, so that a 3-month job ends up being related to 3 records of the calendar table.