Record Metrics in a Drawer

  • 1
  • Question
  • Updated 2 years ago
  • Answered
Hello,

I have a table for a custom object called Checklist_Item__c. We have a custom lookup field on Tasks that looks up to Checklist_Item__c. In the table, for each Checklist_Item__c record I would like to open a drawer that has some analytics on all of the Tasks related to that Checklist_Item__c. One of the metrics I want is the average time to complete tasks for each Checklist_Item__c. This is captured in a custom formula field on the Task called Age. So basically when I open a drawer for I want the average Age of all closed tasks related to that Checklist_Item__c. 

I have tried this two ways: The first is a template field right in the drawer that displays the average Age aggregation in the Task Model. This will work for the first drawer opened, but once I open another drawer, the value from the first drawer will change to the second. The second way I have tried this is displaying the field in a table/field editor with a Context rule that says only display info when Task.Checklist_Item__c == Checklist_Item__c.Id. When this rule is applied no information shows up at all, not even the custom label I have for that field. 

Is there a way to calculate this metric individually for each drawer without affecting the other drawers that have been opened?
Photo of Joe Dobbels

Joe Dobbels

  • 526 Points 500 badge 2x thumb

Posted 2 years ago

  • 1
Photo of Chandra V

Chandra V, Champion

  • 6,966 Points 5k badge 2x thumb
On drawers, you need to have your condition on your Task model set to "merge in new data with the old" instead of completely replace data.  Then you also need the context condition, so the template/chart knows what row to show data for.  Your context condition looks right... just make sure you have the Id field selected in your Checklist Item model, and the Checklist_Item__c selected in your Task model.
Photo of Christine Jessen

Christine Jessen

  • 670 Points 500 badge 2x thumb
Hi Joe!

Chandra is correct, you need to ensure you have the "Get More - Merge in New Data with Old" set on your Before Load actions for the drawer and update the context condition for components inside of this drawer.  What's tricky about drawers is that they still all refer back to one model, and so as this one model is re-queried, your metrics updates for the entire set of model data.  Rather than calculating the metrics when opening the drawer, I'd recommend this solution:

1.) Create a new aggregate model based off of Tasks.  Set the aggregation to calculate the average age.  Then set the grouping to "Checklist_Item__c".  You've now created a model where you have info for each Checklist Item and the average age associated with each item.

2.) Create a UI-Only Model_Lookup formula in your Checklist_Item__c model.  This will lookup the average age in the aggregate model and pull that info into the checklist model.  The model lookup formula should be something like this:
MODEL_LOOKUP("AggregateModelName", "avgAge", "checklistItemc", {{Id}})
Remember - when referencing aggregate model fields to use the alias name and not the field API name.

3.) In your drawer, add a field editor that is connected to the Checklist_Item__c model's newly created UI-Only field.  Add a context condition such that Id = Id (this will be the default context condition).  This essentially just grabs the info from the row you have the drawer opened.  You can also add the UI-Only field into the Checklist_Item__c table if you want the user to view the metrics without opening the drawer.

Hopefully this works for you!  Good luck!

Christine
(Edited)
Photo of Joe Dobbels

Joe Dobbels

  • 526 Points 500 badge 2x thumb
Thanks everyone! 

It turns out the issue was because I did not group the aggregate task model by Checklist Item. Once I did that everything displayed properly.