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.

Photo of Glenn Elliott

Glenn Elliott, Champion

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

Posted 6 years ago

  • 1
Photo of Zach McElrath

Zach McElrath, Employee

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

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">
<model id="DistinctOwners" limit="" query="true" createrowifnonefound="false" sobject="Account" type="aggregate">
<field id="Id" name="countId" function="COUNT"/>
<groupby method="simple">
<field id="OwnerId" name="ownerId"/>
<field id="Owner.Alias" name="ownerAlias"/>
<model id="Accounts" limit="50" query="true" createrowifnonefound="false" sobject="Account" orderby="Name">
<field id="Name"/>
<field id="OwnerId"/>
<field id="Owner.Name"/>
<field id="Owner.Alias"/>
<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"/>
<pagetitle model="Accounts">
<skootable showconditions="true" showsavecancel="true" searchbox="true" searchmethod="server" createrecords="true" model="Accounts" pagesize="10" showexportbuttons="true" mode="read">
<field id="Name"/>
<field id="OwnerId" displaytemplate="{{Alias}}">
<action type="edit"/>
<action type="delete"/>
<massactions defaultlabel="Mass Actions" defaulticon="ui-silk-wand" usefirstitemasdefault="true">
<action type="massupdate"/>
<action type="massdelete"/>
<actions defaultlabel="Global Actions" defaulticon="ui-silk-wand" usefirstitemasdefault="true"/>
<view type="standard"/>
<filter type="select" filteroffoptionlabel="Any Owner" createfilteroffoption="true" condition="OwnerId">
<source type="model" model="DistinctOwners">
<labeltemplate>Owner: {{ownerAlias}}</labeltemplate>

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.