I am new to Filemaker but have some experience with relational database systems. I investigate to use Filemaker for a small application to manage the staffing of projects. But I am still not sure what the best approach is.
I use a simple model for evaluation. I have two tables
Project (id:Number, name:Text)
Employee (id:Number, name:Text)
and a relation table:
Staffing (project:Number, employee:Number, week:Number, persondays:Number)
Now I would like to have two yearly overview reports, one for the projects one for the employees. The one for the projects should be something like this
NAME KW-1 KW-2 KW-3 ...... KW-52
project1 12 11 5 ....... 0
project2 0 0 4 ........ 4
For each project the sum of all person days of all staffed employes per week in a year is displayed.
For me, the problem is here, that the columns of the report are not the columns in any table. This seems to be hard to express in Filemaker.
My current sollution is to add 52 calculated fields to the project table. But, this does no feel like a maintainable solution.
Any other idea or comment would be appreciated.