Counting parent records in child table

  • 1
  • 2
  • Question
  • Updated 3 years ago
  • Answered

I have a projects table which is 1 to M to the stage gates table.  I am creating a chart in the child table to portray how many projects will be available for approval in what months.  To do so I need a project count. I can summarise from a child table but not the other way round.  Any idea how to achieve this? 

 

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,142 Points 3k badge 2x thumb

Posted 3 years ago

  • 1
  • 2
Photo of Craig

Craig

  • 0 Points
Arshad, 

   You could set up Statistics table with one record (Record ID#1) then make that the parent of Projects. The Related Statistic field in the Projects table will be 1 (also the default value). You can then summarize all the Projects as a whole or specific conditions in Summary fields (on the Statistics table) to then bring down as a lookup field down to the Projects table then again down to the Stages table. This should get the '# of Projects' you are looking for.

Thanks!
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,142 Points 3k badge 2x thumb
Thanks Craig.  I already have a parent of projects table. Is it going to stuff up the relationship?
Photo of Craig

Craig

  • 0 Points
This would be a parent table (Statistic table is an example name) with one record (Record ID#1) then the Related Statistic field for all the Project's default value would be the value 1 so all the Projects would be a child of this one record. You can now summarize the # of Projects based on certain criteria then bring this value down to the Projects table then again to the Stage Gates table.