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
------------------------------
Original Message:
Sent: 06-11-2020 10:04
From: Adam Keever
Subject: Summarizing within a table
Take a look at this @Paul Streit:
Parent Table with Summary Fields
When you relate two tables, you can add summary fields from the child table to the parent table giving you similar function of a summary report, but with dynamic filtering capability and you can export to spreadsheet.
------------------------------
Adam Keever
Original Message:
Sent: 06-10-2020 23:22
From: Paul Streit
Subject: Summarizing within a table
I have an application with hierarchical tasks in one table. This tasks table is a child to a parent projects table. Each task will have various types of costs like labor, materials, etc. There would be one Level 1 task (top level), decomposed to many Level 2 tasks, and each Level 2 task could be decomposed into many Level 3 tasks, and so on. I would like to be able to roll up costs for each level into a summarized table report that shows cost categories (labor, materials, etc.) as columns and Levels as rows (Level 1, Level 2, etc.).
It isn't an option to set up one table for each task level with a lineage of one to many relationships because the number of levels can vary for each project. I was able to create separate summary reports for each level, where the report prompts the user for the level they wish to see, but I'm stumped on how to create one report that consolidates all levels at once, plus grand totals by column and row.
It would be helpful if the report design did not use the Quick Base summary table format because ideally I'd like to be able to export the report to Excel, but if a summary report is the only way to do this, then I'll live with that.
Any suggestions on how to do this?
Thank you.
------------------------------
Paul Streit
------------------------------