Filter based on distinct or aggregate query

  • 1
  • Question
  • Updated 6 years ago
  • Answered
I want to filter my table of accounts using a picklist based on the distinct set of owners of those accounts. i.e. the query for the table's model is "select name from Account" and returns 50 accounts, and the query I want behind the filter is "select distinct Owner Allias from Account".

Is this possible? I'm pretty sure SOQL doesn't do distinct, so I tried using a model with an aggregate ("select Owner Alias, count(Id) from Account group by Owner Alias"), but couldn't make it work. I just got blank entries in the filter picklist.

Thanks.
Photo of Glenn Elliott

Glenn Elliott, Champion

  • 7,748 Points 5k badge 2x thumb
  • pensive, but unperturbed

Posted 6 years ago

  • 1
Photo of Anna Wiersema

Anna Wiersema

  • 10,910 Points 10k badge 2x thumb
Yeah, I'm not sure if you can do this exactly in Skuid, but what you can do is create an Owner filter for accounts that displays a list of Owners that is sourced from another model on your page - e.g. I have an "Owner" model on the User object where I can specify "IsActive=true" and which Profiles I want etc. and then use the records from that model as options in the filter.

Unfortunately, this doesn't guarantee that your filter will only display users who own the accounts returned by your Accounts Model, but it will display a distinct list of users which you can tailor more specifically with conditions on the Owner model.

Here's a sample page with a condition like this. You can paste it into your org by creating a new page, Saving it in the builder, and clicking on the View/Edit XML link at the bottom of the page:


<skuidpage showsidebar="true" showheader="true" tabtooverride="Accounts">
<labels/>
<models>
<model id="Accounts" limit="50" query="true" createrowifnonefound="false" sobject="Account" orderby="Name">
<fields>
<field id="Name"/>
</fields>
<conditions>
<condition type="fieldvalue" value="" enclosevalueinquotes="true" field="OwnerId" state="filterableoff" inactive="true" name="OwnerId"/>
<condition type="userinfo" value="" field="OwnerId" operator="=" enclosevalueinquotes="true" userinfotype="userid" state="filterableoff" inactive="true" name="Mine"/>
</conditions>
</model>
<model id="Owner" limit="50" query="true" createrowifnonefound="false" sobject="User" orderby="Alias">
<fields>
<field id="Alias"/>
<field id="Id"/>
</fields>
<conditions>
<condition type="fieldvalue" value="true" enclosevalueinquotes="false" field="IsActive"/>
<condition type="userinfo" value="" field="Id" operator="!=" enclosevalueinquotes="true" userinfotype="userid"/>
</conditions>
</model>
</models>
<components>
<pagetitle model="Accounts">
<maintitle>Accounts</maintitle>
<subtitle>List</subtitle>
<actions/>
</pagetitle>
<skootable showconditions="true" showsavecancel="true" searchbox="true" searchmethod="server" createrecords="true" model="Accounts" pagesize="10" showexportbuttons="true" mode="read">
<fields>
<field id="Name"/>
</fields>
<rowactions>
<action type="edit"/>
<action type="delete"/>
</rowactions>
<massactions defaultlabel="Mass Actions" defaulticon="ui-silk-wand" usefirstitemasdefault="true">
<action type="massupdate"/>
<action type="massdelete"/>
</massactions>
<actions defaultlabel="Global Actions" defaulticon="ui-silk-wand" usefirstitemasdefault="true"/>
<views>
<view type="standard"/>
</views>
<filters>
<filter type="select" filteroffoptionlabel="Any Owner" createfilteroffoption="true" condition="OwnerId">
<sources>
<source type="manual" model="Owner">
<options>
<option label="Owner: Me" type="complex">
<effects>
<effect action="activate" value="" condition="Mine"/>
</effects>
</option>
</options>
</source>
<source type="model" model="Owner">
<labeltemplate>Owner: {{Alias}}</labeltemplate>
<valuetemplate>{{Id}}</valuetemplate>
</source>
</sources>
</filter>
</filters>
</skootable>
</components>
<resources>
<labels/>
<javascript/>
<css/>
</resources>
</skuidpage>

Photo of Zach McElrath

Zach McElrath, Employee

  • 52,236 Points 50k badge 2x thumb
Official Response
Glenn,

Actually, your aggregate idea is great, and should work! Building off of Anna's example, here's a page that should do what you're after:



