Discussions

Expand all | Collapse all

Table Relationship (% in Piechart)

  • 1.  Table Relationship (% in Piechart)

    Posted 10-25-2018 14:21

    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?


  • 2.  RE: Table Relationship (% in Piechart)

    Posted 10-25-2018 21:14
    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.






  • 3.  RE: Table Relationship (% in Piechart)

    Posted 10-31-2018 12:54
    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.





  • 4.  RE: Table Relationship (% in Piechart)

    Posted 10-31-2018 22:05
    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


  • 5.  RE: Table Relationship (% in Piechart)

    Posted 11-01-2018 14:59
    >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.