Filter based on distinct or aggregate query

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: Accounts List Owner: {{Alias}} {{Id}}

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.

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:

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.

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?

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”