Re: Summarizing within a table

Adam,

The issue with using summary fields is that the hierarchy of tasks is variable in depth and breadth. For example, one project might have five levels vertically while another might have seven, and within any given level, one project might have three tasks while another has ten. If I tried to define summary fields at the project level for every combination of the hierarchy, there could be thousands of summary fields. The only level that is easy to do with summary fields is Level 1 because by definition there is only one element at that level (the apex). 

This is like defining an app to capture the hierarchy of organization chart where the number of levels of the organization can vary (some companies might have more levels than others), and within a level, there could be different numbers of functions. 

I looked at defining separate tables for each level with relationship lineage, where a Level 1 table is related to a Level 2 table which is related to a Level 3 table and so on, but that appears to be very complex, especially if other tables have to be linked to these tables for things like resource assignments (or in the org chart analogy, assigning employees to a specific function in the org chart).

Paul Streit

------------------------------
Paul Streit
------------------------------

Adam,

The use case is close to what you describe, except that I don't know in advance how many tables (levels) there would be from task to sub-task to sub-sub-task and onward. I can of course limit the number of levels, but the application is for scoping out work for bid and proposal purposes. So the work breakdown structure could vary considerably from project to project in terms of depth (how many levels of sub-tasks), and horizontally (how many tasks at a given level). The key part of the use case is to roll up budgets to each level from whatever sub-task resource costs were assigned to.

So for example, say a project is to design an airplane. Level 1 is the airplane. Level 2 would have sub-tasks for the fuselage, engines, tail assembly, and wings. Level 3 would have sub-tasks for each Level 2 task, so for example, under the tail assembly there might be tasks for elevators, trim tab, rudder, etc. Level 4 sub-tasks for the elevators could include actuators, hinges, the elevator control surface, and other key subsystems. And it continues. At some lower level costs will be estimated and assigned. The challenge is how to roll up those costs to the intermediate levels. For example, the program manager may want to know labor costs for the tail assembly, and if they're high, drill down another level to see if the cost increases are coming from the elevators, trim tab, etc.

I often see projects with as many as five to seven levels, so that would require one table for each level. But if that's the solution then I'll look at that again.

I'll look at cascading lookups which I'm not using right now.

Does this explain the use case better?

Thank you for your time and thoughts on this.
Paul Streit

------------------------------
Paul Streit
------------------------------