Aggregate Model Lookup

  • 2
  • Question
  • Updated 2 years ago
  • Answered
All of our Cases have 1 or more Tasks associated with them. I am interested in summing the value of a number field on those Tasks and presenting it in a field on our My Cases table.

I have attempted to accomplish this with an Aggregate Task model that sums the Duration field on the and a displays it in a UI-Only formula field on the Case model. The UI-Only field is loading blank. I have little experience with UI-Only fields and the Model Lookup function, so I'm sure I'm doing something wrong. 

Any suggestions?

Thank you!
Photo of Adam Johnson

Adam Johnson

  • 1,000 Points 1k badge 2x thumb

Posted 2 years ago

  • 2
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,040 Points 10k badge 2x thumb
Hi Adam, which kind of field is your Duration field? (Integer, number, etc.)

I think the first step would be to verify whether the aggregate model is doing its part in the process. The formula field is down the line, so to speak. Have you added the sum field from the Aggregate Task model somewhere your page so you can see whether that aspect at least is functioning? In other words, do you see a sum of the duration field on that model? 
Photo of Adam Johnson

Adam Johnson

  • 1,000 Points 1k badge 2x thumb
Hi Mark, thanks for the reply! 

The duration field is a number field and I have added the field to the page via a Template and a Table and both displayed the sum correctly.
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,040 Points 10k badge 2x thumb
I worked to mock this up with standard Salesforce fields, and there are a few gotchas to look for.

First, your aggregate model should be higher up in the list of models than the Case model, so that when the Case model's UI-only field looks for the sum, the sum has already been computed and is ready to be pulled into your formula field.

Next, make sure your MODEL_LOOKUP formula has the 4 parameters in the correct order. This was where I became stuck this afternoon. 

It should look similar to this: MODEL_LOOKUP("AggregateTaskModelNameGoesHere","sumDuration","AggregateTaskModelGroupingAlias",{{Id}})

The first parameter is the name of your aggregate model.

The second should be the name of the field that you are ultimately wanting to display, so, the sum of the Durations in your case.

The third parameter is the grouping's Alias Name from your aggregate model.

The fourth parameter is referring to the field in your Cases model that contains the search term. It's usually in double brackets / our merge syntax.

I would recommend experimenting with this on a very simple page, and making sure the last two parameters are grabbing the correct field names. For your use case, the last parameter should probably be the WhatId on your Case Model, and the third parameter would be the Alias Name you've chosen for the groupings in your Task aggregate model.
(Edited)
Photo of Adam Johnson

Adam Johnson

  • 1,000 Points 1k badge 2x thumb
Thank you for the detailed instructions, it is working perfectly now!