I have a table called Projects, and for each project, there is a Start Date and an End Date. I would like to generate reports with a line for each month of the project's duration. Each of those lines would have calculation fields based on related tables.

I've thought of doing this with a portal on the Projects layout. In that case, I guess I'd have a related table called Months. But how would I populate the related table? One option might be to write a script that creates new records in Months based on the date range, but ideally, I'd have it dynamically update when the user changes Projects::Start Date/End Date.

Is it possible to do this dynamically? Or would I need to have a button that the user clicks after updating the dates, which would then clear the related records in Months and repopulate?

Edited December 8, 200619 yr by Guest

Update:

My employer's requirements have changed. Now they want a report that looks like this:

Employee #1

-----------

  PROJECT   JAN   FEB   MAR   etc

  -------   ---   ---   ---

  Prj #1     50%   50%  100%

  Prj #2     25%   50%    0%

  --------------------------

  TOTALS:    75%  100%  100%



Employee #2

-----------

  PROJECT   JAN   FEB   MAR   etc.

  -------   ---   ---   ---

  Prj #3     25%  75%     0%

  Prj #4     65%   0%    80%

  Prj #4     10%   0%    20%

  --------------------------

  TOTALS:    75%  75%   100%

Here's how it works. There is an Employees table, a Projects table, and an Assignments table. Assignments connects employees to projects. For each assignment, it stores a start date, stop date, and what percent of the employee's time should go to that assignment.

I need to make a report that shows how employees are spending their time on a month-by-month basis. To take an example from the above, in January, Employee #1 is devoting 50% of his time to Project #1 and 25% to Project #2, which means he's only working 75% of the time.

To make things more difficult, the date range for the report cannot be hard-coded. The user should be able to specify that the report runs from any month to any other month.

Is this possible in Filemaker? (Nested portals come to mind, but as I understand it, that's not supported.) Would it be so difficult or ugly that someone with moderate FM experience who comes after me would have a rough time extending it?

Edited December 8, 200619 yr by Guest

Just use calculated HTML and WebViewer, using the data:text/html technique.