Default sort order for aggregate table?

  • 2
  • Question
  • Updated 2 years ago
  • Answered
  • (Edited)
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!
Photo of Adam Johnson

Adam Johnson

  • 1,000 Points 1k badge 2x thumb

Posted 4 years ago

  • 2
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
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. 
Photo of Adam Johnson

Adam Johnson

  • 1,000 Points 1k badge 2x thumb
Worked perfectly. Thank you!
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
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
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
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. 
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
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
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
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.. 
Photo of Chandra V

Chandra V, Champion

  • 6,966 Points 5k badge 2x thumb
wow! Glad this is now available!