Table Relationship (% in Piechart)

  • 0
  • 1
  • Question
  • Updated 2 weeks ago
  • In Progress

I have 3 tables:

·         Projects

·         Tasks

·         Subtasks

Each project has multiple tasks

Each task has multiple subtasks

Each project has 3 phases:

·         Planning

·         Development

·         Implementation

Projects, Tasks, and Subtasks have the following 6 statuses:

·         Not Started

·         In Progress

·         Hold

·         Risk

·         Completed

·         Canceled

I wanted to create a piechart (showing % of the project in not started, in progress, hold, risk, completed, canceled) calculated based on the % of subtasks and % of tasks status against each project phase

E.g.,

·         Subtasks table: I want a % calculation based on the status of each subtasks assigned to a project

·         Tasks table: I want a % calculation based on the average pulled from subtasks status assigned to a project

·         Projects table: I want a % of calculation based on the average pulled from tasks assigned to a project

Can someone please help?
Photo of Kingsly Samuel

Kingsly Samuel

  • 446 Points 250 badge 2x thumb

Posted 3 weeks ago

  • 0
  • 1
You can create the pie chart on the child table then put it on the form for the parent record using the report link field and choosing the pie chart.

I'm not sure what you mean by the 'average' task or subtask. However, if your task has a field that calculates the 'average' subtask status, then you could use that field to build the report.




Photo of Kingsly Samuel

Kingsly Samuel

  • 446 Points 250 badge 2x thumb
Sorry for my delayed response. So, this is how I have it structured on my app.

Now, all I want is to create a piechart on the project level to show accurately as to how much % each project is done, based on task status on all tasks assigned to a project. But I also wanted to filter and see the piechart at project phases level as well within a project.

I was originally thinking that I need to capture an average % of status on Tables 2 and 3 and then calculate the project % based on the average pulled in table 2 and 3. I might be wrong with my approach.

Hope this helps. Please advise.



Without using fancy coding, you have two options:
  1. You can summarize the number of Tasks in each status on the Projects table (from the relationship settings) then use a formula to display the % complete on reports & forms.
  2. You can create the pie chart on the Tasks table, then have is displayed on the Projects record. ie your users will have to click into the project to see the pie chart status breakdown. < This is the suggestion I made in my prior post.
From your post it sounds like you want the visual of the Pie Chart. When you a report/chart from a child table onto the parent record it will automatically filter it to only show the related records (even if it normally shows all records on the child table).

The same approach can be taken with the Subtasks.

Let me know if this makes sense.

-Sharon
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,482 Points 20k badge 2x thumb
>Without using fancy coding ...

Use fancy coding - probably just a couple dozen lines of code to process the data along with the sample code (slightly modified) for a basic HighChart bar chart.

But I am not clear exactly how to roll up the data and what the pie chart should look like. What would be very helpful is if you could (1) annotate your graphic with the numbers that  roll up the data from sub-tasks to tasks and (2) supply a piechart (perhaps generated through Excel) that corresponds to your sample data implied by your graphic.