Child Relationship Sum template field

  • 2
  • Question
  • Updated 3 years ago
  • Answered
I want to create a template field the sums the values of a child relationship field for each record of the parent.
Example:
-Parent has three children
-the child object has a field named "widgets"
-each child record has the number of widgets that child made in the widgets field

I want to sum up all the widgets created by all the children BUT ONLY FOR A CERTAIN DATE RANGE (Last Month) based on the created date of the child record

Is this possible with either template field or UI Only fields?

Thanks!
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb

Posted 3 years ago

  • 2
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Raymond,

Where are you going to show the total?  If you have a table showing the child records, then you can set a column summary on the 'Widgets' field to Sum all of the records that show.  Just be sure to add the condition of "LAST_MONTH" on the CreatedDate.

If you want to show the total on a detail page for the Parent, add an aggregate model for the Child and use a template field to show the total on the Parent detail page.  Here is an example of the aggregate model for the number of Contacts for an Account.


Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Thanks for the suggestions, Bill. I want to display it on a table of the parent model where each row displays the total number of widgets that all children of that parent record made. I'm basically trying to recreate a roll up Salesforce field. Roll up fields don't dynamically reset date ranges to reflect the current date( no "last month" option). I would like to recreate a rollup field that will determine if a group of child records occurred within a certain time frame and have that timeframe dynamically recalibrate based on today's date.
Thanks!
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
This is a great use case for the model lookup function in UI only formula fields.  Here is how: 

Create two models. 

Parent Model:   Includes record ID. 
Child Model: Aggregate model that aggregates the data in the children (sumValues), groups the children records by the ParentID, and filters with the "last Month" date literal. 

Then on the parent model add a UI only field of type "Formula" and give it syntax that looks somthing like this:

 MODEL_LOOKUP("Parent","sumValues","ParentId",{{Id}})   

Note.  The quotes on the first three properties are necessary, as is the merge syntax braces in the fourth. 

That should return the aggregation you are interested in. 

You may want to explore some model synching tools that have bee described elsewher ein the forum so that your Child model only includes the records from what is currently in memory for the parent model.  This will be important if you have lots of records..

Cheers. 
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Sounds like a solution! I'll give it a try and report back. Thanks!