Summary Field is showing blank when there is data to return

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a Relationship created where the Parent is called "Construction Project" and the Child is called "Permit". The relationship has been working fine in a Report Link but when I tried to add a Summary Field to pull a maximum date, I'm seeing no results. I did a test and did a regular Summary to show the number of child records and I'm getting 0. I'm not using the Reference Field in the Permit Form. My Permits can also have many Construction Projects so I don't want to be tied to just one Construction Project. 

Is there a way to work around this problem? I'm a fairly new Quickbase user and learning as I go. Thank you for your help! 
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

You may want to create a many to many relationship by incorporating a third table called Details. Your Permits table would be parent to this Details table, likewise, your Construction table would also be parent to this Details table. You may want to create a copy of what you have, and in the copy, disassociate the Construction to Permits relationship, and build the many to many I've described above. In the Details child table, you'd 'select' the Construction parent record and also select a Permit parent record. This orientation of relationships allows you to establish a many to many between the Construction table and the Permits table -- done indirectly via the Details table.

Construction table >>>> Details table

Permits table >>>>>> Details table

Does this make sense?


Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
Yup, it makes sense! Will I have to update the Details table for new entries or can this automatically make the connection if I use certain fields that are present in both the Construction Project and Permit? 
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
You can allow the Details table to bring in lookup fields from both parents along with any hard data for the Details record you want to input. The Details records can be strictly lookup fields too if you wish. You can then create your summary max date field in any one of those two parents to provide the max create date or any date field you wish to summarize from the Details table if that is still an item after you reconstruct the relationships.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
Sounds good, thank you so much!