Skip to main content

I have a table that I built using an aggregate model. It has 2 columns, purchase month and renewal amount. The table is showing the total purchase amount for each month… easy enough.

However, I can’t for the life of me figure out how to default the sort of the table to ascending for purchase month. I can make the column sortable and do it myself each time the page loads, but not default it.

For a standard table I would just change the ‘Fields to order by’ for the Model to purchase date, but that doesn’t seem to work for the aggregate model. I have also tried the id for the aggregate field and the grouping field.

Any help will be greatly appreciated!

This is not totally clear and has tripped me up severla times. The Order by statement in the Models advanced tab does not accept Field Alias values. You have to put the full function + field value there. Here is an example:


I have a model aggregating dates by Year and Month.



Then the Order By Statement looks like this: CALENDAR_YEAR(Date__c) DESC, CALENDAR_MONTH(Date__c) DESC, DAY_IN_MONTH(Date__c) DESC



Pro Tip. In order to have a Year, Month and Day grouping on the same field - you need to jump over to the XML and add the second and third grouping row.


Worked perfectly. Thank you!


Can you group on a related field that is a date/time field? I have tried numerous combinations and the model will not sort.

Example:
MAX(Object1__r.Object2__r.Field_Name__c) DESC


Tami.  I was able to go up one level and sort on the parent’s create date using the syntax you show above. 

One hack is to add the field to your table,  make it oderable, preview the page and order by the field.  Then dig in the javascript to see the SOQL statement and look for the ORDER BY statement.   

(Hint: Instpect element on the page. Open the console.  Type  skuid.$M(‘ModelName’).soql  )

This is what I got: 
SELECT MAX(CreatedDate) maxCreatedDate,MAX(Opportunity.CreatedDate) maxOpportunityCreatedDate,Opportunity.Account.Name opportunityAccountName FROM OpportunityLineItem GROUP BY Opportunity.Account.Name ORDER BY MAX(Opportunity.CreatedDate) ASC NULLS LAST"

When I pulled out MAX(Opportunity.CreatedDate) and made it my default order - things were beautiful. 




Hi Rob,

Thanks for the detailed response! The SOQL hack proved to be very helpful. The ‘NULLS LAST’ was needed in order for the ordering to work, without that piece it wouldn’t work.

So the order by line looks like this:


MAX(Object_1__r.Object_2__r.Field_Name__c) DESC NULLS LAST

An update… my pro tip above is no longer necessary.  Now you can add date fields to the “grouping” section of an aggregate model - MULTIPLE TIMES!   (yes I’m yelling).  This means you can add a date field grouping for year,  and for month - using the same field. 

Yay… 


wow! Glad this is now available!