Calculating based on summary fields

[Cassetti]

Hiya!

I'm building a basic project manager database for my company (mainly used to keep track of everything when moving into new building)

I have 3 tables:

Projects

Milestones

Milestone_Tasks

I want to create a field called percentage_complete in the projects table that takes the average percentage of the percent_complete of milestone_tasks and average them among all milestones related to the projects table

I hope that makes sense. I'm a bit confused with summary fields and how they work - i haven't played with them very much.

[Søren Dyhr]

Are each milestone task, atrributed with a boolean field for completed?

Are you showing the two sub-tables in the same portal?

--sd

[Cassetti]

Each Milestone Task has a status field set to open by default and closed after a script updates that field.

I have everything revolving around a central user_preferences field - that has global fields for milestone_selectedID or tasks_selectedID

everything is related by ID fields.

So on my layout, i have a section that lists the selected project (based on projectID)

and then i have the portal for milestone_list

The milestone layout has a section that overviews the milestone, and then has 2 portals: 1 for milestone_tasks, and another for milestone_expenses

I totally forgot about creating a new layout for reports. But I just printed up some documentation on how to do it - i have not played with generating reports to know how best to tackle this database.

Because in the milestones and milestone tasks, we have cost estimates - and then i want to generate a report that lists for a selected project, the milestones, and then the milestone expenses.

Lol, sorry if i'm confusing - its been a long day and my brain took an extended lunch-break around 3 hours ago. The slacker hasn't been back since.

[Søren Dyhr]

After having returned to this post, numerous times - not quite getting the whole picture. There were some items in the description that made me say to myself "there is something wrong here" without being abel to put the finger on the exact spot.

The things that made me hessitate, was 1) that I hardly ever use a 3-level relational structure that exactly resembles the same hierarchy the business model exhibited. 2) The use of globals are usually a remedy for a poorly structured solution. Finally 3)

i have not played with generating reports to know how best to tackle this database

This means that everything is decided by fooling around, until the tiniest evidence can give some direction, although there isn't anything that proves to be an actual contribution to the actual direction the solution should take.

The main problem here is that similar fields exists in every 3 of the tables, when it comes to it are all matters in each table a task with subtasks all with budgets and completion deadlines etc. What qualifies a task to be a milestone-task and how large would a task be to qualify to be a genuine Project.

In short would I say the relational model chosen is too rigid!

I think you would benefit from reading this:

http://jonathanstark.com/recursive_data_structures.php

--sd