Discussions

Expand all | Collapse all

How to get the count of parent record in child table?

  • 1.  How to get the count of parent record in child table?

    Posted 01-18-2018 20:58
    I create a summary report in a child table.  I'd like to have the total count for the parent record in the summary report too.   However, there's no intrinsic way to do this.  Any one has some suggestions? 


  • 2.  RE: How to get the count of parent record in child table?

    Posted 01-18-2018 21:16
    If you edit the relationship, you can make a summary field for the count of the # of children.  Then look that up down to the child records. 

    Include that field in your summary report, but use a function of Average rather than Total so as not to multiply duplicate the total and just count it once, hence an average of all the identical values. 


  • 3.  RE: How to get the count of parent record in child table?

    Posted 01-18-2018 21:24
    Thanks for your quick reply. I think it is easy to explain as a simple example.
    Parent A:  3 Children
    Parent B:  2 Children
    I'd like to have a summary report to show
    Parent: Children
    2            5

    The count of children itself is easy to get from the summary table.  I need the count of parent in the summary table.
    Thanks!


  • 4.  RE: How to get the count of parent record in child table?

    Posted 01-18-2018 21:46
    OK, I'm starting to understand.  I think that the issue is that you need to count all the children but just the unique parents.

    How about this for an idea.

    Make a summary of the Minimum [Record ID#] of the child record.  Call it [Record ID# of 1st Child].

    Look it up down to the children.

    Then have a formula called [# unique Parents] 

    IF([Record ID#] = [Record ID# of 1st Child],1,0)

    Then summarize that field and each parent should only be counted once.


  • 5.  RE: How to get the count of parent record in child table?

    Posted 01-18-2018 22:06
    Very smart and quick idea!   Only problem is the minimum function will not be able change dynamically according to the filter of my report.  In order to get it work, I have to create many fields of [Record ID# of 1st Child] in the Parent table for different filters. Then create the respective formula field in the Child Table.  Thanks!


  • 6.  RE: How to get the count of parent record in child table?

    Posted 01-18-2018 22:09
    Yes I see that as a problem too.  I did not think about Dynamic Filters.


  • 7.  RE: How to get the count of parent record in child table?

    Posted 01-18-2018 22:19
    Thanks a lot.  Temporarily I will create four summary fields to satisfy my filter purpose.  Hope QB will build this feature in the future because it is a very typical summary data.