Formula field to count # of records

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
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".


Photo of Ruby Kapil

Ruby Kapil

  • 880 Points 500 badge 2x thumb
  • frustrated

Posted 5 months ago

  • 0
  • 1
Photo of Ruby Kapil

Ruby Kapil

  • 880 Points 500 badge 2x thumb
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".
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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.
Photo of Ruby Kapil

Ruby Kapil

  • 880 Points 500 badge 2x thumb
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.. 

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
I think that you will need to create a table called Node-Permits where the Key field looks like this

DF012-23556149

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

true. 

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.
Photo of Ruby Kapil

Ruby Kapil

  • 880 Points 500 badge 2x thumb
Thank you for the help. I'll give this a try.