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 ?
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 node, with each Post-Aggregation Condition being defined as a separate . 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:
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?
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.
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?
Hey Old post, but I guess the âHavingâ method only applies to Salesforce Data? I just tried same exact logic on a DSO but itâs not working.
@skuid Since then any other method that this can be accoomplished?
Yes Dave - this is only supported on the Salesforce Datasource. there are a number of Salesforce exclusive featuers for aggregations that are really nice that we get for free. We have to build them out specifically for other datasources, and have not done so yet. I believe you had previously discussed grouping by date literal functions. Thats the other big one I know about.
Hi @Zach_McElrath ,
Thank you so much the explanation.
I filtered the aggregate result field using having clause but itâs not getting any filtered result. Here is the screenshot of my XML model. Can you please give any example that how can we use HAVING clause in XML.
Thanks,
Hetal
After your <groupby>
section, you can add your <havings>
section. It looks like youâre doing that correctly. You may need to experiment with your syntax for your individual having. Hereâs an example that works for me when I want to filter out zero values.
<havings> <having field="MyAmount__c" fieldfunction="SUM" operator="!=" value="0" enclosevalueinquotes="false"/> </havings>
in SOQL this would translate to
HAVING SUM(MyAmount__c) != 0
placed after the GROUP BY