SQL SmartObjects and Associations within Smartforms

Problem

Users have the option to create a view in which they utilize an ID Column with the AutoNumber data type. However, when they click on the header title to sort the view, the values do not appear in the expected order (e.g., A, B, G, C, D).

To address this issue, users have attempted to implement sorting on load and added custom sort filters per user. These attempts have successfully enabled sorting in both ascending and descending orders. However, users desire to maintain the list output as is, while still allowing them to sort the view by the ID column in both ascending and descending orders by simply clicking on the column header.

Is there an alternative method that allows the user to dynamically sort the values without having to add the column under the view sort options?

Solution

If you are using a SQL SmartObject as your data source and SmartObject associations, please take note of the following.

SmartObject associations function similarly to SQL joins, as explained in the K2 documentation provided in the link below. When designing the view and using columns that retrieve data from other SmartObjects through one-to-many associations, the order may change during the process of building the return data for each SmartObject. This is achieved by storing the data in temporary tables and then determining the necessary associations and joins between these tables to return the data to the front-end view/form during runtime.

https://help.nintex.com/en-US/k2cloud/userguide/current/Content/How-Tos/SmartObjectAssociations/SmartObjectAssociations.htm?Highlight=%22Create%20a%20SmartObject%20Association%22

To ensure that the data is sorted correctly, you will need to implement sorting using the workaround described below. This approach is now considered the official method to maintain correct sorting, as it applies the sorting as part of the query between the temporary tables that store the data pulled from the associations. Here is an article that explains how SQL orders results when joins are used and if an ORDER BY clause is not specified: https://dba.stackexchange.com/questions/29381/how-does-sql-server-order-results-when-joins-are-used

By incorporating the appropriate sorting techniques, you can ensure that your data is correctly ordered and displayed as intended in your SmartObject associations.