Discussions

 View Only
Expand all | Collapse all

Summarizing within a table

  • 1.  Summarizing within a table

    Posted 06-10-2020 23:22
    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
    ------------------------------


  • 2.  RE: Summarizing within a table

    Posted 06-11-2020 10:04
    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
    ------------------------------



  • 3.  RE: Summarizing within a table

    Posted 06-11-2020 10:35
    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
    ------------------------------



  • 4.  RE: Summarizing within a table

    Posted 06-11-2020 12:25
    Do you have a business process map for what you are trying to do? Those can be helpful in understanding how the information must be structured and how it flows.

    From my understanding of what you are stating, you would need these relationships one Project to many Tasks, one Task to many Sub Tasks, and an additional one Sub Task to many Sub-Sub Tasks. This set of relationships would not be negatively affected by the number of tasks. In fact this would allow you to summarize tasks so from your Project table you could see how many Tasks, Sub Tasks, and Sub-Sub Tasks are contained within that Project.

    For example the Projects table with the number of tasks in each level:

    And also at the lowest level, you can cascade lookups so that you see Project, Task, Sub Task, and Sub-Sub Task and can dynamically filter:
    Which can then be grid edited:

    Most people stop at the sub task level. I am interested to understand your use case and why there are so many levels of tasks.

    ------------------------------
    Adam Keever
    ------------------------------



  • 5.  RE: Summarizing within a table

    Posted 06-11-2020 12:49
    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
    ------------------------------



  • 6.  RE: Summarizing within a table

    Posted 06-11-2020 13:59
    I did an app like this before for a Bill OF Materials for auto components with subassemblies or sub-assembles  and in that design, as I recall, we had 9 levels of depth allowed.  I don't think that we has nine tables but I think that we had we had nine relationships.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Summarizing within a table

    Posted 06-11-2020 15:41
    I was able to get the same result using one tasks table and relating it to itself:


    ------------------------------
    Adam Keever
    ------------------------------



  • 8.  RE: Summarizing within a table

    Posted 06-11-2020 16:08
    Adam,

    Very helpful. Would it be possible for you to send me a link to your tasks test app above so I can see how you constructed the tasks table relationship to itself, and the cascading lookups? 

    Thank you,
    Paul Streit

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



  • 9.  RE: Summarizing within a table

    Posted 06-11-2020 16:40
    Edited by Adam Keever 06-11-2020 16:42




    You end up with some empty spaces in some of the fields. I created a new table report, selected the wanted columns to display, and filtered to only show records where all three fields were not equal to blank. I changed my task field name in the task table to Level 3 Task. Each task lookup then became Level 2 Task and Level 1 Task. As you add in new levels you would have to update the task field name in the task table.






    ------------------------------
    Adam Keever
    ------------------------------



  • 10.  RE: Summarizing within a table

    Posted 06-11-2020 17:02
    Very helpful! I will review this closely and start working on revising my application. Thank you for your time and patience.

    Paul Streit

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



  • 11.  RE: Summarizing within a table

    Posted 06-25-2020 00:47
    Adam,

    OK, I was able to get the application to work. Your instructions were very helpful. I just have one issue remaining that I haven't been able to resolve. The screenshot below shows the results thus far. On the first row, at the top level of the WBS (Work Breakdown Structure) hierarchy, the sums for Total Labor (Hrs), Total Labor, and Total Materials are zero, but they actually should be the same as the grand totals at the bottom of the report. I think what's happening is that no labor hours, labor dollars, or materials dollars are entered for the 116355.01 and 116355.02 levels which are the immediate subordinates for the 116355 level. So the summary totals are zero. Ideally the summary fields would total vertically, essentially summarizing the lower level summary totals. So the new good news is that I think I have the relationship right and the hierarchy within the table is established correctly. But I don't think I have the summary fields correct for rolling up the labor and materials subtotals to each level. I also included a snapshot of the relationship structure. I'm not clear whether I need to add summary fields of the summary fields, which I think would be confusing for the user, or if I need to do something else.

    Also, the relationship graphic shows two report links on the parent side. I'm not clear why that occurred, and when I tried to delete one assuming it was a duplicate, both disappeared. The report link works fine in the report and form, so I left them there. I'm assuming the system created two identical report links because of the self-referencing relationship, but I don't understand why. 

    Thank you,
    Paul Streit

    A screenshot for discussion purposes.

    For discussion purposes


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



  • 12.  RE: Summarizing within a table

    Posted 06-25-2020 09:30
    I am not familiar with the summary technique your are using. Could you explain how you created the summary fields in your child table?

    Have you tried adding a summary field in the parent table of the relationship?



    ------------------------------
    Adam Keever
    ------------------------------



  • 13.  RE: Summarizing within a table

    Posted 06-25-2020 10:39
    When I use the Add Summary Field button, the created summary field actually ends up on the right side (child side) of the relationship. Note the four summary fields at the bottom of the right side. I didn't place them there, Quick Base places them there even though I created them with the Add Summary. I assumed this Quick Base behavior is because of the self-referencing table relationship, where the roles are effectively reversed. It actually works very well based on the sample data

    I'm going to add more levels into the hierarchy today to see if this roll up issue is limited to just the very top of the hierarchy (a record with no parent like the CEO) or if it's for all intermediate levels that are only roll-ups (no entries in the materials, labor hours, or labor fields. I'll report back on that.

    I also thought of trying to add another set of summary fields. These new fields would summarize the first set of summary fields (the four summary fields at bottom right of the relationship diagram), just to see if they will produce what I need (summary statistics for the top record in the hierarchy).

    Adam, does this answer your question?

    Paul Streit

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



  • 14.  RE: Summarizing within a table

    Posted 06-25-2020 10:57
    I just tried adding summary fields of the summary fields as noted above. They worked! I now have totals for the record with no parent at the top of the hierarchy. See new graphics below for the updated report and relationship diagram. When I added the summary fields using the Add Summary button, the field creation process was as normal, but the fields showed up on the right side. But they work in the field list normally, so go figure. 

    The next step is to add more intermediate levels to verify this works, and if does, I'm almost there. 

    One last question - is there any option to suppress zeros for summary fields, in other words, just show the result as blank?

    Paul Streit




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



  • 15.  RE: Summarizing within a table

    Posted 06-25-2020 11:54
    Awesome. Way to stick with it @Paul Streit!

    Try to uncheck the "Treat blank values as "0"" and see if that does it:



    ------------------------------
    Adam Keever
    ------------------------------



  • 16.  RE: Summarizing within a table

    Posted 06-25-2020 16:56
    Unfortunately unchecking the treat blank values as "0" box didn't have any effect. Also, after more testing I discovered that the summary fields only total up for direct child records. So if a record has 4 child records, and one of those child records has a grandchild record, the parent will only show numeric totals for the 4 child records. The grandchild record's numeric value is only rolled up to the child level, not any further to the parent. Stated another way, the statistics only work down one level in a hierarchy, which I suppose makes sense because the self-referencing relationship is only at one level, meaning one parent to multiple children, but it doesn't know about grandchildren or great grandchildren. Modeling hierarchy in QB is hard. I'm not there yet.

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