Default sort order for aggregate table?

Adam JohnsonAdam Johnson
edited February 25, 2019 in Questions
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!
Tagged:

Comments

  • Rob HatchRob Hatch 🛠️ 
    edited March 8, 2017
    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. 

    image


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

    image


    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. 

  • Adam JohnsonAdam Johnson
    edited August 31, 2015
    Worked perfectly. Thank you!
  • Tami LustTami Lust
    edited December 2, 2016
    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

  • Rob HatchRob Hatch 🛠️ 
    edited September 1, 2016
    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. 



  • Tami LustTami Lust
    edited December 2, 2016
    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
  • Rob HatchRob Hatch 🛠️ 
    edited February 25, 2019
    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.. 
  • Chandra VChandra V
    edited March 8, 2017
    wow! Glad this is now available!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Categories