Place "Count" conditions on Aggregate Models

  • 5
  • Question
  • Updated 3 years ago
  • Answered
  • (Edited)
i have an aggregate model that counts the number of times a first name last name combination appears in the client object

can i filter the model to only display > 1 for the count ?
Photo of ktyler

ktyler

  • 9,244 Points 5k badge 2x thumb

Posted 5 years ago

  • 5
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,004 Points 20k badge 2x thumb
Yes, this is possible, but currently only through XML.

Essentially what you are trying to accomplish is a "Post-Aggregation" Condition. Whereas a normal "Pre-Aggregation" Condition affects the set of records that get included in your aggregate query, a "Post-Aggregation" Condition is applied after the aggregate query is already run.

Taking your example, say you have 1000's of Client records. Say, for instance, you are only interested in Clients whose Age is over 65. You would write a "Pre-Aggregation" Condition (Conditions you're used to with Skuid right now) to limit the scope of Client records that your Client_dups Aggregate Model even looks at. So, then, your Aggregate Model is run. It groups all Clients over the age of 65 by some field, then runs your aggregation --- a Count. So now you have, say, 15 "aggregate result" records.

Now, you want to apply a Condition to THESE 15 aggregate result records --- finding just the ones where the Count is greater than 1.

THIS is the scenario where "Post-Aggregation" Conditions come into play.

Skuid currently only supports Post-Aggregation Conditions via XML, so for anyone reading this who's uncomfortable working with XML --- PLEASE, WAIT FOR THIS TO BE SUPPORTED IN THE PAGE BUILDER.

But, for those of you at home in XML, here we go...

In the Salesforce world, Post-Aggregation Conditions are achieved via a "HAVING" Clause. So in Skuid XML, we store Post-Aggregation Conditions in a <havings> node, with each Post-Aggregation Condition being defined as a separate <having/>. Other than that slight syntactical difference, having nodes are exactly the same as condition nodes, so basically if you just think of how your Skuid Condition XML would look, that's how your Having XML should look.

**The only difference is that Havings / Post-Aggregation Conditions can only operate on Fields that you either Grouped or Aggregated on.** Havings / Post-Aggregation Conditions are applied, by definition, only to the result set --- so the only "fields" that are available to filter on are fields in your aggregate result set.

So here's the XML that you will need to add to your Client_dups Model XML to achieve what you're after:



Here's a screenshot of a Model XML just to give some context:



The key piece to notice here that may not be familiar is the "fieldfunction" XML attribute. This is where you select the aggregation function that you are filtering on.

And here's what it looks like on a working finished page --- exactly the same, except that only rows where COUNT(Id) is 2 or more are included:

Photo of Craig Rosenbaum

Craig Rosenbaum

  • 4,776 Points 4k badge 2x thumb
Is there anyway to leverage another aggregate field as the value parameter? I tried using that field's alias and it did not work.

Also, any way to leverage these via a table filter for the user to click on/off? I tried assigning a name and it did not show up as a manual condition filter option, but anyway to do this in XML?
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,004 Points 20k badge 2x thumb
Craig, not sure what you mean by your first question, can you elaborate?

On the second question, no, Filters do not currently support Havings/Post-Aggregation Conditions, but they will in our upcoming Skuid release. More than likely you still won't be able to create Havings/Post-Aggregation Conditions from the Page Composer, but Post-Aggregation Conditions will be filterable via Skuid UI and via JavaScript.
(Edited)
Photo of Craig Rosenbaum

Craig Rosenbaum

  • 4,776 Points 4k badge 2x thumb
In your example, value is hard coded to =1. I was asking if there is a way to make that dynamic by setting it equal to an aggregation from the model instead of 1. Does that make sense?