Forum Discussion

PaulStreit's avatar
PaulStreit
Qrew Member
5 years ago

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 b...
App builders
Relationships
AdamKeever1's avatar
AdamKeever1
Qrew Commander
5 years ago
I was able to get the same result using one tasks table and relating it to itself:


------------------------------
Adam Keever
------------------------------
PaulStreit's avatar
PaulStreit
Qrew Member
5 years ago
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
------------------------------
  • AdamKeever1's avatar
    AdamKeever1
    Qrew Commander
    5 years ago




    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
    ------------------------------
    • PaulStreit's avatar
      PaulStreit
      Qrew Member
      5 years ago
      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
      ------------------------------
      • PaulStreit's avatar
        PaulStreit
        Qrew Member
        5 years ago
        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




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