Skip to main content
Nintex Community Menu Bar

Topic
When you order the records in an aggregate model by a date field with Convert to User Timezone enabled, the model doesn’t load and you get the error “Ordered field must be grouped.”
 

 

Resolution
Add the convertTimezone function to Fields to order records by in the model’s properties, e.g. instead of using DAY_ONLY(CreatedDate), the grouping field without the timezone conversion, use DAY_ONLY(convertTimezone(FieldName)).


Problem Details
On aggregate model grouping fields, there’s an option to convert date fields to the timezone of the user running the page so they see the results in their local time. However, it seems like when you enable Convert to User Timezone on a date field in an aggregate model, you can’t also order the model by that field. It appears that both of these properties work separately, but not together.

Screenshot: Grouping field on an aggregate model with Convert to User Timezone enabled:


Screenshot: Using that field as the Field to order records by:


Resolution Process

  1. First, I verified that the issue was reproducible in the most recent version of Skuid. The issue occurred not just on the customer’s original page, but also on a test page in the most recent version of Skuid using a different object and field.

  2. I searched the community for the error message to see if other users had run into a similar issue, but didn’t find a relevant discussion.

  3. Then I reviewed the Skuid documentation Aggregate Models: Ordering fields in a table. This does call out that for ordering aggregate models you should enter the formatted name of the aggregation or grouping (e.g. MIN(CreatedDate) or DAY_ONLY(CreatedDate)) but this didn’t work in our case when the convert timezone was enabled.

  4. Next I tried an alternative approach to solve the issue.

    • I created a Ui-Only aggregation field on the model and set its value Field From Another Model and chose the grouping field from the same model doCreatedDate. However this didn’t work and I got an error at runtime that said “Error: No such column ‘UiFieldName’ on entity ‘ObjectName’,” which made me suspect that the Ui-Only field was getting added to the model after it was ordered.
    • I found I could add an aggregation on the field I wanted to order by, e.g. MIN(CreatedDate) and that would work, but this still didn’t solve the issue of converting the date to the user timezone.

  5. Next, I asked other Skuid users how to solve the problem. They suggested I check the browser console and network requests and that I use an action sequence run on page load to sort the model. This worked in the v2 page that I was using to test, but it didn’t work in the client’s original v1 page since the model was being handled server side and the sort was happening client-side.

  6. I finally decided to check the model properties in the browser console, because the error did seem to be occurring because of a mismatch between the field name I was using to sort the model in the Composer and the actual field name as it was coming down from the datasource. When I did this, I found that the field we wanted to sort on was not being referred to as DAY_ONLY(CreatedDate) like we thought, but actually DAY_ONLY(convertTimezone(CreatedDate)). When I added convertTimezone to the field name in Fields to order records by, the model was then able to properly sort the records by the date field in the running user’s timezone.

    • When you are viewing a page in runtime, you can open the browser console and use skuid.debug.modelMap() to inspect the various models on the page, including the data and fields they return.
    • For Salesforce models, you can use skuid.debug.model(‘ModelName’).soql in v2 and skuid.$M('ModelName").soql in v1 to get the SOQL query that the model is sending to Salesforce to get the data.

Resolution Details
When you’re ordering aggregate models by a field, that field must be included in the model, either as an aggregation or a grouping. In the ordering properties, you must use the field’s API Name along with any aggregate or date functions (see Aggregate Models: General Issues). The API name is shown after the field alias. The screenshots below show various aggregations and groupings with their API name underlined.

Screenshot: API Names for Aggregations: COUNT(Name) and MIN(CreatedDate):

 

Screenshot: API Names for Groupings: skuid__Module__c and DAY_ONLY(convertTimezone(CreatedDate)) if Convert to User Timezone is enabled OR DAY_ONLY(CreatedDate) if it’s not enabled:
 

 

Additional Information

  • When you see an error message, a good first step is to check the Skuid community and documentation for more information.
  • When you’re struggling with an issue and reach the limits of your knowledge and the internet, ask more experienced Skuid users for help! (See Get help with an issue below for more details.)
  • Sometimes issues can be solved by looking for an alternate solution or setting up the page in a different way (Steps 4-5).
  • The skuid.debug API is very powerful and really helpful to get a better picture of what’s going on under the hood with model data.

Resources

Get help with an issue

Be the first to reply!

Reply


Sidebar Buttons
Nintex Community Home Contact Nintex Support Check Nintex Status
Terms & ConditionsCookie settings