One Child Record Per Parent Record

  • 1
  • Question
  • Updated 4 years ago
  • Answered
  • (Edited)
I'm trying to create a table that is about child records, but I only want to display one child record per parent record by the most recent date. I've tried to figure out how to do this through conditions and subqueries, but I haven't figured it out yet. Anyone have a suggestion on how to display multiple child records, but only one per parent record?
Photo of Nate Gilbertson

Nate Gilbertson

  • 1,194 Points 1k badge 2x thumb

Posted 4 years ago

  • 1
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Create a separate Child Records model.  In the advanced tab of the model properties set your "Max number of records" property to 1,  and your default order to  CreatedDate DESC.  That should do the trick...
Photo of Nate Gilbertson

Nate Gilbertson

  • 1,194 Points 1k badge 2x thumb
I tried this and it only displays one child record on the table. I want all child records of this object to display, but I only want to have one child record displayed per parent record.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Ok - that is different.  Hmm.....

One solution may be to create a model on the parent object,  and then add the child fields using the "child relationships" method.   

When you add a child relationship - and click on the relationship in the field list (In the left hand pallette section of the builder),  there will be a Properties tab.  There you can limit your child relationship records to 1,  and also set the order by to CreateDate.  

Then you drag that child relationship item from the field list down into your table and it acts like a template component,  where you define a custom label and identify the merge field. 

In the picture below I've added Events to my Contacts model,  and added a "most recent event" field to my table.

Here is the child relationship properities: 


Here is the field definition: 



The only downside to this method is that the child relationship data is not editable inline.  If you need to edit the child data, you can probably get this done in a popup or drawer connected to the table. 

Let us know if this works.