Table Relationship (% in Piechart)

Question

I have 3 tables:    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Projects    ?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Tasks    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Subtasks    Each project has multiple tasks    Each task has multiple subtasks    Each project has 3 phases:    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Planning    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Development    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Implementation    Projects, Tasks, and Subtasks have the following 6 statuses:    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Not Started    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  In Progress    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Hold    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Risk    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Completed    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Canceled    I wanted to create a piechart (showing&nbsp;% of the project&nbsp;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.,    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Subtasks table: I want a %  calculation based on the status of each subtasks assigned to a project    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Tasks table: I want a %  calculation based on the average pulled from subtasks status assigned to a project    ?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  Projects table: I want a % of  calculation based on the average pulled from tasks assigned to a project    Can  someone please help?

quickbasejunkie · Answer

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.

kingslysamuel · Answer

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.

quickbasejunkie · Answer

Without using fancy coding, you have two options:

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.
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

_anomdiebolt_ · Answer

&gt;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&nbsp; 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.

Forum Discussion

Table Relationship (% in Piechart)

Related Content

Cross App Relationships

How to Link Tables in a Complex Many-to-Many Relationship?

Moving Fields from One Table to Another

Table is missing

Table-to-Table Relationship Question