Record count in filter label suffix

  • 2
  • Question
  • Updated 4 years ago
  • Answered
Hello!

I am trying to add a count of the record that match a filter's criteria in that filter's label via the suffix.

Currently I have a global variable ({{$Model.Potential.data.length}}) that is giving me a count of the entire model, but I want it to show how many records of each Sales Stage there are. Any ideas? Thanks!
Photo of Craig Rosenbaum

Craig Rosenbaum

  • 4,776 Points 4k badge 2x thumb

Posted 4 years ago

  • 2
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
In think you do this using a model for each stage and adding each model as a source for the filter. Issue with that is that adding/removing stages would mean having to update the filter. Hopefully someone else has a better solution.
Photo of J.

J., Official Rep

  • 7,470 Points 5k badge 2x thumb
Craig,

Try creating an aggregate model on the sObject behind your Potential model, grouped by Stage and Aggregating on COUNT(Id). You may have set conditions on this model to get it to line up with the records in your table (or maybe you always want it to count all records). Use this new model as your filter source, and adjust your merge fields accordingly. Does that work?
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Sounds right to me. I'd like to use this as well, so please let us know.
Photo of Craig Rosenbaum

Craig Rosenbaum

  • 4,776 Points 4k badge 2x thumb
Thanks for the suggestion, working on it now - will let you know how it goes.
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Here's the basic approach to doing this: use an Aggregate Model as a Model Source for your Filter.

I highly recommend watching this Deep Dive starting at 20:38 for an overview of the approach and the steps involved, and if you've never used Aggregate Models before, check out this tutorial.

For the particulars for your scenario, here's what I'd do:

1. In your current page, add an Aggregate Model that calculates the number of records that are in each Sales Stage. Your Aggregate Model should have one Aggregation Field, COUNT(Id), with an alias such as "numberInStage", and one Grouping Field--the Stage field, with an alias "salesStage"
2. Change your Filter Source to be of type "Rows in a Model", and select your Aggregate Model. Enter the following for the Templates:

- Label Template: "Stage: {{{salesStage}}} ({{{numberInStage}}})"
- Value Template: "{{{salesStage}}}"

This should get you what you're after!
(Edited)
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Here's the XML for a sample page on the Opportunity object that does this:

https://raw.githubusercontent.com/skuidify/SamplePages/master/pages/OpportunityTabWithAggregateStage...


And here's what it looks like in the Page Composer:




And in the runtime:


Photo of Craig Rosenbaum

Craig Rosenbaum

  • 4,776 Points 4k badge 2x thumb
Worked like a charm! Thanks all - I was hung up on trying to use a global merge variable. The aggregate model was much easier. 

Out of curiosity, I only used two sets of curly brackets. Is there a reason for using three?
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Shouldn't matter here. If you were trying to show a field with special rendering behavior, such as a Reference Field or a Name field, then with Aggregate Models you might get weird output, but here I would actually use 2 brackets for the Label Template like you did.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Very cool. Going to implement this in a few places.