Expand all | Collapse all

Formula field to count # of records

  • 1.  Formula field to count # of records

    Posted 07-09-2018 14:41
    Parent Tables: Nodes and Power Permits. 
    Child Table called Poles.
    Want to get a count of Power Permits per Node. When I create a Summary report I can group by Node and then by Power Permit and manually see there are x number of Power Permits, but I want a field I can use in reports that gives me this count. 
    Example: 1st column is Nodes, 2nd is Power Permits, 3rd shows count of Poles per Permit, but I need a field that shows Count of Power Permits so for DF012, this field would show "4".

  • 2.  RE: Formula field to count # of records

    Posted 07-09-2018 14:43
    Poles is child to Nodes and child to Power. Power and Nodes have no relationship. However, Power and Nodes are both children of Parent called "DID".

  • 3.  RE: Formula field to count # of records

    Posted 07-09-2018 20:19
    Can you just make a summary field on the relationship between DID and Power Permits to count the number of Power Permits?  Then you can make a lookup field if you need to get that down to Nodes if that is where you need to have that show.

  • 4.  RE: Formula field to count # of records

    Posted 07-09-2018 23:17
    Parent: DID > Children: Nodes, Power Permits
    Parent: Nodes> Child: Poles
    Parent: Power Permits > Child Poles

    Summary Field at DID: # of Power Permits where count of Poles is not 0
    Then lookup field to Nodes will show the # of Power Permits per DID, but I need to break that down further and show the # of Power Permits per Node.

    This is all because I need to build a project tracker for Nodes that shows these count among others. If I can get this one, I think I can figure the others out based on it's logic.. 

  • 5.  RE: Formula field to count # of records

    Posted 07-09-2018 23:56
    I think that you will need to create a table called Node-Permits where the Key field looks like this


    You can populate it initially by making a formula field on your details table and then making a summary report on that field and then using More ... to copy to another table. 

    Then you can make that table a child of the Nodes table by making the field DF012 on that table by formula off the field containing DF012-23556149

    so like Left([Node-Permit],"-")

    and using that to be a link to Nodes.  Then make a summary of the # of Note-Permits.

    But then you will need to auto maintain that Node-Permit table so the app knows to add new records when one does not exists.  You can make an action to do that when the parent does not exists.  The Table Node Permits would need to be a Parent to your most detail table and you can create a field in Node Permits which always calculates to true like [Node permit exists?] with a formula of


    Then look that up down to the detail table. then fire an action to create the parent record if it does not exists.  I also suggest making a safety net subscription report to detect of there are detail records and that Node Permit record did not get created by the Action - in case it fails from time to time.

  • 6.  RE: Formula field to count # of records

    Posted 07-10-2018 00:27
    Thank you for the help. I'll give this a try.