<skuidpage showsidebar="true" showheader="true" tabtooverride="Accounts">
<labels/>
<models>
<model id="DistinctOwners" limit="" query="true" createrowifnonefound="false" sobject="Account" type="aggregate">
<fields>
<field id="Id" name="countId" function="COUNT"/>
</fields>
<conditions/>
<groupby method="simple">
<field id="OwnerId" name="ownerId"/>
<field id="Owner.Alias" name="ownerAlias"/>
</groupby>
</model>
<model id="Accounts" limit="50" query="true" createrowifnonefound="false" sobject="Account" orderby="Name">
<fields>
<field id="Name"/>
<field id="OwnerId"/>
<field id="Owner.Name"/>
<field id="Owner.Alias"/>
</fields>
<conditions>
<condition type="fieldvalue" value="" enclosevalueinquotes="true" field="OwnerId" state="filterableoff" inactive="true" name="OwnerId"/>
<condition type="userinfo" value="" field="OwnerId" operator="=" enclosevalueinquotes="true" userinfotype="userid" state="filterableoff" inactive="true" name="Mine"/>
</conditions>
</model>
</models>
<components>
<pagetitle model="Accounts">
<maintitle>Accounts</maintitle>
<subtitle>List</subtitle>
<actions/>
</pagetitle>
<skootable showconditions="true" showsavecancel="true" searchbox="true" searchmethod="server" createrecords="true" model="Accounts" pagesize="10" showexportbuttons="true" mode="read">
<fields>
<field id="Name"/>
<field id="OwnerId" displaytemplate="{{Alias}}">
<label>Owner</label>
</field>
</fields>
<rowactions>
<action type="edit"/>
<action type="delete"/>
</rowactions>
<massactions defaultlabel="Mass Actions" defaulticon="ui-silk-wand" usefirstitemasdefault="true">
<action type="massupdate"/>
<action type="massdelete"/>
</massactions>
<actions defaultlabel="Global Actions" defaulticon="ui-silk-wand" usefirstitemasdefault="true"/>
<views>
<view type="standard"/>
</views>
<filters>
<filter type="select" filteroffoptionlabel="Any Owner" createfilteroffoption="true" condition="OwnerId">
<sources>
<source type="model" model="DistinctOwners">
<labeltemplate>Owner: {{ownerAlias}}</labeltemplate>
<valuetemplate>{{{ownerId}}}</valuetemplate>
</source>
</sources>
</filter>
</filters>
</skootable>
</components>
<resources>
<labels/>
<javascript/>
<css/>
</resources>
</skuidpage>



There are two tricks to this:

(1) In your "DistinctOwners" Model, you need to group by both OwnerId (first) then by Owner.Alias (second), so that you have both of these fields available to use in your Owner Filter.

(2) In your Owner Filter, on your Accounts table, use "{{ownerAlias}}" as the Label Template, and "{{ownerId}}" as the Value Template. This is necessary because you're using an Aggregate Model, in which all of your grouped / aggregated fields have Alias Names. You have to use these Alias Names whenever you want to use these grouped / aggregated fields.
Photo of Glenn Elliott

Glenn Elliott, Champion

  • 7,748 Points 5k badge 2x thumb
Thanks Zach/Anna.

I ran Zach's page. The filter list renders fine now and the filter works on the Any Owner option.

But when I select any of the owners in the list, the table returns no records.

Moreover, when I select one of the owners (DerekF), a different owner shows as being selected (TimS). Wacky.

Any thoughts?

Successful full table:


Table records returned when DerekF selected:
Photo of Zach McElrath

Zach McElrath, Employee

  • 52,236 Points 50k badge 2x thumb
Hi Glenn, sorry for the delayed response on this one. You are correct, the page above doesn't quite work. There's just one small thing to change, and I promise this will finally work! :)

I've changed the Page XML in my post above so that it will work now, so you can just copy and paste it. OR, you can do this:

Go into the "Any Owner" filter, select the first Source (of type Model), and change the Value Template to:

{{{ownerId}}}

(with three brackets). This will make it work!


Explanation: looks like there's some weirdness going on in Skuid with reference fields in filters, so the double-bracket syntax won't work.
Photo of Glenn Elliott

Glenn Elliott, Champion

  • 7,748 Points 5k badge 2x thumb
Yep, three brackets works a treat. Thanks very much. (Aren't you supposed to be on holiday for the 4th?)

So is this syntax a temporary fix until the "weirdness" is resolved?
Photo of Zach McElrath

Zach McElrath, Employee

  • 52,154 Points 50k badge 2x thumb
I'm working today, off tomorrow :)

Not sure when or how we'll be able to make the two-brackets work here, I think that for now, knowing about the three brackets is a good trick to have available for scenarios like this. Essentially, here's what the difference in bracket syntax means:

Two brackets: {{
= "Try to render this data in a manner appropriate to the display type"
Example, for OwnerId field: render a hyperlink to the Owner's user record, displaying the Owner's Name, e.g. "John Smith" (with this being a hyperlink)

Three brackets: {{{
= "give me the raw data for this field, don't style it or try to do anything fancy"
Example, for OwnerId field: the raw 18 digit Id, e.g. "005000000012378AAA"