Using SortByColumns in PowerApps for Custom Sort Sequences

Introduction:

As businesses globalize, the demand for handling data in multiple languages surges. Particularly when dealing with non-English datasets, the need for language-specific sorting becomes inevitable. While SQL Server provides a robust mechanism through collation settings, Power Apps, combined with SQL, offers a seamless user experience. In this guide on ‘SortByColumns PowerApps Language Sorting,‘ we explore how to customize sort sequences in PowerApps for multilingual datasets.

SQL Server’s strength as a data source lies in its ability to facilitate language-specific sort sequences. Moreover, there are situations in Power Apps where applying unique sort sequences can become challenging. Interestingly, an informative read on sorting conventions for Latin-based languages can be found on Wikipedia. However, for the purpose of this article, we’ll focus on practical applications.

A classic instance of a distinct sort sequence is in traditional Spanish, where “CH” and “LL” are considered single letters. Therefore, the sorting sequence for the words lomo, luz, and llama would be: lomo, luz, llama, with llama being the last.

Implementing Custom Sort Sequence in Power Apps

Imagine a scenario with an Employee table where the data needs to be sorted based on the traditional Spanish sequence. If the database or Employee table collation is set to traditional Spanish, Power Apps aligns seamlessly with the desired sort sequence. Alternatively, a view can be established to implement our sort sequence:

    CREATE VIEW vwEmpDetails
    AS 
    SELECT 
        [Name] COLLATE Traditional_Spanish_CS_AI AS [NameSpan], 
        Surname COLLATE Traditional_Spanish_CS_AI AS [SurNameSpan]
    FROM 
        EmployeeList
    

This snippet leverages the T-SQL Collate function for the traditional Spanish collation. After setting up, integrate this view into an app via the data panel in Power Apps. Now, if you wish to display employees sorted by surnames, utilize the sortbycolumns powerapps function as shown below:

    SortByColumns(vwEmpDetails, "SurNameSpan")
    

This delegable expression ensures SQL Server applies the sort sequence. The data table control in Power Apps will then exhibit the results as expected, positioning names like ‘Chávez‘ right after ‘Conner‘.

Conclusion:

Handling non-English data brings forward challenges like language-specific sorting. Furthermore, SQL Server, with its collation sequences, alongside Power Apps’ sortbycolumns powerapps function, offers an impeccable solution. It not only ensures data accuracy but also enhances the user experience.

If you ever find yourself stuck or need further technical assistance regarding this or any other topic, don’t hesitate to contact us. Our team of experts is always ready to guide and assist you. Remember, the right support can simplify complex tasks!